Postgresql – PITR restore in postgresql

backuppostgresqlpostgresql-9.5restore

I need to perform incremental backups and restore those backups in postgresql. So in order to achieve this i setup wal-archiving as follows.

wal_level = archive
archive_mode = on   
archive_command = 'copy "%p" "C:\\Users\\Suniel\\Desktop\\archive\\%f"'
archive_timeout = 3600 //for every hour
max_wal_senders = 1

Then, I took the base backup. 

pg_basebackup -U postgres -D C:\Users\Suniel\Desktop\Postgresql_backup\pg_basebackup -F t -x

which gives the replication of existing data directory in the given location.

Then i created the recovery.conf file with following parameters.

recovery_target_time = '2016-07-05 04:00:00'
recovery_target_timeline = 'latest'
restore_command = 'copy "C:\\Users\\Suniel\\Desktop\\archive\\%f" "%p"'

I stopped the postgresql service and placed the recovery file under data directory of installed postgres location.

Then I started the postgres service which changes recovery.conf to recovery.done but when i view the tables records, i saw no changes.

But my log files seems like this with some error(i think). 

2016-07-06 12:15:49 NPT LOG:  database system was shut down at 2016-07-06 12:15:08 NPT
2016-07-06 12:15:49 NPT LOG:  starting point-in-time recovery to 2016-07-05 04:00:00+05:45
2016-07-06 12:15:49 NPT LOG:  consistent recovery state reached at 0/19000098
2016-07-06 12:15:49 NPT LOG:  invalid record length at 0/19000098
2016-07-06 12:15:49 NPT LOG:  redo is not required
2016-07-06 12:15:49 NPT LOG:  selected new timeline ID: 19
2016-07-06 12:15:50 NPT LOG:  archive recovery complete
2016-07-06 12:15:50 NPT LOG:  MultiXact member wraparound protections are now enabled
2016-07-06 12:15:50 NPT LOG:  database system is ready to accept connections
2016-07-06 12:15:50 NPT LOG:  autovacuum launcher started

I must be wrong somewhere in these steps. What did i miss? Am i wrong in these procedures?

Thanks in advance.

Best Answer

PITR restores forward, not backward. You must use a base backup which was taken before the point in time to which you want to recover.

Related Question