Pawel Veselov wrote: > I've been struggling with understanding all the necessary pieces for streaming replication. So I put > down the pieces as I did understand them, and would appreciate if you guys could point out any of the > stuff I understood or have done wrong. > > The set up is pgpool + streaming replication + hot stand by. No load balancing, stand-by nodes will > not receive any application queries (I don't have that big of a query load, and I don't want to risk > inconsistent reads). There are no shared file systems, but there is a way to rsync/scp files between > nodes. Fail-over is automatic, and should kick in within reasonably small period after master failure. > > 1. Archiving. Should be turned on on all the nodes. The archive command should copy the archive file > to the local archive directory, and rsync archive directory between all the nodes. My understanding is > that archiving is necessary if a stand-by node ever "missed" enough WAL updates to need an old enough > WAL that might have been removed from pg_xlog.
You don't give details about how the rsync is triggered,
but I'd advise against having rsync as part of archive_command. First, it is slow and if there is a lot of activity, the archiver will not be able to keep up. Second, if rsync fails, the WAL file will not be considered archived.
Both these things will keep the WAL files from being deleted from pg_xlog.
I'd schedule rsync as a cron job or similar.
From your later comments, it's also apparent that these archived WALs will be useless after failover (for the purpose of recovery), so there is no reason to send them to all the nodes after all.
I obviously lost it here. The archives do need to be synchronized, for the purpose of recovering slaves. If a slave dies, and I want to recover it, it may need the archived WALs, and for this, the archives should be available on the node. So, rsync (or something like that) is necessary. But it's a bad idea to run the rsync from the archive command itself.