PITR restore in postgresql

Question

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.

Answer 1

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.

Answer 2

The PITR process is as follows:

• Set up WAL archiving (via archive_command in postgres.conf)
• Create file-level backup of your database using some tool (I assume pg_basebackup does this?..)
• Save file-level backup with WAL archive somewhere

Then when you need to restore you:

• Restore file-level backup into some location
• Copy WAL archive files into some location
• Set up WAL restore (via restore_command in recovery.conf)

When you start the restored cluster, it will notice that a recovery.conf file exists and ATTEMPT to do a restore, reading the WAL archive files and applying changes. At some point, it will reach a CONSISTENT state, which is what is most important in the restore process.

Based on what you have, it looks like you are not actually doing a restore to any other system, but simply starting the cluster that had been stopped.