Thread: Complete Recovery 9.4.4

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.


--
john r pierce, recycling bits in santa cruz

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

On 12/11/15 3:55 PM, Will McCormick wrote:
> Basic backup and recovery question. I want to perform complete restore
> and recovery using continuous archive mode.
>
> Lets imagine we have a single table MYTABLE. Here are my high level steps
>
> 1) Add a record  A) to MYTABLE
> 2) Take a file system backup to be used for recovery. This backup
> includes archive logs

How are you taking the backup?  Are you using cp for archiving?

> 3) Add a record B) to MYTABLE
>
> Timeline -> incident happens need backup
>
> 4) Stop PG
> 5) Make copy of current state including PGDATA w/ pg_xlog and WAL archives
> 6) Cleanup PGDATA /wpg_xlog and WAL archive  directory
> 7) Restore backup taken in step 2, placing contents in PGDATA /w pg_xlog
> and archives

Don't restore the contents of pg_xlog.

> 8) Remove contents of pg_xlog
> 9) Copy contents of pg_xlog in step 5 to PGDATA/pg_xlog

Don't do this - allow Postgres to get the archive logs it needs using
the recovery_command.

> 10) Create recovery.conf file with cp
> /database/postgres/product/9.4.4/archive/%f %p
> 11) Startup the server
>
> What I see happen is 1) restores but my change in step 3) is not.

There's not enough detail here to determine where you are going wrong.
It could be something in step #2 or steps #5-10.

You should have a look at pgBackRest - it's a complete backup and
recovery solution that takes care of all the dirty work for you:

http://www.pgbackrest.org/user-guide.html

And allows you to do Point-in-Time Recovery with a single command:

http://www.pgbackrest.org/user-guide.html#pitr

This a detailed guide that shows you exactly how PITR works and how to
verify your result.  Even if you don't use pgBackRest it may be useful
for you to read it.

--
-David
david@pgmasters.net