Postgresql 中文操作指南

pg_resetwal

pg_resetwal — 重置 PostgreSQL 数据库集群的预写日志和其他控制信息

pg_resetwal — reset the write-ahead log and other control information of a PostgreSQL database cluster

Synopsis

pg_resetwal [ -f | —​force ] [ -n | —​dry-run ] [ option …​] datadir

pg_resetwal [ -f | —​force ] [ -n | —​dry-run ] [option…​] datadir

Description

pg_resetwal 清除预写日志 (WAL)，并可以选择重置存储在 pg_control 文件中的其他一些控制信息。当这些文件已损坏时，有时需要此函数。仅在服务器由于此类损坏而无法启动时才应将其作为最后手段使用。

pg_resetwal clears the write-ahead log (WAL) and optionally resets some other control information stored in the pg_control file. 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.

在运行此命令后，应该能够启动服务器，但请记住由于部分提交的事务，数据库可能包含不一致的数据。你应该立即转储你的数据，运行 initdb 并还原。还原后，检查不一致之处并根据需要进行修复。

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 restore. After restore, check for inconsistencies and repair as needed.

此实用程序只能由安装服务器的用户运行，因为它需要对数据目录进行读/写访问。出于安全原因，你必须在命令行中指定数据目录。 pg_resetwal 不使用环境变量 PGDATA 。

This utility can only be run by the user who installed the server, because it requires read/write access to the data directory. For safety reasons, you must specify the data directory on the command line. pg_resetwal does not use the environment variable PGDATA.

如果 pg_resetwal 抱怨它无法确定 pg_control 的有效数据，则你可以强制它继续操作，方法是指定 -f （强制）选项。在这种情况下，系统会用合理的数值代替缺失的数据。可以找到大多数字段的匹配项，但是可能需要手动为下一个 OID、下一个事务 ID 和时代、下一个多事务 ID 和偏移量，以及 WAL 启动位置字段提供帮助。可以使用下面讨论的选项设置这些字段。如果你无法确定所有这些字段的正确数值，仍然可以使用 -f ，但是恢复的数据库比平时更加可疑：立即转储和还原是必要的。 Do not 在转储之前执行数据库中的任何数据修改操作，因为任何此类操作都会使损坏情况变得更糟。

If pg_resetwal complains that it cannot determine valid data for pg_control, you can force it to proceed anyway by specifying the -f (force) option. In this case plausible values will be substituted for the missing data. Most of the fields can be expected to match, but manual assistance might be needed for the next OID, next transaction ID and epoch, next multitransaction ID and offset, and WAL starting location fields. These fields can be set using the options discussed below. If you are not able to determine correct values for all these fields, -f can still be used, but the recovered database must be treated with even more suspicion than usual: an immediate dump and restore is imperative. Do not execute any data-modifying operations in the database before you dump, as any such action is likely to make the corruption worse.

Options

  • -f_—​force_

    • Force pg_resetwal to proceed even if it cannot determine valid data for pg_control, as explained above.

  • -n_—​dry-run_

    • The -n/—​dry-run option instructs pg_resetwal to print the values reconstructed from pg_control and values about to be changed, and then exit without modifying anything. This is mainly a debugging tool, but can be useful as a sanity check before allowing pg_resetwal to proceed for real.

  • -V_—​version_

    • Display version information, then exit.

  • -?_—​help_

    • Show help, then exit.

仅当 pg_resetwal 无法通过读取 pg_control 确定适当的值时才需要以下选项。可以按照以下步骤确定安全的值。对于使用数字参数的值，可以使用前缀 0x 指定十六进制值。

The following options are only needed when pg_resetwal is unable to determine appropriate values by reading pg_control. Safe values can be determined as described below. For values that take numeric arguments, hexadecimal values can be specified by using the prefix 0x.

  • -c _xid,xid—​commit-timestamp-ids=_xid,xid

    • Manually set the oldest and newest transaction IDs for which the commit time can be retrieved.

    • A safe value for the oldest transaction ID for which the commit time can be retrieved (first part) can be determined by looking for the numerically smallest file name in the directory pg_commit_ts under the data directory. Conversely, a safe value for the newest transaction ID for which the commit time can be retrieved (second part) can be determined by looking for the numerically greatest file name in the same directory. The file names are in hexadecimal.

  • -e _xid_epoch—​epoch=_xid_epoch

    • Manually set the next transaction ID’s epoch.

    • The transaction ID epoch is not actually stored anywhere in the database except in the field that is set by pg_resetwal, so any value will work so far as the database itself is concerned. You might need to adjust this value to ensure that replication systems such as Slony-I and Skytools work correctly — if so, an appropriate value should be obtainable from the state of the downstream replicated database.

  • -l _walfile—​next-wal-file=_walfile

    • Manually set the WAL starting location by specifying the name of the next WAL segment file.

    • The name of next WAL segment file should be larger than any WAL segment file name currently existing in the directory pg_wal under the data directory. These names are also in hexadecimal and have three parts. The first part is the “timeline ID” and should usually be kept the same. For example, if 00000001000000320000004A is the largest entry in pg_wal, use -l 00000001000000320000004B or higher.

    • Note that when using nondefault WAL segment sizes, the numbers in the WAL file names are different from the LSNs that are reported by system functions and system views. This option takes a WAL file name, not an LSN.

  • -m _mxid,mxid—​multixact-ids=_mxid,mxid

    • Manually set the next and oldest multitransaction ID.

    • A safe value for the next multitransaction ID (first part) can be determined by looking for the numerically largest file name in the directory pg_multixact/offsets under the data directory, adding one, and then multiplying by 65536 (0x10000). Conversely, a safe value for the oldest multitransaction ID (second part of -m) can be determined by looking for the numerically smallest file name in the same directory and multiplying by 65536. The file names are in hexadecimal, so the easiest way to do this is to specify the option value in hexadecimal and append four zeroes.

  • -o _oid—​next-oid=_oid

    • Manually set the next OID.

    • There is no comparably easy way to determine a next OID that’s beyond the largest one in the database, but fortunately it is not critical to get the next-OID setting right.

  • -O _mxoff—​multixact-offset=_mxoff

    • Manually set the next multitransaction offset.

    • A safe value can be determined by looking for the numerically largest file name in the directory pg_multixact/members under the data directory, adding one, and then multiplying by 52352 (0xCC80). The file names are in hexadecimal. There is no simple recipe such as the ones for other options of appending zeroes.

  • —​wal-segsize=_wal_segment_size_

    • Set the new WAL segment size, in megabytes. The value must be set to a power of 2 between 1 and 1024 (megabytes). See the same option of initdb for more information.

  • -u _xid—​oldest-transaction-id=_xid

    • Manually set the oldest unfrozen transaction ID.

    • A safe value can be determined by looking for the numerically smallest file name in the directory pg_xact under the data directory and then multiplying by 1048576 (0x100000). Note that the file names are in hexadecimal. It is usually easiest to specify the option value in hexadecimal too. For example, if 0007 is the smallest entry in pg_xact, -u 0x700000 will work (five trailing zeroes provide the proper multiplier).

  • -x _xid—​next-transaction-id=_xid

    • Manually set the next transaction ID.

    • A safe value can be determined by looking for the numerically largest file name in the directory pg_xact under the data directory, adding one, and then multiplying by 1048576 (0x100000). Note that the file names are in hexadecimal. It is usually easiest to specify the option value in hexadecimal too. For example, if 0011 is the largest entry in pg_xact, -x 0x1200000 will work (five trailing zeroes provide the proper multiplier).

Note

pg_resetwal 本身会查找 pg_wal 中的文件，并选择超出最后一个现有文件的文件名的默认 -l 设置。因此，只有在你了解目前在 pg_wal 中不存在的 WAL 分段文件（例如脱机存档中的条目）或 pg_wal 的内容已完全丢失时，才需要手动调整 -l 。

pg_resetwal itself looks at the files in pg_wal and chooses a default -l setting beyond the last existing file name. Therefore, manual adjustment of -l should only be needed if you are aware of WAL segment files that are not currently present in pg_wal, such as entries in an offline archive; or if the contents of pg_wal have been lost entirely.

Note

虽然 pg_resetwal 会将 WAL 开始地址设置为超出最新存在的 WAL 分段文件，但某些分段大小更改可能会导致以前 WAL 文件名称重复使用。建议将 -l 与此选项结合使用，以手动设置 WAL 开始地址（如果 WAL 文件名称重叠会导致存档策略出现问题）。

While pg_resetwal will set the WAL starting address beyond the latest existing WAL segment file, some segment size changes can cause previous WAL file names to be reused. It is recommended to use -l together with this option to manually set the WAL starting address if WAL file name overlap will cause problems with your archiving strategy.

Environment

  • PG_COLOR

    • Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

Notes

当服务器正在运行时，不可使用此命令。如果在 data 目录中找到服务器锁定文件， pg_resetwal 将拒绝启动。如果服务器崩溃，则可能遗留了一个锁定文件；在这种情况下，你可以删除锁定文件以允许 pg_resetwal 运行。但在此之前，要确保没有服务器进程仍在运行。

This command must not be used when the server is running. pg_resetwal will refuse to start up if it finds a server lock file in the data directory. If the server crashed then a lock file might have been left behind; in that case you can remove the lock file to allow pg_resetwal to run. But before you do so, make doubly certain that there is no server process still alive.

pg_resetwal 仅适用于同一主版本服务器。

pg_resetwal works only with servers of the same major version.

See Also

pg_controldata

pg_controldata