John Britto <john@sodexis.com> writes:
> Hello,
>
> I have a streaming replication setup along with WAL archive.
>
> archive_command = ‘test ! -f /var/pg_archive/%f && cp %p <archive
> location>%f && scp %p postgres@192.168.0.123:<archive location>/%f'
>
> When the SCP command fails, the master repeatedly tries to send the
> archived WAL to standby. But during this time, the pg_xlog directly
> grew with newer WAL files.
>
> The streaming replication hasn't had the problem because on my check,
> the WAL write location on the primary was same with the last WAL
> location received/replayed in standby.
>
> Since the pg_xlog in the master had few newer WAL files, the master
> archive is lagging to pick the current pg_xlog WAL file. When a new
> WAL occur in the pg_xlog, Master picks the old WAL file to send to
> the standby.
Yup Pg is going to handle the unshipped WALs one at a time and it will
do them in order, oldest (lowest file name) first.
> How should I force the PostgreSQL to batch copy the lagging WAL files
> to pg_archive and then send to standby. Can I do this manually using
> rsync? I wonder how PostgreSQL knows the changes because it
> maintains info in archive_status with extension as .ready and .done.
I suggest you fix your basic archiving routine to complete and exit with
success to postgres.
And as for archive command scripts in general, simpler is better.
If you want to manually ship them in bulk, you may do so but then will
need to remove the corresponding archive_status/$foo.ready file so that
postgres won't keep trying to ship the same one.
HTH
> Please assist.
>
> Thanks,
>
> John Britto
>
>
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general