I am unable to restore the database to a previous time using the PITR steps mentioned on PostgreSQL website Article 23.3.3. Recovering with an On-line Backup (http://www.postgresql.org/docs/8.1/static/backup-online.html#RECOVERY-TARGET-XID)

The following are the steps I followed:

  1. I made the below changes to postgresql.conf file which is located in the Data Folder:

    wal_level = archive
    archive_mode = on   
    archive_command = 'copy "%p" "C:\\archivedir\\%f"'

    I created a folder named 'archivedir' on C Drive

  2. I made the below changes to recovery.conf file:

    recovery_target_time = '2014-02-05 11:45:00 CST'
    primary_conninfo = 'host=localhost port=5433 user=owner password=xxxxx'
    restore_command = 'copy "C:\\archivedir\\%f" "%p"'
  3. Stopped the service of postgresql and Deleted Folder contents of pg_xlog

  4. Re-started the service
  5. Recovery.conf is changed to recovery.done
  6. But I am unable to restore the database to a previous state.

I have checked multiple times by dropping tables and trying to restore the database to an earlier state. Is there anything that I am missing?

migrated from stackoverflow.com Feb 6 '14 at 0:13

This question came from our site for professional and enthusiast programmers.

  • Oh, dear. Please tell me you didn't do that on your main database, but on a copy. You must perform PITR recovery based on a backup copy of the database, referred to as a "base backup" in the documentation. You can't use PITR to roll an existing, running database back to a point in the past, which you seem to be trying to do. What you need to do now is take a complete filesytem level copy of your database and store it somewhere safe, to attempt to recover it from the damage you just did in case there's no alternative and you don't have a proper base backup. – Craig Ringer Feb 6 '14 at 4:54

First, never, ever, ever delete anything from pg_xlog, ever. You will corrupt your database. Now, on to what you've misunderstood about all this:

I have checked multiple times by dropping tables and trying to restore the database to an earlier state. Is there anything that I am missing?

PITR doesn't work like that. It doesn't revert a running database to an older point in time.

How it works is that you set it up first, before you need to restore:

  • Set up WAL archiving, so you record WAL to a safe location.

  • Before you need to revert, you make a base backup. You can use pg_start_backup() and rsync for this, but it's easiest and safest to use pg_basebackup(). See the documentation for details on creating base backups. You must do this after setting up WAL archiving, so the archive contains all WAL from the moment the basebackup is taken onward.

  • Continue to run with WAL archiving, periodically taking a new base backup to reduce the amount of old WAL you have to keep and the amount of recovery time you need. Automated tools like pgbarman help with this.

When you need to create a historical copy of the database state to recover from something, you:

  • Make a copy of the base backup to writeable storage

  • Set the copy up with a recovery.conf file that specifies an appropriate restore_command for fetching WAL and the recovery_target_time you want to replay up to. Setting primary_conninfo makes no sense, since you won't be using this as a warm or hot standby streaming replica.

  • Start the copy, and allow it to replay WAL until it reports that recovery is complete.

  • Connect to the copy, and do whatever you need to grab the data you wish to recover out of it. You may use pg_dump to extract a database or parts of it, use COPY to extract contents of tables, etc.

If you wish to completely revert a database you can delete the datadir, replace it with the copy, and follow the procedure above to replay the copy up to a point in time.

What you cannot do is take an existing database and roll it back to some point in the past. WAL replay only goes forward in time. So you must restore an old base backup then allow it to replay WAL up to the point in time you want.

  • Hi craig, I am facing the same problem in restoring using PITR. Can you please elaborate the restore part? I took the base backup somewhere safe. I created the recovery.conf file with necessary parameters. On restarting the postgres service, the recovery.conf file changed to recovery.done. When i view the log file, it says the it is successfully restored but the data are still the same in database. What am i missing craig? Thank you. – Suniel Jul 5 '16 at 11:04
  • Sounds like you didn't include a restore_command so it didn't know where to recover WAL from, or your restore_command isn't actually finding the WAL archives. Check the logs. – Craig Ringer Jul 5 '16 at 21:08

Your Answer

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