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.