postgresql pg_resetxlog 有什么作用？它是如何工作的？

What does pg_resetxlog do? And how does it work?

提问by Phil

I have looked at the postgres documentation and the synopsis below is given:

我查看了 postgres 文档，并给出了以下概要：

pg_resetxlog [-f] [-n] [-ooid ] [-x xid ] [-e xid_epoch ] [-m mxid ] [-O mxoff ] [-l timelineid,fileid,seg ] datadir

But at no point in the documentation do they explain what the datadir is. Is it the %postgres-path%/9.0/dataor could it be %postgres-path%/9.0/data/pgxlog?

但是在文档中他们从未解释过 datadir 是什么。它是%postgres-path%/9.0/data还是可能是%postgres-path%/9.0/data/pgxlog？

Also, if I want to change my xlog directory, can I simply move the items in my current pg_xlogdirectory and run the command to point to another directory? (Assume my current pg_xlog directory is in /data1/postgres/data/pg_xlogAND the directory I want it the logs to go to is: /data2/pg_xlog)

另外，如果我想更改我的 xlog 目录，我可以简单地移动当前pg_xlog目录中的项目并运行命令以指向另一个目录吗？（假设我现在的pg_xlog里面的目录是/data1/postgres/data/pg_xlog，我希望它的日志转到目录为：/data2/pg_xlog）

Would the following command achieve what I've just described?

下面的命令能达到我刚刚描述的效果吗？

mv /data1/postgres/data/pg_xlog /data2/pg_xlog
pg_resetxlog /data2

回答by Craig Ringer

pg_resetxlogis a tool of last resort for getting your database running again after:

pg_resetxlog是让您的数据库在以下情况下再次运行的最后手段：

• You deleted files you shouldn't have from pg_xlog;

• You restored a file system level backup that omitted the pg_xlogdirectory due to a backup system configuration mistake (this happens more than you'd think, people think "it has log in the name so it must be unimportant; I'll leave it out of the backups").

• File-system corruption due to a hardware fault or hard drive failure damaged your data directory; or potentially even

• a PostgreSQL bug or operating system bug damaged the write-ahead logs (exceedingly rare).

• 你删除了你不应该拥有的文件pg_xlog；

• pg_xlog由于备份系统配置错误，您恢复了一个省略目录的文件系统级备份（这种情况发生的比您想象的要多，人们认为“它有登录名，所以它一定不重要；我将把它排除在外备份”）。

• 由于硬件故障或硬盘驱动器故障导致的文件系统损坏损坏了您的数据目录；甚至可能

• PostgreSQL 错误或操作系统错误损坏了预写日志（非常罕见）。

As the manual says:

正如手册所说：

pg_resetxlog clears the write-ahead log (WAL) [...]. This function is sometimes needed if these files have become corrupted. It should be used only as a last resort, when the server will not start due to such corruption.

pg_resetxlog 清除预写日志 (WAL) [...]。如果这些文件已损坏，有时需要此功能。当服务器由于此类损坏而无法启动时，它应该仅用作最后的手段。

Do not run pg_resetxlogunless you know exactly what you are doing and why. If you are unsure, ask on the pgsql-general mailing listor on https://dba.stackexchange.com/.

pg_resetxlog除非您确切地知道自己在做什么以及为什么，否则不要跑步。如果您不确定，请在pgsql-general 邮件列表或https://dba.stackexchange.com/上询问。

pg_resetxlogmay corrupt your database, as the documentation warns. If you have to use it, you should REINDEX, dump your database(s), re-initdb, and reload your databases. Do not just continue using the damaged cluster. As per the documentation:

pg_resetxlog正如文档所警告的那样，可能会损坏您的数据库。如果你必须使用它，你应该REINDEX转储你的数据库，重新初始化数据库，并重新加载你的数据库。不要只是继续使用损坏的集群。根据文档：

After running this command, it should be possible to start the server, but bear in mind that the database might contain inconsistent data due to partially-committed transactions. You should immediately dump your data, run initdb, and reload. After reload, check for inconsistencies and repair as needed.

运行此命令后，应该可以启动服务器，但请记住，由于部分提交的事务，数据库可能包含不一致的数据。您应该立即转储数据，运行 initdb，然后重新加载。重新加载后，检查不一致并根据需要进行修复。

If you simply want to move your write-ahead log directory to another location, you should:

如果您只是想将预写日志目录移动到另一个位置，您应该：

• Stop PostgreSQL
• Move pg_xlog
• Add a symbolic link from the old location to the new location
• Start PostgreSQL

• 停止 PostgreSQL
• 移动 pg_xlog
• 添加从旧位置到新位置的符号链接
• 启动 PostgreSQL

Or, as the documentation says:

或者，正如文档所说：

It is advantageous if the log is located on a different disk from the main database files. This can be achieved by moving the pg_xlog directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.

如果日志与主数据库文件位于不同的磁盘上，则是有利的。这可以通过将 pg_xlog 目录移动到另一个位置（当然，服务器关闭时）并创建从主数据目录中的原始位置到新位置的符号链接来实现。

If PostgreSQL fails to start, you've done something wrong. Do not use pg_resetxlogto "fix" it. Undo your changes and work out what you did wrong.

如果 PostgreSQL 无法启动，您就做错了。不要pg_resetxlog用来“修复”它。撤消您的更改并找出您做错了什么。

回答by Ibrar Ahmed

Move the contents of your pg_xlog directory to the desired location like '/home/foo/pg_xlog'

将 pg_xlog 目录的内容移动到所需的位置，如“/home/foo/pg_xlog”

mv pg_xlog/* /home/foo/pg_xlog

Delete the pg_xlog directory

删除 pg_xlog 目录

rm -rf pg_xlog

Create a soft-link of pg_xlog

创建 pg_xlog 的软链接

ln -s /home/foo/pg_xlog pg_xlog

Verify the link

验证链接

ls -lrt pg_xlog

Note: pg_resetxlog is not the right tool to move the pg_xlog please read

注意：pg_resetxlog 不是移动 pg_xlog 的正确工具，请阅读

http://www.postgresql.org/docs/9.2/static/app-pgresetxlog.html

http://www.postgresql.org/docs/9.2/static/app-pgresetxlog.html

回答by Daniel Vérité

The data directory corresponds to the data_directoryentry in the postgresql.conf file, or the PGDATAenvironment variable, and it can also be queried live in SQL with the SHOW data_directorystatement. It does not point to the pg_xlogdirectory, but one level above.

数据目录对应data_directorypostgresql.conf文件中的条目，或者PGDATA环境变量，也可以用SHOW data_directory语句在SQL中实时查询。它不指向pg_xlog目录，而是指向上一层。

To change the location of the WAL files, the PG server must be shut down, the pg_xlogdirectory and its contents moved to the new location, a symbolic link should be created from the old location to the new location, and the server restarted. pg_resetxlogshould not be used for this, as it may suppress the latest transactions (this tool is typically used in crash recovery situations when all else fails).

要更改 WAL 文件的位置，必须关闭 PG 服务器，将pg_xlog目录及其内容移动到新位置，应创建从旧位置到新位置的符号链接，然后重新启动服务器。pg_resetxlog不应为此使用，因为它可能会抑制最新的事务（此工具通常用于所有其他方法都失败时的崩溃恢复情况）。

回答by jnas

You should never manually touch the WAL files, that is perfectly clear.

您永远不应该手动触摸 WAL 文件，这一点非常清楚。

If there is dangling files in the pg_xlogdirectory, that is, there are is file which ends with .done*in the sub-folder archive_statuswhich need to be cleaned up manually, that can be accomplished with the sql command

如果pg_xlog目录中有dangling文件，即.done*子文件夹中archive_status有结尾的文件需要手动清理，可以用sql命令完成

CHECKPOINT;

which forces a transaction checkpoint which includes cleaning up the WAL segment files.

这会强制执行一个事务检查点，其中包括清理 WAL 段文件。

See documentation for 9.3but exists in all current versions of Postgresql.

请参阅9.3 的文档，但存在于所有当前版本的 Postgresql 中。