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.
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 How to Set Up Streaming Replication in PostgreSQL 12 for detailed instructions. Once you have built this setup, you can use the following statement to verify that the replication is working correctly.
|
1 |
postgres=# select * from pg_stat_replication;<br>-[ RECORD 1 ]----+------------------------------<br>pid | 234189<br>usesysid | 16402<br>usename | replicator<br>application_name | walreceiver<br>client_addr | 172.31.30.60<br>client_hostname |<br>client_port | 42950<br>backend_start | 2024-03-12 16:40:07.668783-05<br>backend_xmin |<br>state | streaming<br>sent_lsn | 9445/8E82D2F8<br>write_lsn | 9445/8E82D2F8<br>flush_lsn | 9445/8E82D2F8<br>replay_lsn | 9445/8E82D2F8<br>write_lag | 00:00:00.000344<br>flush_lag | 00:00:00.000475<br>replay_lag | 00:00:00.000674<br>sync_priority | 0<br>sync_state | async<br>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. However, the steps for setting up Repmgr have not been discussed in this blog to keep it simple. You can refer to this link for more information on Repmgr.
3. After configuring repmgr correctly, both nodes will be visible in the cluster. In this example, repmgr will be used for switchover.
|
1 |
postgres@ip-172-31-26-9:~$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf cluster show<br>ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string<br>----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------<br> 1 | node1 | primary | * running | | default | 100 | 1 | host=172.31.26.9 user=postgres dbname=postgres password=postgres connect_timeout=2<br> 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.
|
1 |
postgres@ip-172-31-30-60:~$ psql<br>psql (14.10 (Ubuntu 14.10-1.pgdg22.04+1))<br>Type "help" for help.<br>postgres=# show data_checksums;<br> data_checksums<br>----------------<br> off<br>(1 row) |
5. Stop the standby database cluster.
|
1 |
ubuntu@ip-172-31-30-60:~$ sudo systemctl stop postgresql@14-main<br>ubuntu@ip-172-31-30-60:~$ |
6. Enable checksum on the standby database cluster.
|
1 |
ubuntu@ip-172-31-30-60:~$ sudo su - postgres<br>postgres@ip-172-31-30-60:~$<br>postgres@ip-172-31-30-60:~$ /usr/lib/postgresql/14/bin/pg_checksums --pgdata /var/lib/postgresql/14/main --enable --progress<br>25/25 MB (100%) computed<br>Checksum operation completed<br>Files scanned: 942<br>Blocks scanned: 3262<br>pg_checksums: syncing data directory<br>pg_checksums: updating control file<br>Checksums enabled in cluster |
7. Start the standby database cluster.
|
1 |
ubuntu@ip-172-31-30-60:~$ sudo systemctl start postgresql@14-main |
8. Verify the status of the checksum on the standby node, now this must be enabled.
|
1 |
ubuntu@ip-172-31-30-60:~$ sudo su - postgres<br>postgres@ip-172-31-30-60:~$ psql<br>psql (14.10 (Ubuntu 14.10-1.pgdg22.04+1))<br>Type "help" for help.<br>postgres=# show data_checksums;<br> data_checksums<br>----------------<br> on<br>(1 row) |
9. Confirm that standby is fully synchronized with the primary and there is no lag.
|
1 |
postgres=# 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;<br>application_name | state | current_wal | replay_lsn | replay_lag | size<br>------------------+-----------+---------------+---------------+-----------------+---------<br>walreceiver | streaming | 10BC/BC4B62E0 | 10BC/BC4B62E0 | 00:00:00.000574 | 0 bytes<br>(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.
|
1 |
postgres@ip-172-31-30-60:~/.ssh$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf standby switchover --dry-run<br>NOTICE: checking switchover on node "node2" (ID: 2) in --dry-run mode<br>INFO: SSH connection to host "172.31.26.9" succeeded<br>INFO: able to execute "repmgr" on remote host "172.31.26.9"<br>INFO: 1 walsenders required, 10 available<br>INFO: demotion candidate is able to make replication connection to promotion candidate<br>INFO: 0 pending archive files<br>INFO: replication lag on this standby is 0 seconds<br>NOTICE: attempting to pause repmgrd on 2 nodes<br>NOTICE: local node "node2" (ID: 2) would be promoted to primary; current primary "node1" (ID: 1) would be demoted to standby<br>INFO: following shutdown command would be run on node "node1":<br>"pg_ctl -D '/var/lib/postgresql/14/main' -W -m fast stop"<br>INFO: parameter "shutdown_check_timeout" is set to 60 seconds<br>INFO: prerequisites for executing STANDBY SWITCHOVER are met |
11. Perform actual switchover.
|
1 |
postgres@ip-172-31-30-60:~$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf standby switchover<br>NOTICE: executing switchover on node "node2" (ID: 2)<br>NOTICE: attempting to pause repmgrd on 2 nodes<br>NOTICE: local node "node2" (ID: 2) will be promoted to primary; current primary "node1" (ID: 1) will be demoted to standby<br>NOTICE: stopping current primary node "node1" (ID: 1)<br>NOTICE: issuing CHECKPOINT on node "node1" (ID: 1)<br>DETAIL: executing server command "pg_ctlcluster 14 main -m f stop"<br>INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")<br>NOTICE: current primary has been cleanly shut down at location 0/D000028<br>NOTICE: promoting standby to primary<br>DETAIL: promoting server "node2" (ID: 2) using pg_promote()<br>NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete<br>NOTICE: STANDBY PROMOTE successful<br>DETAIL: server "node2" (ID: 2) was successfully promoted to primary<br>NOTICE: node "node2" (ID: 2) promoted to primary, node "node1" (ID: 1) demoted to standby<br>NOTICE: switchover was successful<br>DETAIL: node "node2" is now primary and node "node1" is attached as standby<br>NOTICE: STANDBY SWITCHOVER has completed successfully |
12. Now the old standby server acts like the new primary node.
|
1 |
postgres@ip-172-31-26-9:~$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf cluster show<br> ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string<br>----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------<br> 1 | node1 | standby | running | node2 | default | 100 | 1 | host=172.31.26.9 user=postgres dbname=postgres password=postgres connect_timeout=2<br> 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).
|
1 |
postgres@ip-172-31-26-9:~$ pg_ctlcluster 14 main -m f stop<br>postgres@ip-172-31-26-9:~$<br>postgres@ip-172-31-26-9:~$ pg_ctlcluster 14 main -m f status<br>pg_ctl: no server running |
14. Enable checksum on new standby (ex-primary).
|
1 |
postgres@ip-172-31-26-9:~$ /usr/lib/postgresql/14/bin/pg_checksums --pgdata /var/lib/postgresql/14/main --enable<br>Checksum operation completed<br>Files scanned: 943<br>Blocks scanned: 3262<br>pg_checksums: syncing data directory<br>pg_checksums: updating control file<br>Checksums enabled in cluster |
15. Start the new standby (ex-primary).
|
1 |
postgres@ip-172-31-26-9:~$ pg_ctlcluster 14 main -m f start<br>postgres@ip-172-31-26-9:~$ pg_ctlcluster 14 main -m f status<br>pg_ctl: server is running (PID: 27480)<br>/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.
|
1 |
postgres@ip-172-31-26-9:~$ psql<br>psql (14.10 (Ubuntu 14.10-1.pgdg22.04+1))<br>Type "help" for help.<br>postgres=# show data_checksums;<br>data_checksums<br>----------------<br> on<br>(1 row) |
17. Check the current cluster status in repmgr.
|
1 |
postgres@ip-172-31-26-9:~$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf cluster show<br>ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string<br>----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------<br> 1 | node1 | standby | running | node2 | default | 100 | 2 | host=172.31.26.9 user=postgres dbname=postgres password=postgres connect_timeout=2<br> 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.
|
1 |
postgres=# 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;<br>-[ RECORD 1 ]----+----------------<br>application_name | walreceiver<br>state | streaming<br>current_wal | 14F68/16C14000<br>replay_lsn | 14F68/16C14000<br>replay_lag | 00:00:00.000624<br>size | 0 bytes |
19. Once this has been confirmed there is no lag, perform switchover again to get the original setup of primary-standby.
|
1 |
postgres@ip-172-31-26-9:~$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf standby switchover<br>NOTICE: executing switchover on node "node1" (ID: 1)<br>NOTICE: attempting to pause repmgrd on 2 nodes<br>NOTICE: local node "node1" (ID: 1) will be promoted to primary; current primary "node2" (ID: 2) will be demoted to standby<br>NOTICE: stopping current primary node "node2" (ID: 2)<br>NOTICE: issuing CHECKPOINT on node "node2" (ID: 2)<br>DETAIL: executing server command "pg_ctlcluster 14 main -m f stop"<br>INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")<br>NOTICE: current primary has been cleanly shut down at location 0/F000028<br>NOTICE: promoting standby to primary<br>DETAIL: promoting server "node1" (ID: 1) using pg_promote()<br>NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete<br>NOTICE: STANDBY PROMOTE successful<br>DETAIL: server "node1" (ID: 1) was successfully promoted to primary<br>NOTICE: node "node1" (ID: 1) promoted to primary, node "node2" (ID: 2) demoted to standby<br>NOTICE: switchover was successful<br>DETAIL: node "node1" is now primary and node "node2" is attached as standby<br>NOTICE: STANDBY SWITCHOVER has completed successfully |
20. Validate the cluster output to confirm it reflects the original setup.
|
1 |
postgres@ip-172-31-26-9:~$ /usr/lib/postgresql/14/bin/repmgr -f /etc/repmgr.conf cluster show<br>ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string<br>----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------<br> 1 | node1 | primary | * running | | default | 100 | 3 | host=172.31.26.9 user=postgres dbname=postgres password=postgres connect_timeout=2<br> 2 | node2 | standby | running | node1 | default | 100 | 3 | host=172.31.30.60 user=postgres dbname=postgres password=postgres connect_timeout=2 |
Before executing the pg_checksum procedure on a production environment, there are some precautions that should be taken care of:
In this blog post, 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.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together. Run PostgreSQL in your production and mission-critical environments and easily deploy and orchestrate reliable PostgreSQL in Kubernetes.
