Backup Strategy Advise

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Backup Strategy Advise
Date: 2018-04-24 14:50:39
Message-ID: CAMBRECAno5gQWOZOPKy29V_OWEX19GWm5pu9coa-ZZO83AWcSA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jehan-Guillaume (ioguix) de Rorthais 2018-04-24 15:08:55 Re: Postgres PAF setup
Previous Message Sam Gendler 2018-04-24 14:27:04 Re: Rationale for aversion to the central database?