Re: pg_basebackup on slave running for a long time

On 11/22/2016 3:41 AM, Subhankar Chattopadhyay wrote:
> John,
>
> Can you explain the Wal Archive procedure, how it can be setup so that
> the slave never goes out of sync, even if master deletes the WAL
> files?

The WAL archive will typically be a separate file server that both the
master and slave can reach... it could be accessed via NFS or via scp
or whatever is appropriate for your environment. The master is
configured with an archive command (cp in the case of nfs, or scp for
ssh/scp, or whatever) to copy WAL segments to the archive. The slave
is setup with an recovery command (cp, scp, etc) to fetch from this same
archive.

The archive will continue grow without limit if you don't do some
cleanup on it. one strategy is to periodically (weekly? monthly?) do
a base backup of the master (possibly by using rsync or another file
copy method, rather than pg_basebackup), and keep 2 of these full
backups, and all wal archives since the beginning of the oldest one.
with this backup + archive, you can initialize a new slave without
bothering the master (rsync or scp or cp the latest backup, then let the
slave recover from the wal archive).

this backup+archive will also let you do point-in-time-recovery (aka
PITR). say something catastrophic happens and the data in the master
is bad after some point in time (maybe a jr admin accidentally clobbers
key data, but the app kept running). you can restore the last good
base backup, and recover up to but not including the point in time of
the transaction that clobbered your data.

--
john r pierce, recycling bits in santa cruz