Thread: WAL archiving to 2 standby servers

WAL archiving to 2 standby servers

From
John Scalia
Date:
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.


Re: WAL archiving to 2 standby servers

From
Jerry Sievers
Date:
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


Re: WAL archiving to 2 standby servers

From
Kevin Grittner
Date:
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