Re: pg_basebackup on slave running for a long time

Thanks John, Well that clarifies about archive a lot!

On 22 Nov 2016 22:22, "John R Pierce" <pierce(at)hogranch(dot)com> wrote:

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