On Fri, Dec 11, 2015 at 3:15 PM, John R Pierce <pierce@hogranch.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
