I committed an incorrect UPDATE statement and have lost some data.
我提交了一个错误的更新语句，并且丢失了一些数据。
Is it possible to rollback now, after I've already committed?
在我已经承诺之后，现在是否可以回滚?
Any help?
任何帮助吗?
ROLLBACK
says NOTICE: there is no transaction in progress.
通知:没有交易正在进行中。
1 个解决方案
#1
78
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 DELETE or similar, not an rm -rf /data/directory scenario).
(注意:如果将数据目录从文件系统中删除，不要停止数据库。以下建议适用于删除或类似的意外提交，而不是rm -rf /data/directory场景)。
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.
如果这个数据很重要，现在就停止数据库，不要重新启动它。立即使用pg_ctl stop -m，这样在关机时就不会运行检查点。
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.
事务一旦开始，就不能回滚。您将需要从备份中恢复数据，或者使用点时间恢复，这必须在事故发生之前设置好。
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 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.
一旦数据库停止，您应该创建整个数据目录的文件系统级别——包含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 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.
如果您快速停止数据库，您可能希望从表中恢复一些数据。这是因为PostgreSQL使用多版本并发控制(MVCC)来管理对其存储的并发访问。有时，它会为您更新到表的行编写新版本，保留旧的行，但标记为“删除”。过了一段时间，autovaccum将行标记为*空间，因此它们可以被后来的插入或更新所覆盖。因此，更新后的行的旧版本可能仍然存在，但不可访问。
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.
另外，Pg在两个阶段中编写。第一个数据被写入预写日志(WAL)。只有当它被写入到WAL并到达磁盘时，它才会被复制到“堆”(主表)，可能覆盖那里的旧数据。WAL内容被bgwriter和周期性检查点复制到主堆中。默认情况下，每5分钟就会有一个检查点。如果你设法停止数据库之前发生了一个检查站,停止hard-killing,插头的机器上,或者使用pg_ctl在直接模式中你可能已经捕获的数据在检查站发生之前,所以你的旧的数据更可能仍然是在堆中。
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 innards to 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 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邮件列表,和ВикторЕгоровpg_dirtyread depesz有关的文章,看起来就像你想要的,尽管它不烤恢复数据的有限的效用。试一试，如果你幸运的话，它可能有用。
See: pg_dirtyread on GitHub.
看:pg_dirtyread在GitHub上。
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 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.
在一个半相关的附加说明中，如果您正在使用两个阶段提交，那么您可以回滚为准备提交但未完全推荐的收发做准备。这是最接近于回滚已经提交的事务的方法，并不适用于您的情况。
#1
78
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 DELETE or similar, not an rm -rf /data/directory scenario).
(注意:如果将数据目录从文件系统中删除，不要停止数据库。以下建议适用于删除或类似的意外提交，而不是rm -rf /data/directory场景)。
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.
如果这个数据很重要，现在就停止数据库，不要重新启动它。立即使用pg_ctl stop -m，这样在关机时就不会运行检查点。
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.
事务一旦开始，就不能回滚。您将需要从备份中恢复数据，或者使用点时间恢复，这必须在事故发生之前设置好。
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 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.
一旦数据库停止，您应该创建整个数据目录的文件系统级别——包含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 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.
如果您快速停止数据库，您可能希望从表中恢复一些数据。这是因为PostgreSQL使用多版本并发控制(MVCC)来管理对其存储的并发访问。有时，它会为您更新到表的行编写新版本，保留旧的行，但标记为“删除”。过了一段时间，autovaccum将行标记为*空间，因此它们可以被后来的插入或更新所覆盖。因此，更新后的行的旧版本可能仍然存在，但不可访问。
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.
另外，Pg在两个阶段中编写。第一个数据被写入预写日志(WAL)。只有当它被写入到WAL并到达磁盘时，它才会被复制到“堆”(主表)，可能覆盖那里的旧数据。WAL内容被bgwriter和周期性检查点复制到主堆中。默认情况下，每5分钟就会有一个检查点。如果你设法停止数据库之前发生了一个检查站,停止hard-killing,插头的机器上,或者使用pg_ctl在直接模式中你可能已经捕获的数据在检查站发生之前,所以你的旧的数据更可能仍然是在堆中。
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 innards to 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 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邮件列表,和ВикторЕгоровpg_dirtyread depesz有关的文章,看起来就像你想要的,尽管它不烤恢复数据的有限的效用。试一试，如果你幸运的话，它可能有用。
See: pg_dirtyread on GitHub.
看:pg_dirtyread在GitHub上。
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 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.
在一个半相关的附加说明中，如果您正在使用两个阶段提交，那么您可以回滚为准备提交但未完全推荐的收发做准备。这是最接近于回滚已经提交的事务的方法，并不适用于您的情况。