Skip to content
Hands-on IT IT Drills

Day 13: Major Upgrade

Goal: You upgrade a cluster with data and extensions from PostgreSQL 17 to 18 – once with pg_upgradecluster, once by hand with pg_upgrade --check and --link – and check statistics and extensions. · Duration: approx. 90 min · Prerequisite: Day 12

Briefing

  • Minor updates (18.6 → 18.7) only swap binaries. A major upgrade changes the format of the system catalogs: data has to be migrated.
  • pg_dump/restore is safe and slow. pg_upgrade takes over the data files and only rebuilds the catalogs – minutes instead of hours, with --link even seconds.
  • Debian/Ubuntu package this as pg_upgradecluster: create a new cluster, carry over the configuration, swap ports.
  • Today: upgrade 17 → 18 on pg2. At the time of writing, PostgreSQL 19 was only available in the PGDG repository as a release candidate (component 19) – check with apt-cache policy postgresql-19 whether it is in main by now.
  pg2:  17/upgrade :5433 --pg_upgradecluster--> 18/upgrade :5433 (17 -> :5434)
        17/zwei    :5435 --pg_upgrade --link--> 18/zwei    :5436
        18/main    :5432  stays untouched

Drill 1: Is PostgreSQL 19 here yet?

Before every planned upgrade: which versions does the repository offer? On pg2:

sudo apt-get update
apt-cache policy postgresql-17 postgresql-19 | grep -E '^post|Candidate'
ARCH=$(dpkg --print-architecture)
P=https://apt.postgresql.org/pub/repos/apt/dists/noble-pgdg/19/binary-$ARCH/Packages
curl -s $P | grep -A1 '^Package: postgresql-19$' | grep Version

Expected:

postgresql-17:
  Candidate: 17.12-1.pgdg24.04+1
postgresql-19:
  Candidate: (none)
Version: 19~rc2-1.pgdg24.04+1
Version: 19~rc1-1.pgdg24.04+1
Version: 19~beta4-1.pgdg24.04+1

PostgreSQL 19 exists only as a release candidate in component 19 (Day 0). Once 19 appears in main, everything here works the same way with 17 → 18 replaced by 18 → 19. We practice with the path that exists today: 17 → 18.

Save your progress? Sign up for free

Drill 2: A cluster with PostgreSQL 17

Install PostgreSQL 17 next to 18 – PGDG packages coexist, each version has its own directory under /usr/lib/postgresql:

sudo apt-get install -y postgresql-17 postgresql-client-17
pg_lsclusters
sudo pg_createcluster 17 upgrade --start
pg_lsclusters

Expected (abridged):

Setting up postgresql-17 (17.12-1.pgdg24.04+1) ...
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...
Creating new PostgreSQL cluster 17/upgrade ...
Data page checksums are disabled.
Ver Cluster Port Status Owner    Data directory                 Log file
17  upgrade 5433 online postgres /var/lib/postgresql/17/upgrade ...
18  main    5432 online postgres /var/lib/postgresql/18/main    ...

This time no 17/main is created automatically, because a cluster already exists. We name the cluster upgrade, because pg_upgradecluster keeps the name – 17/main would become 18/main, and that one already exists. Note: PostgreSQL 17 creates clusters without checksums.

Now data, pg_stat_statements (needs shared_preload_libraries) and amcheck:

echo "shared_preload_libraries = 'pg_stat_statements'" \
  | sudo -u postgres tee /etc/postgresql/17/upgrade/conf.d/tag13.conf
sudo pg_ctlcluster 17 upgrade restart
sudo -u postgres createdb -p 5433 tag13
sudo -u postgres pgbench -p 5433 -i -s 10 -q tag13
sudo -u postgres psql -p 5433 -d tag13 -c 'CREATE EXTENSION pg_stat_statements' \
  -c 'CREATE EXTENSION amcheck'
sudo -u postgres pgbench -p 5433 -T 10 -c 2 tag13 | grep tps
sudo -u postgres psql -p 5433 -d tag13 -c '\dx'
sudo -u postgres psql -p 5433 -d tag13 -Atc 'SELECT sum(abalance) FROM pgbench_accounts'

Expected (abridged):

shared_preload_libraries = 'pg_stat_statements'
done in 4.88 s (drop tables 0.00 s, create tables 0.01 s, client-side generate ...
CREATE EXTENSION
CREATE EXTENSION
tps = 481.149597 (without initial connection time)
        Name        | Version | Default version |   Schema   | ...
--------------------+---------+-----------------+------------+----
 amcheck            | 1.4     | 1.4             | public     | ...
 pg_stat_statements | 1.11    | 1.11            | public     | ...
 plpgsql            | 1.0     | 1.0             | pg_catalog | ...
-204371

The sum of the account balances is our checksum for after the upgrade.

Save your progress? Sign up for free

Drill 3: The Debian way – pg_upgradecluster

The default method of pg_upgradecluster is dump (slow, safe); -m upgrade uses pg_upgrade and copies the data files. Check first, then go:

sudo pg_upgradecluster --check -v 18 17 upgrade
sudo pg_upgradecluster -v 18 -m upgrade 17 upgrade
pg_lsclusters

Expected (abridged):

Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
...
Performing Upgrade
------------------
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Restoring database schemas in the new cluster                 ok
Copying user relation files                                   ok
Checking for extension updates                                notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
    /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
    /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-only
pg_upgradecluster: pg_upgrade output scripts are in /var/log/postgresql/pg_upgradec...
Disabling automatic startup of old cluster...
Starting upgraded cluster on port 5433...
Running finish phase upgrade hook scripts ...
vacuumdb: processing database "tag13": Generating minimal optimizer statistics ...
...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 17 upgrade
Ver Cluster Port Status Owner    Data directory                 Log file
17  upgrade 5434 down   postgres /var/lib/postgresql/17/upgrade ...
18  main    5432 online postgres /var/lib/postgresql/18/main    ...
18  upgrade 5433 online postgres /var/lib/postgresql/18/upgrade ...

The new cluster takes over port 5433 – clients notice nothing. The old one moves to 5434 and is set to manual (does not start at boot). The hook script /etc/postgresql-common/pg_upgradecluster.d/analyze has already run the two vacuumdb calls. The upgrade took 20 seconds here.

Save your progress? Sign up for free

Drill 4: Follow-up – configuration and extensions

First check whether everything is really there:

sudo -u postgres psql -p 5433 -d tag13 -Atc 'SHOW server_version' \
  -c 'SHOW data_checksums' -c 'SELECT sum(abalance) FROM pgbench_accounts'
sudo -u postgres psql -p 5433 -d tag13 -c 'SELECT count(*) FROM pg_stat_statements'
ls /etc/postgresql/17/upgrade/conf.d/ /etc/postgresql/18/upgrade/conf.d/

Expected:

18.7 (Ubuntu 18.7-1.pgdg24.04+2)
off
-204371
ERROR:  pg_stat_statements must be loaded via "shared_preload_libraries"
/etc/postgresql/17/upgrade/conf.d/:
tag13.conf

/etc/postgresql/18/upgrade/conf.d/:

The data is correct, checksums stay off (as in the old cluster). But: pg_upgradecluster carries over postgresql.conf, not the files in conf.d. Bring it over, restart, then update the extensions with the supplied script:

sudo -u postgres cp /etc/postgresql/17/upgrade/conf.d/tag13.conf \
  /etc/postgresql/18/upgrade/conf.d/
sudo pg_ctlcluster 18 upgrade restart
L=$(sudo ls -d /var/log/postgresql/pg_upgradecluster-17-18-upgrade.*)
sudo cat $L/update_extensions.sql
sudo cat $L/update_extensions.sql | sudo -u postgres psql -p 5433
sudo -u postgres psql -p 5433 -d tag13 -c '\dx' \
  -c 'SELECT count(*) FROM pg_stat_statements'

Expected (abridged):

\connect tag13
ALTER EXTENSION "pg_stat_statements" UPDATE;
ALTER EXTENSION "amcheck" UPDATE;
You are now connected to database "tag13" as user "postgres".
ALTER EXTENSION
ALTER EXTENSION
        Name        | Version | Default version |   Schema   | ...
--------------------+---------+-----------------+------------+----
 amcheck            | 1.5     | 1.5             | public     | ...
 pg_stat_statements | 1.12    | 1.12            | public     | ...
 plpgsql            | 1.0     | 1.0             | pg_catalog | ...
 count
-------
     3

pg_upgrade carries over extensions in their old version; the new binaries come from postgresql-18. ALTER EXTENSION … UPDATE brings the SQL objects up to the level of the new version. Third-party extensions (PostGIS, TimescaleDB) must be installed for the new version beforehand.

Save your progress? Sign up for free

Drill 5: By hand – pg_upgrade –check

Second run, this time without Debian’s help. Old cluster 17/zwei with data, new empty cluster 18/zwei, both stopped:

sudo pg_createcluster 17 zwei --start
sudo -u postgres createdb --cluster 17/zwei tag13
sudo -u postgres pgbench -p 5435 -i -s 5 -q tag13
sudo -u postgres psql -p 5435 -d tag13 -c 'CREATE EXTENSION amcheck'
sudo -u postgres psql -p 5435 -d tag13 -Atc \
  "SELECT count(*) FROM pg_stats WHERE schemaname = 'public'"
sudo pg_createcluster 18 zwei
sudo pg_ctlcluster 17 zwei stop
pg_lsclusters | grep zwei

Expected (abridged):

CREATE EXTENSION
11
17  zwei    5435 down   postgres /var/lib/postgresql/17/zwei    ...
18  zwei    5436 down   postgres /var/lib/postgresql/18/zwei    ...

11 columns have planner statistics (pgbench -i analyzes at the end). pg_upgrade needs both binary directories, both data directories and – because of the Debian split – the path of the respective postgresql.conf. It writes logs and scripts into the current directory, which must be writable for postgres:

cd /tmp
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/zwei -D /var/lib/postgresql/18/zwei \
  -o '-c config_file=/etc/postgresql/17/zwei/postgresql.conf' \
  -O '-c config_file=/etc/postgresql/18/zwei/postgresql.conf' --check

Expected:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok

old cluster does not use data checksums but the new one does
Failure, exiting

The trap from Drill 2: 17 creates clusters without checksums, 18 with them. pg_upgrade demands that they match. pg_upgradecluster solved this silently by creating the new cluster without checksums. Better: enable them in the old cluster – offline; pg_checksums rewrites every file in the process:

sudo -u postgres /usr/lib/postgresql/17/bin/pg_checksums --enable \
  -D /var/lib/postgresql/17/zwei
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/zwei -D /var/lib/postgresql/18/zwei \
  -o '-c config_file=/etc/postgresql/17/zwei/postgresql.conf' \
  -O '-c config_file=/etc/postgresql/18/zwei/postgresql.conf' --check

Expected (abridged):

Checksum operation completed
Files scanned:   1257
Blocks scanned:  13367
Checksums enabled in cluster
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database connection settings                         ok
Checking for valid logical replication slots                  ok
Checking for subscription state                               ok
Checking data type usage                                      ok
Checking for objects affected by Unicode update               ok
Checking for presence of required libraries                   ok
...
*Clusters are compatible*

--check changes nothing and even runs against a running old cluster – ideal for finding surprises weeks before the maintenance window. For large databases, pg_checksums --enable takes correspondingly long; in that case, create the new cluster with --no-data-checksums instead and enable checksums later.

Save your progress? Sign up for free

Drill 6: pg_upgrade –link

--link creates hard links instead of copies: no extra space, duration independent of the data volume. The price: as soon as the new cluster runs, the old one is unusable.

sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/zwei -D /var/lib/postgresql/18/zwei \
  -o '-c config_file=/etc/postgresql/17/zwei/postgresql.conf' \
  -O '-c config_file=/etc/postgresql/18/zwei/postgresql.conf' --link
ls /tmp/*.sql /tmp/*.sh

Expected (abridged):

Adding ".old" suffix to old "global/pg_control"               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from "/var/lib/postgresql/17/zwei/global/pg_control.old".
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking user relation files                                   ok
...
Upgrade Complete
----------------
/tmp/update_extensions.sql
/tmp/delete_old_cluster.sh

The whole run took 7 seconds. Now start it and prove that the files are shared and the planner statistics came along – before any ANALYZE:

sudo pg_ctlcluster 18 zwei start
F=$(sudo -u postgres psql -p 5436 -d tag13 -Atc \
  "SELECT pg_relation_filepath('pgbench_accounts')")
sudo stat -c '%h %n' /var/lib/postgresql/18/zwei/$F
sudo -u postgres psql -p 5436 -d tag13 -Atc \
  "SELECT count(*) FROM pg_stats WHERE schemaname = 'public'"
sudo -u postgres psql -p 5436 -d tag13 -c "SELECT attname, n_distinct
  FROM pg_stats WHERE tablename = 'pgbench_accounts' ORDER BY attname"
sudo -u postgres psql -p 5436 -d tag13 -c "SELECT relname, last_analyze, n_live_tup
  FROM pg_stat_user_tables ORDER BY relname"

Expected:

2 /var/lib/postgresql/18/zwei/base/16384/16397
11
 attname  | n_distinct
----------+------------
 abalance |          1
 aid      |         -1
 bid      |          5
 filler   |          1
(4 rows)

     relname      | last_analyze | n_live_tup
------------------+--------------+------------
 pgbench_accounts |              |          0
 pgbench_branches |              |          0
 pgbench_history  |              |          0
 pgbench_tellers  |              |          0
(4 rows)

Link count 2: the file belongs to the old and the new cluster at the same time. New in PostgreSQL 18: pg_upgrade carries over the planner statistics (pg_stats) – the new server plans sensibly right away instead of being blind until the first ANALYZE. Not carried over are extended statistics (CREATE STATISTICS) and the running counters in pg_stat_user_tables – hence n_live_tup = 0. The recommendation from above catches up on both; --missing-stats-only analyzes only what is missing:

sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb -p 5436 --all \
  --analyze-in-stages --missing-stats-only
sudo -u postgres psql -p 5436 -f /tmp/update_extensions.sql
sudo -u postgres psql -p 5436 -d tag13 -Atc \
  "SELECT extversion FROM pg_extension WHERE extname = 'amcheck'" \
  -c 'SHOW data_checksums' -c 'SELECT count(*) FROM pgbench_accounts'

Expected (abridged):

vacuumdb: processing database "tag13": Generating minimal optimizer statistics ...
vacuumdb: processing database "tag13": Generating medium optimizer statistics ...
vacuumdb: processing database "tag13": Generating default (full) optimizer stati...
ALTER EXTENSION
1.5
on
500000

In production, vacuumdb --all --analyze-only follows as well, to update the row counters too.

Save your progress? Sign up for free

Drill 7: Remove the old cluster

Only once the new cluster has been checked and a fresh backup exists. The Debian way is pg_dropcluster; the delete_old_cluster.sh generated by pg_upgrade would do the same, but only for the data directory:

sudo pg_dropcluster 17 upgrade
sudo pg_dropcluster 17 zwei
pg_lsclusters

Expected (abridged):

Ver Cluster Port Status Owner    Data directory                 Log file
18  main    5432 online postgres /var/lib/postgresql/18/main    ...
18  upgrade 5433 online postgres /var/lib/postgresql/18/upgrade ...
18  zwei    5436 online postgres /var/lib/postgresql/18/zwei    ...

With --link, deleting the old directory is harmless: the hard links keep the files alive for the new cluster.

Save your progress? Sign up for free

Exercise: Speed Drills

  1. Show all clusters with version, port and status. Target time: 5 s
  2. Show for a stopped cluster whether it has checksums. Target time: 40 s
  3. Show all extensions in tag13 whose installed version is outdated. Target time: 40 s
  4. Show whether an old cluster starts automatically after the upgrade. Target time: 20 s

Solution

Before Drill 7, on pg2:

pg_lsclusters
sudo -u postgres /usr/lib/postgresql/17/bin/pg_controldata \
  /var/lib/postgresql/17/upgrade | grep -E 'checksum version|cluster state'
sudo -u postgres psql -p 5433 -d tag13 -c "SELECT name, installed_version,
  default_version FROM pg_available_extensions
  WHERE installed_version <> default_version"
grep -E '^(manual|auto)' /etc/postgresql/17/upgrade/start.conf

Expected (abridged):

Ver Cluster Port Status Owner    Data directory                 Log file
17  upgrade 5434 down   postgres /var/lib/postgresql/17/upgrade ...
17  zwei    5435 down   postgres /var/lib/postgresql/17/zwei    ...
18  main    5432 online postgres /var/lib/postgresql/18/main    ...
18  upgrade 5433 online postgres /var/lib/postgresql/18/upgrade ...
18  zwei    5436 online postgres /var/lib/postgresql/18/zwei    ...
Database cluster state:               shut down
Data page checksum version:           0
 name | installed_version | default_version
------+-------------------+-----------------
(0 rows)

manual

Version 0 means: no checksums. No row for the extensions: Drill 4 updated them all.

Save your progress? Sign up for free

Checkpoint

sudo -u postgres psql -p 5433 -Atc 'SHOW server_version'

Expected: 18.7 (Ubuntu 18.7-1.pgdg24.04+2) – the cluster upgrade runs with 18 on the old port.

sudo -u postgres psql -p 5433 -d tag13 -Atc 'SELECT sum(abalance) FROM pgbench_accounts'

Expected: the same value as before the upgrade (-204371 in the example).

sudo -u postgres psql -p 5433 -d tag13 -Atc \
  "SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_statements'"

Expected: 1.12

sudo -u postgres psql -p 5436 -d tag13 -Atc 'SHOW data_checksums'

Expected: on

Challenge

How long would the database be down during the upgrade with --link, and what is your way back if an error shows up after 18/zwei has started? Answer both based on the output from Drill 6.

Hint: Read the message about pg_control.old carefully.

Solution

The downtime is the duration of pg_upgrade --link plus start and ANALYZE – here 7 seconds for the upgrade itself, independent of the data volume, because only catalogs are rebuilt. A way back to the old cluster exists only as long as the new one has not been started: remove .old from pg_control.old and start 17/zwei. After the start, 18 writes into the shared files – then only the backup from before helps (Day 9). Hence the rule: a verified backup before --link, or go without --link (copy, double the space) or use --clone on file systems with reflinks (XFS, Btrfs).

sudo -u postgres ls /var/lib/postgresql/17/zwei/global/ | grep pg_control

Expected (before Drill 7):

pg_control.old

Save your progress? Sign up for free

Cleanup

No later day needs PostgreSQL 17. pg2 returns to the state from Day 0:

sudo pg_dropcluster --stop 18 zwei
sudo pg_dropcluster --stop 18 upgrade
sudo rm -f /tmp/update_extensions.sql /tmp/delete_old_cluster.sh
sudo rm -rf /var/log/postgresql/pg_upgradecluster-17-18-upgrade.*
sudo apt-get purge -y postgresql-17 postgresql-client-17
sudo rm -rf /etc/postgresql/17
pg_lsclusters
ls /usr/lib/postgresql /etc/postgresql

Expected:

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...
/etc/postgresql:
18

/usr/lib/postgresql:
18

pg_dropcluster leaves /etc/postgresql/17/upgrade/conf.d/tag13.conf in place – it doesn’t delete files it didn’t create itself.

Interview Note

  • “How do you upgrade PostgreSQL to a new major version?” – pg_upgrade (with --check beforehand, --link for short downtime) or pg_dump/restore; with almost no downtime via logical replication (Day 11).
  • “What needs to be done after pg_upgrade?” – Reconcile the configuration, extensions with ALTER EXTENSION … UPDATE, statistics via vacuumdb (since 18 only what’s missing), set up backups again, remove the old cluster.
  • “Risk of --link?” – No way back after the new cluster has started; take a verified backup beforehand.

Enterprise Note

  • Rehearse the upgrade on a copy of production first, measure the duration, run application tests against the new version (read the deprecations in the release notes).
  • Standbys: with physical replication they are rebuilt using the rsync procedure from the docs or anew via pg_basebackup – they can’t simply come along.
  • Don’t upgrade the operating system in the same step: new glibc/ICU versions can change collation and invalidate indexes (ALTER COLLATION … REFRESH VERSION, REINDEX).
  • Major versions are supported for five years; upgrades belong in annual planning, not in the last week before end of support.
  • Backup tools (pgBackRest) need stanza-upgrade and a new full backup after the upgrade.