本文共 3609 字，大约阅读时间需要 12 分钟。
PostgreSQL , checksum , online modify , pg_verify_checksums , pg_enable_data_checksums , pg_disable_data_checksums
PostgreSQL的数据文件是以数据块组织的，由于数据块可能比文件系统的原子写更大，所以在某些情况下可能出现partial write（例如断点或者块设备异常），出现数据块数据损坏（不一致）的情况。为了满足可靠性要求，PG在设计之初已经考虑到这点，所以有了WAL的FULL PAGE WRITE，以及WAL的PAGE强制开启CHECKSUM的功能。如果主机异常或者文件系统异常等问题导致数据库异常关闭，那么数据库在下次启动时会进入RECOVERY模式，使用从上一个检查点开始的WAL，修复数据文件内的BLOCK。
既然WAL可以有CHECKSUM，那么数据文件是否有checksum呢？答案当然是有的，不过在PostgreSQL 11以前，CHECKSUM需要在数据库初始的时候决定，要么开启要么不开启。
PostgreSQL 11引入了一个功能点，可以在线开关checksum。
Allow on-line enabling and disabling of data checksums This makes it possible to turn checksums on in a live cluster, without the previous need for dump/reload or logical replication (and to turn it off). Enabling checkusm starts a background process in the form of a launcher/worker combination that goes through the entire database and recalculates checksums on each and every page. Only when all pages have been checksummed are they fully enabled in the cluster. Any failure of the process will revert to checksums off and the process has to be started. This adds a new WAL record that indicates the state of checksums, so the process works across replicated clusters. Authors: Magnus Hagander and Daniel Gustafsson Review: Tomas Vondra, Michael Banck, Heikki Linnakangas, Andrey Borodin
在线CHECKSUM转换，开启了一个后台进程对数据块进行校验和写checksum值，从而会引入一定的数据文件读写IO。以及写WAL的IO。所以设计时也考虑了调度的问题，同时考虑了在一个库中同时存在有checksum的BLOCK和没有CHECKSUM的block的问题。
pg_verify_checksums可以用来检查数据库的数据块是否有逻辑坏块。（即存储的checksum的值与计算得到的checksum值不一致）。
这个工具只能在停库时执行。
pg_verify_checksums — verify data checksums in an offline PostgreSQL database cluster Synopsis pg_verify_checksums [option] [[-D] datadir] Description pg_verify_checksums verifies data checksums in a PostgreSQL cluster. It must be run against a cluster that's offline. Options The following command-line options are available: -r relfilenode Only validate checksums in the relation with specified relfilenode. -f Force check even if checksums are disabled on cluster. -d Enable debug output. Lists all checked blocks and their checksum. -V --version Print the pg_verify_checksums version and exit. -? --help Show help about pg_verify_checksums command line arguments, and exit. Notes Can only be run when the server is offline.
9.26.7. Data Checksum Functions
The functions shown in Table 9.84 can be used to enable or disable data checksums in a running cluster. See Section 30.2 for details.
Table 9.84. Checksum SQL Functions
| Function | Return Type | Description |
|---|---|---|
| pg_enable_data_checksums([cost_delay int, cost_limit int]) | void | Initiates data checksums for the cluster. This will switch the data checksums mode to in progress and start a background worker that will process all data in the database and enable checksums for it. When all data pages have had checksums enabled, the cluster will automatically switch to checksums on. If cost_delay and cost_limit are specified, the speed of the process is throttled using the same principles as Cost-based Vacuum Delay. |
| pg_disable_data_checksums() | void | Disables data checksums for the cluster. |
通过pg_controldata可以查看当前PG集群的checksum状态
pg_controldata |grep checksumData page checksum version: 0
checksum除了可以用来判断逻辑块错误，同时也被pg_rewind这个数据库时间线回溯软件用来对比两个PG集群的差异，即pg_rewind起来checksum=on或者依赖wal_log_hints=on.
如果数据库是non checksum的，使用pg_enable_data_checksums函数在线打开checksum，会对所有BLOCK进行校验并写入校验值，产生大量的写数据文件的IO以及写WAL日志的IO。
数据库通过设置delay和每一轮的limit来设置调度，尽量的降低转换为CHECKSUM模式带来的IO风暴，使其平滑化。如果未设置delay,cost limit，则沿用数据库设置的vacuum的delay和cost limit。
转载地址：http://btttx.baihongyu.com/