August 22, 2024
Summary: in this tutorial, you will learn how to enable data checksums with minimum downtime in PostgreSQL.
Table of Contents
Introduction
PostgreSQL has a feature called data checksum which is used to detect any storage-level corruption of data pages. However, a new PostgreSQL database cluster does not have this feature enabled by default. To activate this feature, you must specify the --data-checksums flag when initializing a new PostgreSQL database cluster with the initdb utility.
So what about clusters that are already running? Is there any way to enable data checksums for an existing cluster? Previously, the only way was to initialize a new cluster with data checksums and dump the database into it using dump-restore or logical replication. Unfortunately, these methods are slow. Starting from PostgreSQL 12, users can utilize a new built-in utility called pg_checksum to enable checksums in a cluster that was not initialized with this feature. However, this utility only works when the database server is offline. For a large production server, it may be difficult to schedule a significant amount of downtime. To avoid this issue, a primary-standby architecture can be created. Below are the steps that can be followed to achieve this.
How to do it…
Follow these steps to complete this tutorial:
1. Please create a set-up consisting of a primary and standby server using streaming replication to ensure data redundancy and availability. To set up a primary-standby system using streaming replication, please refer to Setting up Streaming Replication for detailed instructions. Once you have built this setup, you can use the following statement to verify that the replication is working correctly.
select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 234189
usesysid | 16402
usename | replicator
application_name | walreceiver
client_addr | 172.31.30.60
client_hostname |
client_port | 42950
backend_start | 2024-03-12 16:40:07.668783-05
backend_xmin |
state | streaming
sent_lsn | 9445/8E82D2F8
write_lsn | 9445/8E82D2F8
flush_lsn | 9445/8E82D2F8
replay_lsn | 9445/8E82D2F8
write_lag | 00:00:00.000344
flush_lag | 00:00:00.000475
replay_lag | 00:00:00.000674
sync_priority | 0
sync_state | async
reply_time | 2024-03-14 00:45:18.968746-05
2. To ensure high availability and minimal downtime, configure an automatic failover/switchover for the primary-standby nodes with any HA tool. In this example, Repmgr has been used as a High Availability (HA) tool. To set up repmgr, please refer to Automatic failover using repmgr for detailed instructions.
3. After configuring repmgr correctly, both nodes will be visible in the cluster. In this example, repmgr will be used for switchover.
$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 1 | host=172.31.26.9 user=postgres dbname=postgres password=postgres connect_timeout=2
2 | node2 | standby | running | node1 | default | 100 | 1 | host=172.31.30.60 user=postgres dbname=postgres password=postgres connect_timeout=2
4. Confirm that the checksum is disabled on the standby node.
show data_checksums;
data_checksums
----------------
off
(1 row)
5. Stop the standby database cluster.
$ sudo systemctl stop postgresql@14-main
6. Enable checksum on the standby database cluster.
$ /usr/lib/postgresql/14/bin/pg_checksums --pgdata /var/lib/postgresql/14/main --enable --progress
25/25 MB (100%) computed
Checksum operation completed
Files scanned: 942
Blocks scanned: 3262
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster
7. Start the standby database cluster.
$ sudo systemctl start postgresql@14-main
8. Verify the status of the checksum on the standby node, now this must be enabled.
show data_checksums;
data_checksums
----------------
on
(1 row)
9. Confirm that standby is fully synchronized with the primary and there is no lag.
select application_name,state,pg_current_wal_lsn() as current_wal,replay_lsn,replay_lag,
pg_size_pretty((pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))) as size
from pg_stat_replication order by pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) desc;
application_name | state | current_wal | replay_lsn | replay_lag | size
------------------+-----------+---------------+---------------+-----------------+---------
walreceiver | streaming | 10BC/BC4B62E0 | 10BC/BC4B62E0 | 00:00:00.000574 | 0 bytes
(1 row)
10. Once this gets confirmed there is no lag between primary and standby, perform a dry run for the switchover to confirm everything is perfect.
$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf standby switchover --dry-run
NOTICE: checking switchover on node "node2" (ID: 2) in --dry-run mode
INFO: SSH connection to host "172.31.26.9" succeeded
INFO: able to execute "repmgr" on remote host "172.31.26.9"
INFO: 1 walsenders required, 10 available
INFO: demotion candidate is able to make replication connection to promotion candidate
INFO: 0 pending archive files
INFO: replication lag on this standby is 0 seconds
NOTICE: attempting to pause repmgrd on 2 nodes
NOTICE: local node "node2" (ID: 2) would be promoted to primary; current primary "node1" (ID: 1) would be demoted to standby
INFO: following shutdown command would be run on node "node1":
"pg_ctl -D '/var/lib/postgresql/14/main' -W -m fast stop"
INFO: parameter "shutdown_check_timeout" is set to 60 seconds
INFO: prerequisites for executing STANDBY SWITCHOVER are met
11. Perform actual switchover.
$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf standby switchover
NOTICE: executing switchover on node "node2" (ID: 2)
NOTICE: attempting to pause repmgrd on 2 nodes
NOTICE: local node "node2" (ID: 2) will be promoted to primary; current primary "node1" (ID: 1) will be demoted to standby
NOTICE: stopping current primary node "node1" (ID: 1)
NOTICE: issuing CHECKPOINT on node "node1" (ID: 1)
DETAIL: executing server command "pg_ctlcluster 14 main -m f stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/D000028
NOTICE: promoting standby to primary
DETAIL: promoting server "node2" (ID: 2) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "node2" (ID: 2) was successfully promoted to primary
NOTICE: node "node2" (ID: 2) promoted to primary, node "node1" (ID: 1) demoted to standby
NOTICE: switchover was successful
DETAIL: node "node2" is now primary and node "node1" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully
12. Now the old standby server acts like the new primary node.
$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------
1 | node1 | standby | running | node2 | default | 100 | 1 | host=172.31.26.9 user=postgres dbname=postgres password=postgres connect_timeout=2
2 | node2 | primary | * running | | default | 100 | 2 | host=172.31.30.60 user=postgres dbname=postgres password=postgres connect_timeout=2
13. Stop the new standby (ex-primary).
$ pg_ctlcluster 14 main -m f stop
$ pg_ctlcluster 14 main -m f status
pg_ctl: no server running
14. Enable checksum on new standby (ex-primary).
$ /usr/lib/postgresql/14/bin/pg_checksums --pgdata /var/lib/postgresql/14/main --enable
Checksum operation completed
Files scanned: 943
Blocks scanned: 3262
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster
15. Start the new standby (ex-primary).
$ pg_ctlcluster 14 main -m f start
$ pg_ctlcluster 14 main -m f status
pg_ctl: server is running (PID: 27480)
/usr/lib/postgresql/14/bin/postgres "-D" "/var/lib/postgresql/14/main" "-c" "config_file=/etc/postgresql/14/main/postgresql.conf"
16. Verify the status of the checksum on new standby (ex-primary). Now this must be enabled.
show data_checksums;
data_checksums
----------------
on
(1 row)
17. Check the current cluster status in repmgr.
$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------
1 | node1 | standby | running | node2 | default | 100 | 2 | host=172.31.26.9 user=postgres dbname=postgres password=postgres connect_timeout=2
2 | node2 | primary | * running | | default | 100 | 2 | host=172.31.30.60 user=postgres dbname=postgres password=postgres connect_timeout=2
18. Confirm that the standby is fully synchronized with the new primary and there is no lag.
select application_name,state,pg_current_wal_lsn() as current_wal,replay_lsn,replay_lag,
pg_size_pretty((pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))) as size
from pg_stat_replication order by pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) desc;
-[ RECORD 1 ]----+----------------
application_name | walreceiver
state | streaming
current_wal | 14F68/16C14000
replay_lsn | 14F68/16C14000
replay_lag | 00:00:00.000624
size | 0 bytes
19. Once this has been confirmed there is no lag, perform switchover again to get the original setup of primary-standby.
$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf standby switchover
NOTICE: executing switchover on node "node1" (ID: 1)
NOTICE: attempting to pause repmgrd on 2 nodes
NOTICE: local node "node1" (ID: 1) will be promoted to primary; current primary "node2" (ID: 2) will be demoted to standby
NOTICE: stopping current primary node "node2" (ID: 2)
NOTICE: issuing CHECKPOINT on node "node2" (ID: 2)
DETAIL: executing server command "pg_ctlcluster 14 main -m f stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/F000028
NOTICE: promoting standby to primary
DETAIL: promoting server "node1" (ID: 1) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "node1" (ID: 1) was successfully promoted to primary
NOTICE: node "node1" (ID: 1) promoted to primary, node "node2" (ID: 2) demoted to standby
NOTICE: switchover was successful
DETAIL: node "node1" is now primary and node "node2" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully
20. Validate the cluster output to confirm it reflects the original setup.
$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 3 | host=172.31.26.9 user=postgres dbname=postgres password=postgres connect_timeout=2
2 | node2 | standby | running | node1 | default | 100 | 3 | host=172.31.30.60 user=postgres dbname=postgres password=postgres connect_timeout=2
Summary
Before executing the pg_checksum procedure on a production environment, there are some precautions that should be taken care of:
- If a lower environment is available, it is recommended to execute pg_checksum on that environment first. This will help in estimating the execution time and the lag generated during the activity.
- If a lower environment is not available, then it is suggested to build a clone of the Production environment and test it first to get the above-mentioned estimations.
In this article, we have discussed the procedure for enabling data checksums on an existing running cluster using pg_checksums with minimum downtime. I hope that this article provides you with informative and helpful insights.