我可以回滚我已经提交的事务吗？ （数据丢失） [英] Can I rollback a transaction I've already committed? (data loss)
问题描述
我实施了不正确的 UPDATE 语句，并丢失了一些数据。
I committed an incorrect UPDATE statement and have lost some data.
现在可以回滚，
任何帮助？
ROLLBACK
说注意：没有正在进行的交易。
推荐答案
否，不能撤消，回滚或撤消提交。
No, you can't undo, rollback or reverse a commit.
如果此数据很重要，请立即停止您的数据库，不要重新启动。使用 pg_ctl stop -m immediate ，以便在关闭时不运行检查点。
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.
您无法回滚事务一次它已经承诺。您需要从备份中还原数据，或使用时间点恢复
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 等的文件夹。 em>所有到新位置。不要对新位置中的副本执行任何操作，如果没有备份，它是恢复数据的唯一希望。如果可以，在某些可移动存储设备上创建另一个副本，然后从计算机上拔下该存储设备。记住，你需要数据目录的绝对的每一部分，包括 pg_xlog 等。没有部分不重要。
Once your database is stopped, you should make a file system level 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.
确切如何制作副本取决于您运行的操作系统。
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.
如果你停止你的DB足够快，你可能有希望从表中恢复一些数据。这是因为PostgreSQL使用多版本并发控制（MVCC）来管理对其存储的并发访问。有时，它会将您更新到表中的行的 INSERT 或 UPDATE 覆盖。因此，旧版本的 UPDATE d行可能仍然存在，但是不可访问。
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分钟发生一次。如果您设法在检查点发生之前停止数据库，并通过硬杀死它，拉出机器上的插头或使用
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邮件列表上发布了这条消息，ВикторЕгоровlinked to 关于pg_dirtyread的depesz的帖子，它看起来像你想要的，虽然它不能恢复 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.
请参阅： pg_dirtyread on GitHub 。
我删除了我在本节中写的，因为它已经被工具淘汰了
I've removed what I'd written in this section as it's obsoleted by that tool.
另请参见 PostgreSQL行存储基础知识
查看我的博客条目防止PostgreSQL数据库损坏。
在半相关的旁注中，如果您使用两阶段commit 你可以 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屋！
