I looked into this briefly a few weeks ago.

From what I could see slony references the OIDs of individual objects, and since it's not possible to maintain object OIDs across a pg_dump and pg_restore the slony schema is in effect "shagged".

----- Original Message ----
> From: Martin Eriksson <M.Eriksson at albourne.com>
> To: slony1-general at lists.slony.info
> Sent: Monday, 26 May, 2008 7:25:34 AM
> Subject: [Slony1-general] pg_dump/pg_restore keeping all slony stuff intact?
> 
> Hi people,
> 
> I'm wondering if there is a way to shut down a slave slony node take a dump 
> using pg_dump of the database on that node
> dropping that database then using pg_restore to restore the database and then 
> happily just fire up slony again.
> 
> I tried this and it did not work.
> 
> The reason why I ask is that we got a few slave nodes distributed around the 
> world and some nodes are on rather slow links mainly due to the geographic 
> location. The database is around 40 gigs so just re-creating the node and 
> letting it replicate across is just not possible the slave node will never catch 
> up. replicating 40 gigs across to these nodes will take several days and that is 
> not really an option.
> 
> we are trying to do 2 things.
> 
> 1. upgrade to postgres 8.3.1 (currently on 8.2.4)
> 2. adding a new slave node in a remote location. (its ALOT faster to FedEx a 
> harddrive with the dump and pg_restore it there then the full replicaiton would 
> ever be)
> 
> but as i said I've had no luck using pg_dump and pg_restore to re-create a wiped 
> slave node :(
> 
> what i've tried so far on a test system is:
> 
> 1. replicate the database (1 Master 1 Slave)
> 2. Verify replication is working correctly
> 3. shutting down slony (for both slave and master, using slon_kill)
> 4. use "pg_dump -Z 9 -Fc -o db > /tmp/slonyDump.dmp" on the salve db (tried 
> without the -o as well)
> 5. re-create the db and then load the dump it into it with "pg_restore -Fc -d db 
> /tmp/slonyDump.dmp"
> 
> then i try to just start it again, with the slon_start for both master and 
> slave)
> 
> but I get:
> 
> 2008-05-23 18:35:30 EEST ERROR  remoteWorkerThread_1: "select 
> "_db_cluster".sequenceSetValue(1,1,'581','32'); " PGRES_FATAL_ERROR ERROR:  
> Slony-I: sequenceSetValue(): sequence 1 not found
> 
> so maybe either my pg_dump or my pg_restore was not doing a complete job.
> 
> I'm open to try more or less anything so please if anyone got some ideas of a 
> way to restore a database in a way that will make slony startup and not force a 
> full replication?
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general



      __________________________________________________________
Sent from Yahoo! Mail.
A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html