On 12/9/2015 4:27 PM, FattahRozzaq wrote:
I really don't know why I should keep the wal archives. I implement streaming replication into 1 server (standby server). I'm really newbie to PostgreSQL but the boss pushed me to handle it and implement it in production f*&%*$%%$#%$#&# (forgive me) They don't hire a database expert, I don't know why.
primary use for wal archives is to implement a point-in-time-recovery (PITR) backup system. It is effectively a continuous backup of your databases, that can be restored to an arbitrary point-in-time.
typically, every so often (maybe once a week?) you would create a base backup. I personally would keep the last 2 basebackups, plus all wal archives since the start of the older of those two. both the WAL archives and base backups should be stored on a separate storage system,, NOT either the master or standby database servers, typically via NFS.
one usecase for this basebackup+wal archive is fast recovery in case of master failure... if your master fails, you need to promote the standby to master, then you'll want to bring up a new standby server, this can be done by using the most recent base backup and then playing back all wal archives to it til it catches up, and then it can stream from the recently promoted master which was the standby and you have your high availability redundancy back.
another use case, and perhaps more critical one.. say someone does something nasty to your databases, like drops the wrong table, or clobbers a bunch of financial data. this will quickly replicate to the standby, rendering it equally useless for recovery. With PITR, you can restore that most recent basebackup, then play back the WAL archive up just to before the transaction that clobbered your critical data. you would need to do this on both the master and slave, resuming replication in the correct sequence.
about the only use case for wal archives without a base backup is speeding up the resuming of a standby server that got behind, perhaps due to being shutdown for hardware or OS maintenance, or whatever. if you don't have a wal archive, the standby server has to request all the xlog's since it last streamed from the master, while with a wal archive, it can fetch as many as it can from the wal archive THEN catch up with the master. This lowers the workload on the master.
-- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
