News & Blog back


Waiting for PostgreSQL 11: Online enabling of data checksums.

Enable data page checksums on the fly without the database interruption.

New interesting patch was committed on Thursday, 5 Apr.

Upd. Unfortunately this patch was reverted, and we can only dream about it and hope this feature will be added in a future.

Author: Magnus Hagander <[email protected]>
Date: Thu Apr 5 21:57:26 2018 +0200

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

Enabling checksums 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

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

A data page checksums functionality has been introduced for PostgreSQL 9.3. It was truly a very cool feature, that allowed detection of data corruption. At the same time, it had one major drawback – checksums could only be enabled at datadir initialization by initdb. The new patch removes this limitation, and it is now possible to enable or disable data page checksums online without a shutdown and re-initialization.
There are two functions that have been introduced. One of them is pg_enable_data_checksums() – it enables data page checksums for the database cluster. Here is how it works:

First, it waits for all active transactions to finish – it needs to make sure that there are no tables that haven’t been committed yet and hence are not visible. Transactions that have been initiated after the process started data handling, won’t block it, even if they have been launched during the waiting period.

Second, it starts the background worker which processes all data and enables checksums. Obviously, this process rewrites all data and on big databases might cause prolonged and significant IO to the system. To avoid performance degradation you can specify cost_delay and cost_limit as function’s arguments and throttle the process. This parameters work similarly to vacuum cost-based delay and limit. Since process works across replicated clusters, monitoring of the replication lag might be a good idea.

Third, before finishing, the process of checksum initiation waits for all existing temporary tables to be removed. So, in cases of long-lived temporary tables, it may need to terminate connections that used them to allow completion of the process. Finally, when checksums are enabled for all data pages, cluster switches the checksum on.

If during the enabling process, the cluster has stopped for any reason, this process would need to be restarted manually. There is no way to resume the work and instead the process starts from scratch.

If, for some reason, you would like to disable data page checksums, you can use second function pg_disable_data_checksums(), which does the opposite action – disables data checksums.

Enough words, let’s try it on my test cluster without data page checksums.

$ pg_controldata /pgdb/9999/data/ |grep "Data page checksum version"
Data page checksum version:

Next, let’s connect to Postgres and start enabling the checksums (without throttling).

# select pg_enable_data_checksums();

(1 row)

Function returns immediately, but it doesn’t mean that the process is now complete. The whole progress can be monitored through the pg_stat_activity view. In the “query” column you will see current progress or information about what process is waiting for.
# SELECT pid, wait_event_type ||'.'|| wait_event as wait_details, query from pg_stat_activity where backend_type in ('checksumhelper worker', 'checksumhelper launcher');

  pid |   wait_details  | query
 6671 | Timeout.PgSleep | Waiting for current transactions to finish (waiting for 594)

Hmm, if there is a block, the process will be waiting until it resolved to continue its work. Note, that it is not a PID in the brackets, it’s an XID – transaction identificator, so don’t try to terminate the processes with such PID. Once the blockage is resolved the handling of data will start.

  pid |     wait_details     | query
 6671 | IPC.BgWorkerShutdown | Waiting for worker in database postgres (pid 6673)
 6673 |                      | processing: public.pgbench_accounts (main block 96600/327869)

In the end of the process, it can also be blocked by temporary tables and has to wait until they are removed.

  pid |     wait_details     | query
  6671 | IPC.BgWorkerShutdown   | Waiting for worker in database postgres (pid 6673)
  6673 | Timeout.PgSleep        | Waiting for 5 temp tables to be removed

Once the process is finished, let’s check the cluster’s status again.
$ pg_controldata /pgdb/9999/data/ |grep "Data page checksum version"
Data page checksum version:

The version has been changed, which means our data are now protected with checksums. If you see version 2, it means process still works and not finished.

Patch details are available here as well as in the devel documentation here and here.

Huge thanks to Magnus Hagander and Daniel Gustafsson as well as all the reviewers for such nifty tool.

You may also like: