3

I accidentally updated 71 rows of 1 column in table. It's production and I want to learn how I can revert back those changes. It's an PostgreSQL environment.

Just after updating I realized where condition is missing. I rollback it but:

db=# ROLLBACK;
WARNING:  there is no transaction in progress
ROLLBACK

Not sure how it's not getting rolled back. I had no backup files for it. Else I would have copied files locally and stored in database. So, can someone suggest how else I can proceed with reverting those changes?

Is there any way PostgreSQL store logs and that can be used to restore my data?

halfer
  • 19,824
  • 17
  • 99
  • 186
John
  • 41
  • 1
  • 3
  • Oh dear, if you have no backup then you are screwed. Rollback can't do anything once a transaction has finished. – The Blue Dog Apr 19 '14 at 19:27
  • In SQL i guess it can be rollback even by logs which happens to get update after every transaction and older can be used to Roll back. IN postgres we dont have such facility ? :( – John Apr 19 '14 at 19:32
  • No, once it's done; then it's done. How varied is the data? Can you 'make something up' and bluff it? ;) – The Blue Dog Apr 19 '14 at 19:36
  • No. though column update had only time value which is update for day after tomorrow but this data is used every day and each with different time zone. so if no chance then i will have to manually update each row that too with 2 hours of downtime. – John Apr 19 '14 at 19:38
  • Unfortunately, it looks like that is your only option right now - good luck... – The Blue Dog Apr 19 '14 at 19:41
  • thanks man. but as u said I'm screwed. – John Apr 19 '14 at 19:47

1 Answers1

1

If you have committed the transaction,you cannot rollback or undo the update.Please find the detailed answer here

Can I rollback a transaction I've already committed? (data loss)

Community
  • 1
  • 1
Monika
  • 135
  • 1
  • 12
  • Thank Monika. but i tried that too my bad luck as it is not even capturing transaction details in /usr/local/pg_xlog (this directory too is missing) . – John Apr 19 '14 at 19:47
  • @John - it will be storing WAL somewhere, just in a different location. That's not going to help you though, as you'd need a base backup to replay the log from. – Richard Huxton Apr 20 '14 at 07:02