Thread: WAL archiving to 2 standby servers
Hi all, I'm wondering as I've never seen anyone doing this if I can/should setup WAL archiving from the primary server to 2 differentstandby servers. These machines are configured to be streaming replication and I've got the archive command on the primary doing an scp to both standbys, but for some reasonthe second one isn't getting anything. What the archive_command looks like is "scp %p user@standby1:/<dir>/%f; scp %p user@standby2:<dir>/%f". That's not wokring correctly,but I'm wondering if it's even necessary for the 2nd standby? Just askin', Jay PS. There are two standby's to deal with the problem of synchronous_commit and the failure of one of the standby's.
John Scalia <jayknowsunix@gmail.com> writes: > Hi all, > > I'm wondering as I've never seen anyone doing this if I can/should > setup WAL archiving from the primary server to 2 different standby > servers. These machines are configured to be streaming replication and > I've got the archive command on the primary doing an scp to both > standbys, but for some reason the second one isn't getting > anything. What the archive_command looks like is "scp %p > user@standby1:/<dir>/%f; scp %p user@standby2:<dir>/%f". That's not > wokring correctly, but I'm wondering if it's even necessary for the > 2nd standby? Look in your server log for errors on the 2nd scp. Also check your $PGDATA/pg_xlog/archive_status directory for a pile-up of *.ready files. If your archive command isn't fully operational and is returning non-zero to the caller, then Pg thinks archiving is failing and will *not* recycle the related xlog files. I'd suggest also you get on the server box as user postgres and make sure you can manually ssh without being prompted for a password and/or asked to verify host keys... fix mismatching lines in ~/.ssh/known_hosts file etc. As for whether or not you should be sending WALs to both instances, it depends on what you need to accomplish. HTH > > Just askin', > Jay > > PS. There are two standby's to deal with the problem of synchronous_commit and the failure of one of the standby's. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
John Scalia <jayknowsunix@gmail.com> wrote: > I'm wondering as I've never seen anyone doing this if I can/should setup > WAL archiving from the primary server to 2 different standby servers. These > machines are configured to be > streaming replication and I've got the archive command on the primary doing > an scp to both standbys, but for some reason the second one isn't getting > anything. What the > archive_command looks like is "scp %p user@standby1:/<dir>/%f; scp %p > user@standby2:<dir>/%f". That's not wokring correctly, but > I'm wondering if it's even necessary for the 2nd > standby? What I have done in a situation like this is to have the archive command copy to a directory on the database server machine, and use rsync to make copies on all targets. If you try to have the archive command deal with both standby machines directly, it gets pretty complicated trying to handle the failure of one of those standby machines, or of the network connecting to one of them. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company