On 10/13/2008 7:10 AM, Stéphane A. Schildknecht wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Stuart Bishop a écrit :
>>>>> I'm setting us up a separate staging / test server and I want to read
>>>>> in a pg_dump of our current origin stripping out all the slony stuff.
>>>>>
>>>>> I was thinking this could serve two purposes a) test out backups
>>>>> restore properly and b) provide us with us with the staging / test
>>>>> server
>>>>>
>>>>> What's the best way to remove all the slony bits?
>>>> Well, you can always just drop the slony schema (with a cascade) -
>>>> that should do it.
>>> Not quite.  There are two things that *doesn't* hit:
>> 
>> So what was the final recommended process for building a stand alone
>> database from a pg_dump of a replicated node?
>> 
>> pg_dump --oids --format=c --file=master.dump master_db
>> createdb staging_db
>> pg_restore -d staging_db master.dump
>> slonik << EOM
>> cluster name = sl;
>> node 1 admin conninfo = 'dbname=staging_db user=slony';
>> uninstall node (id = 1);
>> EOM
>> 
>> This process dies on the last step with:
>> 
>> <stdin>:3: PGRES_FATAL_ERROR select "_sl".uninstallNode();  - ERROR:
>> Slony-I: alterTableRestore(): Table with id 1 not found
>> CONTEXT:  SQL statement "SELECT  "_sl".alterTableRestore( $1 )"
>> PL/pgSQL function "uninstallnode" line 14 at PERFORM
>> Failed to exec uninstallNode() for node 1
>> 
>> So if I'm reading this thread correctly, the alternative is 'DROP _sl
>> CASCADE;', which doesn't do a full cleanup. Is there no supported
>> disaster recovery procedure?
>> 
> 
> 
> Hi,
> 
> What you can do is call the uninstallnode() procedure.

It is too late at this point. In Slony before version 2.0, the dump from 
the subscriber may NOT contain the full schema information. There is no 
way to "fix" that.

The recommended way is to take separate schema and data dumps. The 
schema must come from the origin, the data can come from a subscriber.

Alternatively a healthy subscriber can be turned into a standalone 
database by running UNINSTALL NODE. Or one can do a regular failover in 
order to keep other subscribers.


Jan


> 
> It is located in the replication schema.
> 
> You can call it like that :
> select _replication.uninstallnode();
> 
> You should recover all tables in their normal state.
> Be sure your dump are done on master. Otherwise you will certainly lose
> information.
> 
> Best regards,
> - --
> Stéphane Schildknecht
> PostgreSQLFr - http://www.postgresql.fr
> Dalibo - http://www.dalibo.com
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
> 
> iD8DBQFI8yy6A+REPKWGI0ERAvL6AKCC+r4V+A7h4PmiotSCg7tiDrnHdgCfTK/M
> WUccjyObkxYmlROmWgNd+7U=
> =9F6L
> -----END PGP SIGNATURE-----
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general


-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin