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