On 04/24/2018 07:50 AM, David Gauthier wrote:
Hi: I need some advise on how best to backup a PG DB.
PG 9.5.2 on RHEL6
The requirement is to be able to restore the DB after catastrophic
failure and lose no more than the last 15 minutes worth of data. Also,
we would like to be able to do the backups on-line (no down time).
There is no need for PITR other than the point in time being the latest
possible.
Typically, I would think doing a weekly full backup, daily incremental
backups and turn on journaling to capture what goes on since the last
backup. When DB recovery is needed, restore up to the last daily, then
reply the journal to restore up to the last time the journal was flushed
to disk (checkpoint=15 minutes). I'm not quite sure if something like
this is possible with PG. I've read about the WAL file and wonder if it
Pretty sure that won't work. Once you do the restore from the last daily
you will change the Postgres files to a state different from the ones
captured by the journaling. Replaying the journal will result in a
mixture of old and new.
could be used together with the on-line logical backups (pg_dump) to
achieve the 15 minute requirement without needing downtime for physical
backups..
Any advise?
Take a look here:
https://www.postgresql.org/docs/9.5/static/continuous-archiving.html
Thanks in Advance.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
