Ahsan Hadi: 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 . 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 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. is the node being upgraded. It runs under Patroni with a physical standby, which matches the shape most production nodes have.The database is populated with the tables, all in the 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:

Drain traffic from the node, pause Patroni, stop the standby and then the node.
Install PostgreSQL 18, initialize a new data directory, and put the Spock settings in it.
Run
, which hard-links the data files instead of copying them.
Start the node on PostgreSQL 18 and restore Spock’s replication progress.
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.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. 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:Finally, turn off DDL replication on every node for the duration of the rollout, same as the rolling upgrade:

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:Pause Patroni so it does not restart Postgres behind your back, then stop the standby first and the node second: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 and show their subscriptions to as , and keep serving the application:

Step 2: Prepare the New Data Directory

Initialize the PostgreSQL 18 cluster. The first gotcha is checksums. PostgreSQL 18’s turns data checksums on by default, so if your old cluster has them off, leaving out is not enough. I did exactly that first, and stopped right away:Be explicit:The second gotcha is ordering. Spock refuses to load unless it is in , and needs to load it in the new cluster. Without the Spock settings in place, the check fails on required libraries: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: only exists on newer pgEdge builds such as 18.6. is new in PostgreSQL 18.

Step 3: Run pg_upgrade

Perform a test run from a directory you can write to, since writes its logs there:Then, run the real thing with :Because the old node was on PostgreSQL 17, 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:Everything looked right. Same row count, both Spock slots carried forward with . Then I checked subscriptions:The other nodes could read from , but could not receive anything. The log said why:This is the most important part of this post. Spock tracks how far each subscription has applied using replication origins, and 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:To prove nothing was lost, I wrote a row on while was in this broken state, before running the fix. A few seconds after the origins came back, it showed up on :Then run the write test in both directions, same as in the rolling post. All three nodes agreed:

Step 5: Hand the Node Back to Patroni

Point the node’s Patroni config at the new data directory and binaries:A 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: 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:This only works cleanly if the Spock settings live in in the Patroni config, because Patroni writes that section into etcd when it adopts the node. Leave them out and Patroni replaces and with its own defaults:Then resume and restart through Patroni:

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 . Patroni would not have it:The standby’s Patroni exits on that error, which means its REST API is gone and has nothing to talk to. Wipe the old standby directory and start its Patroni. With an empty data directory, Patroni runs from the new leader on its own: does not carry forward, so anything you set with is gone. Set again on the leader, so the other Spock nodes never get ahead of the standby:Then confirm the standby holds current copies of the Spock slots:The standby’s matching the primary’s is what tells you a failover would not lose the other nodes’ place.

Step 7: Finish Up

prints three follow ups at the end. Do all of them: updates any extensions whose version changed between the two builds. The two runs rebuild the statistics 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.