On Fri, Dec 11, 2015 at 3:15 PM, John R Pierce <pierce@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
