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 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? Thanks in Advance.
