database 我可以回滚我已经提交的事务吗？（数据丢失）

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

提问by SSS

I committed an incorrect UPDATEstatement and have lost some data.

我提交了一个不正确的UPDATE语句并丢失了一些数据。

Is it possible to rollback now, after I've already committed?

在我已经提交之后，现在是否可以回滚？

Any help?

有什么帮助吗？

ROLLBACK

says NOTICE: there is no transaction in progress.

说NOTICE: there is no transaction in progress。

回答by Craig Ringer

No, you can't undo, rollback or reverse a commit.

不，您无法撤消、回滚或撤销提交。

STOP THE DATABASE!

停止数据库！

(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 DELETEor similar, not an rm -rf /data/directoryscenario).

（注意：如果您从文件系统中删除了数据目录，请不要停止数据库。以下建议适用于 aDELETE或类似的意外提交，而不是rm -rf /data/directory场景）。

If this data was important, STOP YOUR DATABASE NOWand do not restart it. Use pg_ctl stop -m immediateso that no checkpoint is run on shutdown.

如果此数据很重要，请立即停止您的数据库并且不要重新启动它。使用pg_ctl stop -m immediate以便在关闭时不运行检查点。

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 beforethe accident happened.

事务一旦提交就无法回滚。您将需要从备份中恢复数据，或使用时间点恢复，这必须在事故发生前设置。

If you didn't have any PITR / WAL archiving set up and don't have backups, you're in real trouble.

如果您没有设置任何 PITR / WAL 归档并且没有备份，那么您就真的有麻烦了。

Urgent mitigation

紧急缓解

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 itto 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 partof the data directory, including pg_xlogetc. No part is unimportant.

一旦你的数据库已经停止，你应该让整个数据目录的文件系统级副本-包含文件夹base，pg_clog等复制这一切到一个新的位置。不要对新位置的副本进行任何操作，如果您没有备份，这是您恢复数据的唯一希望。如果可以，请在某个可移动存储上制作另一个副本，然后从计算机上拔下该存储。请记住，您绝对需要数据目录的每一部分，包括pg_xlog等等。没有任何部分是不重要的。

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 的方式。

Ways some data could've survived

一些数据可能幸存下来的方式

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 versionsof the rows you update to the table, leaving the old ones in place but marked as "deleted". After a while autovaccumcomes along and marks the rows as free space, so they can be overwritten by a later INSERTor UPDATE. Thus, the old versions of the UPDATEd rows might still be lying around, present but inaccessible.

如果您足够快地停止数据库，您可能希望从表中恢复一些数据。这是因为 PostgreSQL 使用多版本并发控制 (MVCC)来管理对其存储的并发访问。有时它会将您更新的行的新版本写入表中，保留旧版本但标记为“已删除”。一段时间后autovaccum出现并将行标记为可用空间，因此它们可以被稍后INSERT或UPDATE. 因此，UPDATEd 行的旧版本可能仍然存在，存在但无法访问。

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 bgwriterand 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_ctlin immediatemode you might've captured the data from before the checkpoint happened, so your old data is more likely to still be in the heap.

此外，Pg 分两个阶段写入。第一个数据写入预写日志 (WAL)。只有在它被写入 WAL 并命中磁盘后，它才会被复制到“堆”（主表），可能会覆盖那里的旧数据。WAL 内容bgwriter通过定期检查点和定期检查点复制到主堆。默认情况下，检查点每 5 分钟发生一次。如果您设法在检查点发生之前停止数据库并通过硬杀死它、拔掉机器上的插头或使用pg_ctlinimmediate模式来停止它，那么您可能已经从检查点发生之前捕获了数据，因此您的旧数据是更有可能仍在堆中。

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.

现在您已经制作了数据目录的完整文件系统级副本，如果您确实需要，您可以启动您的数据库备份；数据仍然会消失，但你已经尽你所能给自己一些可能恢复它的希望。？考虑到我可能会选择关闭数据库以确保安全。

Recovery

恢复

You may now need to hire an expert in PostgreSQL's innardsto assist you in a data recovery attempt. Be prepared to pay a professional for their time, possibly quite a bit of time.

您现在可能需要聘请一位了解 PostgreSQL 内部的专家来协助您进行数据恢复尝试。准备支付专业人士的时间，可能是相当多的时间。

I posted about this on the Pg mailing list, and Виктор Егоров linked todepesz'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.

我在 Pg 邮件列表上发布了有关此内容，而Виктор Егоров 链接到depesz 在 pg_dirtyread 上的帖子，看起来正是您想要的，尽管它不会恢复TOASTed 数据，因此它的实用性有限。试试看，如果幸运的话，它可能会奏效。

See: pg_dirtyread on GitHub.

请参阅：GitHub 上的 pg_dirtyread。

I've removed what I'd written in this section as it's obsoleted by that tool.

我已经删除了我在本节中写的内容，因为它已被该工具淘汰。

See also PostgreSQL row storage fundamentals

另请参阅PostgreSQL 行存储基础知识

Prevention

预防

See my blog entry Preventing PostgreSQL database corruption.

请参阅我的博客条目防止 PostgreSQL 数据库损坏。

---

---

On a semi-related side-note, if you were using two phase commityou could ROLLBACK PREPAREDfor 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.

在一个半相关侧面说明，如果你使用两个阶段提交你可以ROLLBACK PREPARED为这是准备提交，但尚未完全COMMITED一个transction。这是您最接近回滚已提交事务的方法，不适用于您的情况。