Since version 9.3 PostgreSQL introduced the ability of checksums. The documentation says "Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent". So, PostgreSQL's developers strongly recommended to use this mechanism for production systems. This can be done by specifying the "--data-checksums" option when initializing PostgreSQL cluster using initdb. In PostgreSQL 11, it became possible to check existed cluster using pg_verifiry_checksums utility. Since PostgreSQL 12, the utility was renamed to pg_checksums and now can enable, disable or check data checksums in a database cluster. But what if your PostgreSQL version is prior to 11 or 12 and you need to check (enable/disable) checksums in the existed cluster?
The good news - the utility is available in github and supports Postgres starting from 9.3. Not so good news - if you are not running PostgreSQL on Debian/Ubuntu, you will have to build this utility from source. We could not find any manuals for this topic, so hoped this article would be useful for professionals looking for such solution. Please see bleow the steps to build pg_cheksksums for PostgreSQL 10 and earlier versions running on CentOS 7.
Download the source code from github:
root@$01 git clone https://github.com/credativ/pg_checksums.git
Install postgresql-devel package and gcc:
root@01$ yum install postgresql10-devel.x86_64 root@01$ yum install gcc
Build the utility from source:
root@01$ cd /pg_checksums root@01$ make root@01$ make install
If you got an error like:
root@01$ sudo make USE_PGXS=1 gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -I. -I. -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o pg_checksums.o pg_checksums.c pg_checksums.c:29:30: fatal error: storage/checksum.h: No such file or directory #include "storage/checksum.h"
then you need to explicitly specify the location of /pg_config:
root@01$ make PG_CONFIG=/usr/pgsql-10/bin/pg_config root@01$ make install
Check if the checksums are enabled in the cluster:
root@01$ su postgres postgres@01$ psql -c 'SHOW data_checksums;' ----------------------------------------- data_checksums off (1 row)
Before enable checksums we need to stop PostgreSQL server:
root@01$ systemctl stop postgresql root@01$ /usr/pgsql-10/bin/pg_controldata -D /var/lib/pgsql/10/data/ | grep state Database cluster state: shut down
Execute command:
root@01$ pg_checksums -D /var/lib/pgsql-10/data/ --enable Checksum operation completed Files scanned: 961 Blocks scanned: 3092 pg_checksums: syncing data directory pg_checksums: updating control file Checksums enabled in cluster
We can check state of checksums using pg_controldata:
root@01$ /usr/pgsql-10/bin/pg_controldata -D /var/lib/pgsql/10/data/ | grep checksum Data page checksum version: 1
Checksums are enabled. So, if there is corruption of I/O system, Postgres will inform about it. We can set monitoring tools to issue an alert in case of such incidents and action to fix the issue as soon as possible. In addition, using this utility, we can validate backups taken with pg_basebackup. These topics will be discovered in the next blogs.
Conclusion
Using checksums on data pages is strongly advised in production environments to detect corruption by the I/O system. pg_checksums utility is a powerful tool we can use PostgreSQL 10 and earlier versions. There is a ready package for Debian/Ubuntu. But if you are running on other OS - you will have to build the utility from source.