我可以回滚我已经提交的事务吗?(数据丢失) [英] Can I rollback a transaction I've already committed? (data loss)
问题描述
我提交了一个错误的UPDATE 语句并且丢失了一些数据.
I committed an incorrect UPDATE statement and have lost some data.
在我已经提交之后，现在可以回滚吗?
Is it possible to rollback now, after I've already committed?
有什么帮助吗?
ROLLBACK
说注意:没有正在进行的交易.
推荐答案
不，您不能撤消、回滚或撤消提交.
No, you can't undo, rollback or reverse a commit.
(注意:如果您从文件系统中删除了数据目录，请不要停止数据库.以下建议适用于 DELETE 或类似内容的意外提交，而不是 rm -rf/data/directory 场景).
(Note: if you deleted the data directory off the filesystem, do NOT stop the database. The following advice applies to an accidental commit of a DELETE or similar, not an rm -rf /data/directory scenario).
如果此数据很重要，请立即停止您的数据库并且不要重新启动它.使用 pg_ctl stop -mimmediate 以便在关闭时不运行检查点.
If this data was important, STOP YOUR DATABASE NOW and do not restart it. Use pg_ctl stop -m immediate so that no checkpoint is run on shutdown.
事务一旦提交就无法回滚.您需要从备份中恢复数据，或使用 point-in-时间恢复，必须在事故发生之前设置.
You cannot roll back a transaction once it has commited. You will need to restore the data from backups, or use point-in-time recovery, which must have been set up before the accident happened.
如果您没有设置任何 PITR/WAL 存档并且没有备份，那您就真的有麻烦了.
If you didn't have any PITR / WAL archiving set up and don't have backups, you're in real trouble.
一旦您的数据库停止，您应该制作整个数据目录的文件系统级副本 - 包含 base、pg_clog 等的文件夹. Copy 全部 到一个新位置.不要对新位置的副本进行任何操作，如果您没有备份，这是您恢复数据的唯一希望.如果可以，请在某个可移动存储上制作另一个副本，然后从计算机上拔下该存储.请记住，您需要绝对数据目录的每一部分，包括pg_xlog等.没有任何部分是不重要的.
Once your database is stopped, you should make a file system level copy of the whole data directory - the folder that contains base, pg_clog, etc. Copy all of it to a new location. Do not do anything to the copy in the new location, it is your only hope of recovering your data if you do not have backups. Make another copy on some removable storage if you can, and then unplug that storage from the computer. Remember, you need absolutely every part of the data directory, including pg_xlog etc. No part is unimportant.
复制的具体方式取决于您运行的操作系统.数据目录的位置取决于您运行的操作系统以及您安装 PostgreSQL 的方式.
Exactly how to make the copy depends on which operating system you're running. Where the data dir is depends on which OS you're running and how you installed PostgreSQL.
如果您足够快地停止数据库，您可能希望从表中恢复一些数据.那是因为 PostgreSQL 使用 多版本并发控制 (MVCC) 来管理对其存储的并发访问.有时它会将您更新的行的INSERT 或 UPDATE 覆盖.因此，UPDATEd 行的旧版本可能仍然存在，存在但无法访问.
If you stop your DB quickly enough you might have a hope of recovering some data from the tables. That's because PostgreSQL uses multi-version concurrency control (MVCC) to manage concurrent access to its storage. Sometimes it will write new versions of the rows you update to the table, leaving the old ones in place but marked as "deleted". After a while autovaccum comes along and marks the rows as free space, so they can be overwritten by a later INSERT or UPDATE. Thus, the old versions of the UPDATEd rows might still be lying around, present but inaccessible.
另外，Pg 分两个阶段写入.第一个数据写入预写日志 (WAL).只有在将其写入 WAL 并命中磁盘后，才会将其复制到堆"中.(主表)，可能会覆盖那里的旧数据.WAL 内容由 bgwriter 和定期检查点复制到主堆.默认情况下，检查点每 5 分钟发生一次.如果您设法在检查点发生之前停止数据库并通过硬杀死它、拔掉机器上的插头或在 immediate 模式下使用 pg_ctl 来停止它，您可能已经在检查点发生之前捕获了数据，因此您的旧数据更有可能仍在堆中.
Additionally, Pg writes in two phases. First data is written to the write-ahead log (WAL). Only once it's been written to the WAL and hit disk, it's then copied to the "heap" (the main tables), possibly overwriting old data that was there. The WAL content is copied to the main heap by the bgwriter and by periodic checkpoints. By default checkpoints happen every 5 minutes. If you manage to stop the database before a checkpoint has happened and stopped it by hard-killing it, pulling the plug on the machine, or using pg_ctl in immediate mode you might've captured the data from before the checkpoint happened, so your old data is more likely to still be in the heap.
现在您已经制作了数据目录的完整文件系统级副本，如果您确实需要，您可以启动您的数据库备份；数据仍然会消失，但你已经尽你所能给自己一些可能恢复它的希望.考虑到选择，为了安全起见，我可能会关闭数据库.
Now that you have made a complete file-system-level copy of the data dir you can start your database back up if you really need to; the data will still be gone, but you've done what you can to give yourself some hope of maybe recovering it. Given the choice I'd probably keep the DB shut down just to be safe.
您现在可能需要聘请 PostgreSQL 内部专家来协助您数据恢复尝试.准备好为他们的时间付钱给专业人士，可能是相当多的时间.
You may now need to hire an expert in PostgreSQL's innards to assist you in a data recovery attempt. Be prepared to pay a professional for their time, possibly quite a bit of time.
我在 Pg 邮件列表上发布了这个消息，并且 depesz 在 pg_dirtyread 上的帖子，这看起来正是您想要的，尽管它不会恢复 TOAST ed 数据，因此它的实用性有限.试试看，如果幸运的话，它可能会奏效.
I posted about this on the Pg mailing list, and Виктор Егоров linked to depesz's post on pg_dirtyread, which looks like just what you want, though it doesn't recover TOASTed data so it's of limited utility. Give it a try, if you're lucky it might work.
我已经删除了我在本节中所写的内容，因为它已被该工具废弃.
I've removed what I'd written in this section as it's obsoleted by that tool.
另请参阅 PostgreSQL 行存储基础知识
请参阅我的博客条目 防止 PostgreSQL 数据库损坏.
See my blog entry Preventing PostgreSQL database corruption.
在半相关的旁注中，如果您使用的是 两阶段提交 您可以ROLLBACK PREPARED 用于准备提交但未完全提交的事务.这是最接近回滚已提交事务的方法，不适用于您的情况.
On a semi-related side-note, if you were using two phase commit you could ROLLBACK PREPARED for a transction that was prepared for commit but not fully commited. That's about the closest you get to rolling back an already-committed transaction, and does not apply to your situation.
这篇关于我可以回滚我已经提交的事务吗?(数据丢失)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
