How to rollback an update in PostgreSQL

Question

While editing some records in my PostgreSQL database using sql in the terminal (in ubuntu lucid), I made a wrong update.

Instead of -

update mytable set start_time='13:06:00' where id=123;

I typed -

update mytable set start_time='13:06:00';

So, all records are now having the same start_time value.

Is there a way to undo this change? There are some 500+ records in the table, and I do not know what the start_time value for each record was

Is it lost forever?

Answer 1

I'm assuming it was a transaction that's already committed? If so, that's what "commit" means, you can't go back.

Some data may be recoverable if you're lucky. Stop the database NOW.

Here's an answer I wrote on the same topic earlier. I hope it's helpful.

This might be too: Recoved deleted rows in postgresql .

Unless the data is absolutely critical, just restore from backups, it'll be lots easier and less painful. If you didn't have backups, consider yourself soundly thwacked.

Answer 2

If you catch the mistake and immediately bring down any applications using the database and take it offline, you can potentially use Point-in-Time Recovery (PITR) to replay your Write Ahead Log (WAL) files up to, but not including, the moment when the errant transaction was made. This would return the database to the state it was in prior, thus effectively 'undoing' that transaction.

As an approach for a production application database it has a number of obvious limitations, but there are circumstances in which PITR may be the best option available, especially when critical data loss has occurred. However, it is of no value if archiving was not already configured before the corruption event.

https://www.postgresql.org/docs/current/static/continuous-archiving.html

Similar capabilities exist with other relational database engines.