如何回滚 PostgreSQL 中的更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12880994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to rollback an update in PostgreSQL
提问by damon
While editing some records in my PostgreSQL database using sql in the terminal (in ubuntu lucid), I made a wrong update.
在终端（在 ubuntu lucid 中）使用 sql 在我的 PostgreSQL 数据库中编辑一些记录时，我进行了错误的更新。
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.
因此，所有记录现在都具有相同的 start_time 值。
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
有没有办法撤消此更改？表中有大约 500 多条记录，我不知道每条记录的 start_time 值是多少
Is it lost forever?
它会永远消失吗？
采纳答案by Craig Ringer
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.
这也可能是：在 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.
除非数据是绝对关键的，否则只需从备份中恢复，它会更容易，也不会那么痛苦。如果您没有备份，请认为自己受到了严重打击。
回答by hemp
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.
如果您发现错误并立即关闭使用该数据库的任何应用程序并将其脱机，您可能会使用时间点恢复 (PITR) 来重放您的预写日志 (WAL) 文件，直到但不包括进行错误交易的那一刻。这会将数据库返回到它之前的状态，从而有效地“撤消”该事务。
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.
作为生产应用程序数据库的一种方法，它有许多明显的局限性，但在某些情况下，PITR 可能是可用的最佳选择，尤其是在发生关键数据丢失时。但是，如果在损坏事件之前尚未配置存档，则它没有任何价值。
https://www.postgresql.org/docs/current/static/continuous-archiving.html
https://www.postgresql.org/docs/current/static/continuous-archiving.html
Similar capabilities exist with other relational database engines.
其他关系数据库引擎也存在类似的功能。
