The destination server (we call the Secondary server) only needs to be a
copy of the Primary (live) server.
Well, the easiest solution is just to re-initdb the secondary and load
pg_dumpall output into it.
We're using Debian and have servers on Etch and Lenny.
I've never carried out initialisation before and wasn't sure if I wasn't getting into something I don't know enough about. AFAIK it should be something like;
Stop DB
Delete the contents of the 'datadir'
Run initdb something, something
Start DB
I wasn't sure if the Debian installer hadn't set things up in some special way.
Is there anything you expect to gain by
re-inventing portions of pg_dumpall?
The primary reason is that if you run the output from pg_dumpall --clean into a DB which already has previous copies of the databases then there are errors. Primarily, it can't drop a ROLE because existing objects depend on it and errors are raised.
We want our systems guy to monitor the output from the synchronisation script and they are not DB developers. Therefore, we need output which doesn't show ERRORs. Normally, when they see ERROR's they would raise it with us DB guys.
Currently I feel that the sync script I want is within my capabilities - the re-init stuff would need investigation and testing. I'll get on with the script and post up the result. There are parts which others may find useful.
BTW - I've been using PG for years and really appreciate the hard work put in by you guys. We've used it in all kinds of scenarios. One of our clients was particularly happy about a Debian/PG box which replaced their flakey Access DB share and ran perfectly for years until they asked us to replace the whole application with a web app.
Cheers,
Kevin
regards, tom lane