PostgreSQL^® 11: Checksums and base backups

The second beta release of PostgreSQL 11 (which is now feature frozen) has been released recently. Time to look at some of the improvements that credativ has contributed in the area of checksums and backups.

Checksum verification during base backups

Since version 9.3 it is possible to enable checksums for the underlying storage of tables and indices during instance creation. Those checksums raise SQL errors if bit errors are encountered in their respective data pages, which allows for early discovery of storage issues. However those checksums are only verified if queries access the corrupted page. Running an explicit check is only possible with the forthcoming pg_verify_checksums application from version 11, however, it requires an offline instance in order to work.

Our change allows for verification of checksums during base backups. This is a good opportunity to verify the checksum consistency as all data blocks needs to be read during a base backup anyway. Checksum failures are logged as warnings (rather than errors) in order not to abort the whole base backup when they occur. The commonly used pg_basebackup application was extended with the –no-verify-checksums option which disables the verification.

Replication slots during base backups

The second change concerns the handling of replication slots by pg_basebackup during the setup of standby servers. Replication slots allow a primary to reserve the required transaction logs for the standby associated with the slot, even if it is temporarily down. Previous releases already allowed using a replication slot with pg_basebackup, however, this slot had to be created manually beforehand. Our change adds the new option -C or –create-slot and allows the on-shot creation of a standby clone including the usage of replication slots:

$ pg_basebackup -v -h primary.lan -D data --slot=standby1 --create-slot --write-recovery-conf
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/1D000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "standby1"
pg_basebackup: write-ahead log end point: 0/1D0000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
$ cat data2/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/.pgpass'' host=primary.lan
  port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'standby1'

Afterwards the standby just has to be started and will replicate automatically.

In addition, several other small improvements to pg_basebackup and its testsuite were done by us.

Parallel dump to /dev/null

A patch that did not make it into the release is presented here nevertheless: parallel pg_dump to /dev/null in the directory format. The reason for it is the common usage of pg_dump to check for errors in a PostgreSQL instance where /dev/null/ is used a target in order not to use additional disk space. The problem is that /dev/null can only be used in the custom format which does not allow dumping in parallel. The directory format supports multiple concurrent processes but cannot use /dev/null as target as it is not a directory. Our patch adds support for /dev/null as a target when using the directory format.

The reasons for the rejection were not technical issues with the patch but the fact that pg_dump is not a diagnostics tool and no special support for that should be included. Nevertheless, the submitted patch works and is being used by our clients. Versions of the patch for PostgreSQL 9.3, 9.4, 9.5, 9.6 and 10 are available.