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.
