From: | Kevin Grittner <kgrittn(at)gmail(dot)com> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Complete Recovery 9.4.4 |
Date: | 2015-12-11 21:48:54 |
Message-ID: | CACjxUsMJYK5NBYjzBkUHyv1fFu4QDi2qqJPqSn=pZR9ES0yDQg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Dec 11, 2015 at 3:15 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> 1) Stop PG <- no, instead, execute: select pg_start_backup();
> 2) Make copy of current state including PGDATA w/ pg_xlog <= don't backup
> the WAL archives, they are external to the database server, and are written
> continuously.
> 3) Select pg_stop_backup();
> 4) run along until your problem happens.
> 5) stop postgres server
> 6) Cleanup PGDATA w/ pg_xlog
> 7) Restore backup taken in step 2, placing contents in PGDATA /w pg_xlog
> 8) setup a recovery.conf file specifying the desired transaction ID or
> timestamp as the 'recovery_target' and the recovery command to fetch from
> your archive.
> 9) restart postgres server and let it recover from the archives.
To expand on that a little, I think it is safest to exclude from
the backup not only all files under pg_xlog, but also
postmaster.pid. You absolutely should *not* exclude or delete
backup_label *except in the case that the server crashes DURING THE
BACKUP PROCESS*, leaving you without a complete backup. Never
trust files copied from pg_xlog copied between pg_start_backup()
and pg_stop_backup() except those made through the archiving
process or by pg_basebackup -x or -X switches.
On the other hand, if you are recovering after a crash, and the
files in pg_xlog are readable, you can copy them while the server
is stopped (post-crash) into the pg_xlog directory created from the
backup, before starting the recovery from the backup. If these
files are intact, that will allow all transactions which were
logged (if synchronous_commit is on, that will be, at a minimum,
all which had a successful return from commit and all for which the
effects of the commit were visible before the crash).
It's worth reading the PITR restore docs closely.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | David Steele | 2015-12-11 21:50:36 | Re: Complete Recovery 9.4.4 |
Previous Message | Carlo Cabanilla | 2015-12-11 21:37:26 | Re: connections not getting closed on a replica |