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?