2

I was running a batch script in a PostgreSQL database which inserted values in different tables between 10:40 and 11:00. Later I came to know that the script was executed mistakenly. How can I get back my database to a state before 10:40 without stopping the server? I don't want the values which were inserted by batch during 10:40 to 11:00.

WAL has been configured.

I was struggling with this for the last three days. I have been trying with creating recovery.conf file with:

restore_command='path to my archivefile/%f %p'
recovery_target_time = '2014-10-15 10:40:00'
recovery_target_inclusive = true

Still I am not able to get my database backup to previous state.

Improve this question
10
  • Before you do anything else, make sure you're not deleting old WAL archives. Take a copy of your latest base backup and the WAL archives somewhere safe and read-only.
    – Craig Ringer
    Oct 15, 2014 at 13:40
  • Did you configure WAL archiving before you had the problem? If you only configured it afterwards it will do you no good at all. What base backup are you starting the restore with? What message(s) appear in the logs when you start it, if any? Please explain in detail what you are doing, step by step.
    – Craig Ringer
    Oct 15, 2014 at 13:41
  • "Without stopping the server"?
    – jjanes
    Oct 15, 2014 at 17:59
  • @jjanes i don't want my database to be shutdown.
    – user37651
    Oct 16, 2014 at 8:59
  • @Craig Ringer I have configured the WAL before problem has occurred.Have taken the backup using pg_start_backup copied the data dir to diff location and pg_sop_backup.
    – user37651
    Oct 16, 2014 at 11:50

1 Answer 1

Reset to default
2

If you know which rows were accidentally updated, you just don't know what the previous value of those rows were, then what you can do is:

  1. Restore to a new non-production server from your base backup.
  2. Recover that server to the point just before the error.
  3. Write a query to select the values for the rows you know got mistakenly updated.
  4. Turn that into a series of updates which can be run against the original production server.
  5. Spin up yet another server which has been recovered up to current date to test those updates against to make sure they do the correct thing
  6. Apply to production.

Of course if people or software have been relying on the bad values between when the error was made and now, then reversing the error will not reverse the decisions they made based on bad data.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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