Revert back updated column in table in PostgreSQL

Question

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?

Oh dear, if you have no backup then you are screwed. Rollback can't do anything once a transaction has finished. — The Blue Dog
– The Blue Dog, Commented Apr 19, 2014 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
– John, Commented Apr 19, 2014 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
– The Blue Dog, Commented Apr 19, 2014 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
– John, Commented Apr 19, 2014 at 19:38
Unfortunately, it looks like that is your only option right now - good luck... — The Blue Dog
– The Blue Dog, Commented Apr 19, 2014 at 19:41

Community · Accepted Answer · 2017-05-23 11:52:44Z

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)

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Apr 19, 2014 at 19:34

Monika

1232 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Over a year ago

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) .

Richard Huxton Over a year ago

@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.

Collectives™ on Stack Overflow

Revert back updated column in table in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related