Nachrichten und Blog zurück

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
off).

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
started.

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:

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:          0

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

# select pg_enable_data_checksums();
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:          1

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.