Zero Downtime In-Place Major PostgreSQL Upgrades on a Distributed PostgreSQL Cluster

In my last post I walked through a rolling major version upgrade: add a brand new node on the new version, let it join the Spock cluster, retire the old node. It works well, but it assumes you can stand up an extra server for every node you need to upgrade. A lot of customers I talk to cannot. Either the hardware is not there, or the database is too big to hold a second full copy.

For them there is a second option. Upgrade each node in place with pg_upgrade --link. The node being upgraded does go down, but in a multi-master cluster the other nodes keep taking writes the whole time, so the application never loses a writable endpoint. The downtime belongs to one node, not to your users.

I tested this end to end on the same lab I used for the rolling post, taking one node from PostgreSQL 17.10 to 18.6, including its Patroni managed standby. It worked, but not without a few surprises that the standard pg_upgrade playbook will not warn you about. This post covers exactly what I ran.

The Starting Setup

A three node Spock cluster on two Rocky Linux ARM64 VMs. n3 is the node being upgraded. It runs under Patroni with a physical standby, which matches the shape most production nodes have.

The testdb database is populated with the pgbench tables, all in the default replication set. In production each of these would be its own server.

How the In-Place Upgrade Works

For each node, one at a time:

1. Drain traffic from the node, pause Patroni, stop the standby and then the node.

2. Install PostgreSQL 18, initialize a new data directory, and put the Spock settings in it.

3. Run pg_upgrade --link, which hard-links the data files instead of copying them.

4. Start the node on PostgreSQL 18 and restore Spock’s replication progress.

5. Hand the node back to Patroni and rebuild the standby from it.

You need at least three nodes, so that two stay up while one is being upgraded.

Before You Start

Check the node while it is still running.

-- on n3
SELECT version();
SHOW server_encoding;
SELECT extversion FROM pg_extension WHERE extname = 'spock';
/usr/pgsql-17/bin/pg_controldata ~/n3data | grep 'checksum version'
Data page checksum version:           0

Write down the encoding and the checksum setting. The new cluster has to match both.

Next, make sure the Spock package for the new major version is the same Spock version the cluster runs. pg_upgrade moves the database from the 17 binaries to the 18 binaries, so both sides need the same Spock. For a cluster on 5.0.13:

sudo dnf install -y pgedge-postgresql18-server pgedge-postgresql18-contrib pgedge-spock50_18-5.0.13

Finally, turn off DDL replication on every node for the duration of the rollout, same as the rolling upgrade:

ALTER SYSTEM SET spock.enable_ddl_replication = off;
SELECT pg_reload_conf();

Step 1: Take the Node Down

Drain traffic from the node at your load balancer or pooler, then confirm every subscription to and from it is caught up:

SELECT subscription_name, status FROM spock.sub_show_status();

Pause Patroni so it does not restart Postgres behind your back, then stop the standby first and the node second:

patronictl -c ~/patroni/n3.yml pause n3
/usr/pgsql-17/bin/pg_ctl -D ~/n3standby stop
/usr/pgsql-17/bin/pg_ctl -D ~/n3data stop

Stop the standby straight away. Once Postgres stops on the node, Patroni releases its leader lock even in maintenance mode. A healthy old standby sitting there is a promotion waiting to happen if anyone resumes Patroni too early.

At this point n1 and n2 show their subscriptions to n3 as down, and keep serving the application:

192.168.169.158|sub_n2_n1|replicating
192.168.169.158|sub_n3_n1|down
192.168.169.159|sub_n1_n2|replicating
192.168.169.159|sub_n3_n2|down

Step 2: Prepare the New Data Directory

Initialize the PostgreSQL 18 cluster. The first gotcha is checksums. PostgreSQL 18’s initdb turns data checksums on by default, so if your old cluster has them off, leaving out --data-checksums is not enough. I did exactly that first, and pg_upgrade --check stopped right away:

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

Be explicit:

/usr/pgsql-18/bin/initdb -D ~/n3data_new --encoding=UTF8 --no-data-checksums

The second gotcha is ordering. Spock refuses to load unless it is in shared_preload_libraries, and pg_upgrade needs to load it in the new cluster. Without the Spock settings in place, the check fails on required libraries:

could not load library "$libdir/spock": ERROR:  spock is not in shared_preload_libraries
In database: testdb

To avoid this error, add the Spock settings to the postgresql.conf file for the new node before running pg_upgrade, making sure the parameters in the new node are an exact match for the parameters in the old node:

cat >> ~/n3data_new/postgresql.conf <<'EOF'
port = 5442
listen_addresses = '*'
unix_socket_directories = '/tmp'
wal_level = logical
track_commit_timestamp = on
shared_preload_libraries = 'spock'
output_plugin_libraries = 'pgoutput, test_decoding, spock_output'
max_active_replication_origins = 10
max_replication_slots = 10
max_wal_senders = 10
max_worker_processes = 16
EOF
cp ~/n3data/pg_hba.conf ~/n3data_new/pg_hba.conf

output_plugin_libraries only exists on newer pgEdge builds such as 18.6. max_active_replication_origins is new in PostgreSQL 18.

Step 3: Run pg_upgrade

Perform a test run from a directory you can write to, since pg_upgrade writes its logs there:

cd ~
/usr/pgsql-18/bin/pg_upgrade \
  --old-datadir ~/n3data --new-datadir ~/n3data_new \
  --old-bindir /usr/pgsql-17/bin --new-bindir /usr/pgsql-18/bin \
  --check
Checking for valid logical replication slots                  ok
Checking for presence of required libraries                   ok
Checking for new cluster logical replication slots            ok

*Clusters are compatible*

Then, run the real thing with --link:

/usr/pgsql-18/bin/pg_upgrade \
  --old-datadir ~/n3data --new-datadir ~/n3data_new \
  --old-bindir /usr/pgsql-17/bin --new-bindir /usr/pgsql-18/bin \
  --link
Linking user relation files                                   ok
Restoring logical replication slots in the new cluster        ok
...
Upgrade Complete

Because the old node was on PostgreSQL 17, pg_upgrade carried its logical slots forward (the slots the other nodes stream from). On a PostgreSQL 15 or 16 source the slots are not preserved, and the other nodes’ subscriptions to this node need a full resync. Once you start the new cluster, the old one cannot be used again.

Step 4: Start the Node and Restore Spock’s Progress

Start the node on PostgreSQL 18:

/usr/pgsql-18/bin/pg_ctl -D ~/n3data_new -l ~/n3data_new/n3.log start

Everything looked right. Same row count, both Spock slots carried forward with failover = t. Then I checked subscriptions:

192.168.169.158:5432|sub_n3_n1|replicating
192.168.169.159:5432|sub_n3_n2|replicating
192.168.169.158:5442|sub_n1_n3|down
192.168.169.158:5442|sub_n2_n3|down

The other nodes could read from n3, but n3 could not receive anything. The log said why:

ERROR:  replication origin "spk_testdb_n1_sub_n1_n3" does not exist

This is the most important part of this post. Spock tracks how far each subscription has applied using replication origins, and pg_upgrade does not carry them over. It only does that for native PostgreSQL subscriptions. Without the origins, the upgraded node sends changes but never applies any, and nothing on the other nodes tells you.

The fix is to recreate each origin at the position the provider last saw this node confirm. That position is still sitting in the provider’s slot, which the upgrade never touched:

L1=$(psql -h 192.168.169.158 -p 5432 -d testdb -Atc \
  "SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name='spk_testdb_n1_sub_n1_n3';")
L2=$(psql -h 192.168.169.159 -p 5432 -d testdb -Atc \
  "SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name='spk_testdb_n2_sub_n2_n3';")

psql -p 5442 -d testdb \
  -c "SELECT pg_replication_origin_create('spk_testdb_n1_sub_n1_n3');" \
  -c "SELECT pg_replication_origin_advance('spk_testdb_n1_sub_n1_n3', '$L1');" \
  -c "SELECT pg_replication_origin_create('spk_testdb_n2_sub_n2_n3');" \
  -c "SELECT pg_replication_origin_advance('spk_testdb_n2_sub_n2_n3', '$L2');"

To prove nothing was lost, I wrote a row on n1 while n3 was in this broken state, before running the fix. A few seconds after the origins came back, it showed up on n3:

origin|spk_testdb_n1_sub_n1_n3|0/99032A08
origin|spk_testdb_n2_sub_n2_n3|0/9A038268
sub|sub_n1_n3|replicating
sub|sub_n2_n3|replicating
n3 tid1|180001

Then run the write test in both directions, same as in the rolling post. All three nodes agreed:

192.168.169.158:5432|1|180001
192.168.169.158:5432|2|180002
192.168.169.158:5432|3|180003
192.168.169.159:5432|1|180001
192.168.169.159:5432|2|180002
192.168.169.159:5432|3|180003
192.168.169.158:5442|1|180001
192.168.169.158:5442|2|180002
192.168.169.158:5442|3|180003

Step 5: Hand the Node Back to Patroni

Point the node’s Patroni config at the new data directory and binaries:

postgresql:
  data_dir: /home/pgedge/n3data_new
  bin_dir: /usr/pgsql-18/bin

A patronictl reload was not enough. Patroni kept looking at the old data directory until I restarted the Patroni process itself. Once it restarted, it warned me about the next problem:

WARNING: system ID has changed while in paused mode. Patroni will exit when resuming
unless system ID is reset: 7699871208021337719 != 7699876136052481372

pg_upgrade gives the cluster a new system identifier, and etcd still holds the old one. If we resume as is, Patroni exits. The fix is to remove the cluster record from etcd and let Patroni adopt the running instance again. Stop Patroni first. The cluster is paused, so Postgres keeps running and the node keeps serving Spock:

pkill -f 'patroni/n3_standby.yml'; pkill -f 'patroni/n3.yml'
patronictl -c ~/patroni/n3.yml remove n3
nohup patroni ~/patroni/n3.yml > ~/patroni/n3.log 2>&1 &

This only works cleanly if the Spock settings live in bootstrap.dcs in the Patroni config, because Patroni writes that section into etcd when it adopts the node. Leave them out and Patroni replaces wal_level and shared_preload_libraries with its own defaults:

bootstrap:
  dcs:
    postgresql:
      use_slots: true
      parameters:
        wal_level: logical
        track_commit_timestamp: on
        shared_preload_libraries: spock
        max_worker_processes: 16
        max_wal_senders: 10
        max_replication_slots: 10
        sync_replication_slots: on
        hot_standby_feedback: on

Then resume and restart through Patroni:

patronictl -c ~/patroni/n3.yml resume n3
patronictl -c ~/patroni/n3.yml restart n3 n3 --force
+ Cluster: n3 (7699876136052481372) -----+---------+----+
| Member | Host                 | Role   | State   | TL |
| n3     | 192.168.169.158:5442 | Leader | running |  1 |

Step 6: Rebuild the Standby

A physical standby cannot follow a primary across a major version, so it gets rebuilt from the upgraded node. I first tried starting its Patroni against the old data and asking for a reinit. Patroni would not have it:

CRITICAL: system ID mismatch, node n3_standby belongs to a different cluster

The standby’s Patroni exits on that error, which means its REST API is gone and reinit has nothing to talk to. Wipe the old standby directory and start its Patroni. With an empty data directory, Patroni runs pg_basebackup from the new leader on its own:

rm -rf ~/n3standby
nohup patroni ~/patroni/n3_standby.yml > ~/patroni/n3_standby.log 2>&1 &
INFO: replica has been created using basebackup
INFO: bootstrapped from leader 'n3'

| n3         | 192.168.169.158:5442 | Leader  | running   |  1 |   |
| n3_standby | 192.168.169.158:5452 | Replica | streaming |  1 | 0 |

pg_upgrade does not carry postgresql.auto.conf forward, so anything you set with ALTER SYSTEM is gone. Set synchronized_standby_slots again on the leader, so the other Spock nodes never get ahead of the standby:

ALTER SYSTEM SET synchronized_standby_slots = 'n3_standby';
SELECT pg_reload_conf();

Then confirm the standby holds current copies of the Spock slots:

primary|spk_testdb_n3_sub_n3_n1|t|0/B005658
primary|spk_testdb_n3_sub_n3_n2|t|0/B005658
standby|spk_testdb_n3_sub_n3_n1|t|t|0/B005658
standby|spk_testdb_n3_sub_n3_n2|t|t|0/B005658

The standby’s confirmed_flush_lsn matching the primary’s is what tells you a failover would not lose the other nodes’ place.

Step 7: Finish Up

pg_upgrade prints three follow ups at the end. Do all of them:

psql -p 5442 -d postgres -f ~/update_extensions.sql
/usr/pgsql-18/bin/vacuumdb -p 5442 --all --analyze-in-stages --missing-stats-only
/usr/pgsql-18/bin/vacuumdb -p 5442 --all --analyze-only
~/delete_old_cluster.sh

update_extensions.sql updates any extensions whose version changed between the two builds. The two vacuumdb runs rebuild the statistics pg_upgrade does not transfer. Only delete the old cluster when you are happy with the new one.

Then move on to the next node and repeat. When the last one is done, turn DDL replication back on.

Wrapping Up

In-place upgrades give you the same result as the rolling approach without an extra server per node. The node goes down for a few minutes, and the rest of the cluster carries the application while it does.

Most of what tripped me up never showed up as a failed status check:

• Spock’s replication origins do not survive pg_upgrade. Without them, the upgraded node stops applying changes from the other nodes, while those nodes still report it as healthy. Recreate the origins before you move on.

• The Spock settings must be in the new cluster before pg_upgrade, not after.

• PostgreSQL 18’s initdb enables checksums by default. Pass --no-data-checksums if the old cluster has none.

• The new system identifier breaks Patroni’s resume. Remove the cluster from etcd and let Patroni adopt the node again.

• Rebuild the standby by wiping its directory, not with reinit.

• Anything set with ALTER SYSTEM is lost and has to be set again.

Test writes in both directions before you touch the next node. Subscription status alone will not tell you if the upgraded node has gone deaf.