Building pg_checksums utility for PostgreSQL 10 and earlier versions on CentOS 7

Building pg_checksums utility for PostgreSQL 10 and earlier versions on CentOS 7

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. 

Michael Goldberg
No Comments

Post a Comment

Comment
Name
Email
Website

This site uses Akismet to reduce spam. Learn how your comment data is processed.