PostgreSQL full_page_write记录

PostgreSQL 在 checkpoint 之后在对数据页面的第一次写的时候会将整个数据页面写到 xlog 里面。

当出现主机断电或者OS崩溃时，redo操作时通过checksum发现“部分写”的数据页，并将xlog中保存的这个完整数据页覆盖当前损坏的数据页，然后再继续redo就可以恢复整个数据库了。

记住分两步进行。

full_page_writes (boolean)

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint. This is needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that contains a mix of old and new data. The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored, but at the price of increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)

Turning this parameter off speeds normal operation, but might lead to either unrecoverable data corruption, or silent data corruption, after a system failure. The risks are similar to turning off fsync, though smaller, and it should be turned off only based on the same circumstances recommended for that parameter.

Turning off this parameter does not affect use of WAL archiving for point-in-time recovery (PITR) (see Section 25.3).

This parameter can only be set in the postgresql.conf file or on the server command line. The default is on.

 

当此参数打开时，PostgreSQL服务器在检查点首次修改该页面之后，将每个磁盘页面的整个内容写入WAL。这是需要的，因为在操作系统崩溃期间正在进行的页面写入可能仅部分完成，导致包含旧数据和新数据的磁盘页面。通常存储在WAL中的行级更改数据将不足以在崩溃恢复期间完全还原此类页面。存储全页图像可确保页面能够正确恢复，但以增加必须写入WAL的数据量为代价。 （因为WAL重放总是从检查点开始，所以在检查点之后的每个页面的第一次更改过程中这样做是足够的，因此，减少全页写入成本的一种方法是增加检查点间隔参数。）

关闭此参数可以加速正常运行，但在系统发生故障后可能会导致数据不能恢复，或无声数据损坏。风险类似于关闭fsync，尽管更小，只能基于与该参数相同的情况关闭。

关闭此参数不影响使用WAL归档进行时间点恢复（PITR）（见第25.3节）。

此参数只能在postgresql.conf文件或服务器命令行中设置。默认值为开。

 

wal_log_hints (boolean)

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint, even for non-critical modifications of so-called hint bits.

If data checksums are enabled, hint bit updates are always WAL-logged and this setting is ignored. You can use this setting to test how much extra WAL-logging would occur if your database had data checksums enabled.

This parameter can only be set at server start. The default value is off.

 

当此参数打开时，PostgreSQL服务器在检查点之后的该页面的第一次修改期间将每个磁盘页面的整个内容写入WAL，即使对所谓的提示位进行非关键修改。

如果启用数据校验和，则提示位更新始终处于WAL状态，此设置将被忽略。 您可以使用此设置来测试如果您的数据库启用了数据校验和，将会发生多少额外的WAL日志记录。

此参数只能在服务器启动时设置。 默认值为off。