1

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.

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.

|improve this answer|||||
1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.