我能否回滚已提交的事务？（数据丢失）

Question

我提交了错误的UPDATE语句，导致一些数据丢失。

现在提交后是否仍有可能回滚？

有什么帮助吗？

ROLLBACK

显示 注意：当前没有正在进行的交易。

- SSS

---

如果在一个事务中嵌套了另一个事务，那么会怎样？如果内部（第二个）事务被提交，我们应该期望什么不变式？回滚外部（第一个）事务是否也会回滚第二个事务？这里期望什么？例如：db.begin(); db.add(1); db.begin(); db.add(2); db.commit(); db.rollback() - daparic

我认为你误解了“commit”这个词的含义。 - Mark Ransom

Answer 1

不，你不能撤销、回滚或者反转一个提交。

停止数据库！

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

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

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

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

紧急缓解措施

一旦您的数据库停止运行，您应该对整个数据目录进行文件系统级别的复制——包含base、pg_clog等的文件夹。将其全部复制到一个新位置。不要对新位置中的副本进行任何操作，如果没有备份，它是恢复数据的唯一希望。如果可以的话，在某些可移动存储设备上再复制一份，并将该存储设备从计算机上拔掉。记住，您需要绝对每个部分的数据目录，包括pg_xlog等。没有任何部分是不重要的。

如何制作副本取决于您使用的操作系统。数据目录的位置取决于您使用的操作系统以及如何安装PostgreSQL。

一些数据可能存活的方式

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

此外，Pg分两个阶段进行写入。首先，数据被写入预写日志（WAL）。只有在写入WAL并且已经到磁盘后，才会将其复制到“堆”（主表）中，可能会覆盖之前存在的旧数据。WAL内容由bgwriter和周期性检查点复制到主堆中。默认情况下，检查点每5分钟发生一次。如果在检查点发生之前停止数据库并通过强制关闭它、拔掉机器上的插头或使用pg_ctl以immediate模式停止，则可能捕获到检查点发生之前的数据，因此旧数据更有可能仍然存在于堆中。

现在，您已经完全复制了数据目录的文件系统级别，如果确实需要，可以重新启动数据库；数据仍将丢失，但您已尽力为自己提供一些希望恢复它的可能性。如果可以选择，我可能会保持数据库关闭以确保安全。

恢复

您现在可能需要聘请一位精通PostgreSQL内部的专家来协助您进行数据恢复尝试。准备好支付专业人士的时间费用，可能会很高。

我在Pg邮件列表上发布了关于此的帖子，Виктор Егоров链接到 depesz在pg_dirtyread上的帖子，看起来正是您想要的，但它无法恢复TOASTed数据，因此其效用有限。如果运气好，可以试试看。

请参见：GitHub上的pg_dirtyread。

由于那个工具已经过时，我已删除此部分的内容。

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

预防措施

请查看我的博客文章预防PostgreSQL数据库损坏。

---

在半相关的旁注中，如果您正在使用两阶段提交，则可以对准备提交但尚未完全提交的事务执行ROLLBACK PREPARED。这是撤销已经提交的事务的最接近方式，不适用于您的情况。

- Craig Ringer