Benjamin <benjamin@netyantra.com> writes:
> My idea was to lock the db on A, scp the required files onto B and then
> unlock db on A.
The only adequate "lock" for that sort of thing is to shut down the
postmaster on A. Anything less is simply not trustworthy.
There is support coming up in 8.0 for WAL archiving and point-in-time
recovery. With that, you could do the scp without any lock and then
fix up discrepancies by replaying the WAL archives for the interval that
the scp was running. Furthermore you could continue to ship WAL
segments to B to keep it up to date with A, without needing repeated
full scp's. See
http://developer.postgresql.org/docs/postgres/backup.html
for some preliminary documentation about this.
Slony looks like a pretty good alternative too, and it's available now.
But don't bother trying to roll your own replication setup. It's
unlikely that you can easily build a reliable one.
regards, tom lane