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
