xf.is blog

When upgrading PostgreSQL cluster from one major version to another you cannot use pg_upgrade tool to upgrade/convert cluster with page checksums disabled to a cluster with page checksums enabled.

Instead you have to do dump and restore.

On Debian/Ubuntu based systems this is made easier using the pg_createcluster / pg_dropcluster commands.

Here I’m converting a PostgreSQL 11 cluster named 11/main (with no page checksums enabled) to new cluster named 11/data with page checksums enabled.

Start by editing /etc/postgresql-common/createcluster.conf and change

#initdb_options = ''

to

initdb_options = '--data-checksums'

pg_lsclusters should only show one cluster running:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

Create a new cluster with

$ pg_createcluster 11 data

This creates a empty cluster with page checksums enabled on another port (usually port 5433). Verify it with pg_lsclusters:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
11  data    5433 online postgres /var/lib/postgresql/11/data /var/log/postgresql/postgresql-11-data.log

Verify that page checksums are enabled with

$ psql -p 5433 -c "show data_checksums"

data_checksums
----------------
on
(1 row)

To dump and reload it is simplest to do (as the postgres user)

$ pg_dumpall --port=5432 --clean | psql --port=5433

This dumps all users and databases and restores them to the new cluster.

After this is done (assuming no transactions happened in the meantime) remember to copy pg_hba.conf and other settings from /etc/postgresql/11/main to /etc/postgresql/11/data.

Drop the old cluster with

$ pg_dropcluster 11 main

and change the port on 11/data to 5432.