Every major PostgreSQL upgrade I have done on a single primary came with the same conversation: how long can the application be down? pg_upgrade --link is fast, but it is still an outage, and you still have to rebuild or resync the standbys afterwards.

A multi-master cluster gives you a different option. Spock is pgEdge's open-source PostgreSQL extension for multi-master logical replication, and with it every node takes writes, so you never have to upgrade a node in place. You add a brand new node on the new major version, let it join the cluster, move traffic to it, and retire the old one. Repeat for every node and the cluster ends up on the new version without the application ever losing a writable endpoint.

Starting with PostgreSQL 19, community Postgres offers a blue-green style upgrade built on pg_createsubscriber, pg_upgrade and the new sequence sync. This is not really ZERO downtime upgrade because the writes need to be paused for a certain period of time. At cutover, writes have to stop while the last changes drain, sequences are synced and the application is switched over. For a critical application, that pause is still an outage.

I tested this end to end on my two node pgEdge test cluster running PostgreSQL 17.10 with Spock 5.0.12, adding a PostgreSQL 18.6 node next to them. This post walks through exactly what I ran, including the things that tripped me up, so you can do the same thing on your own cluster.

One note before we start. pgEdge Control Plane is pgEdge's declarative orchestration tool for managing Postgres clusters. If your cluster runs under it, use its built-in major version upgrade instead. It drives the same zero downtime add node process through its API.

The Starting Setup

Before I touched anything, the cluster was a plain two node Spock setup on PostgreSQL 17, running on two Rocky Linux ARM64 VMs. Both nodes take writes.

NodeHostPortPostgreSQLSpockRole in this test
n1192.168.169.158543217.105.0.12Existing node, source for the new node
n2192.168.169.159543217.105.0.12Existing node
n3192.168.169.158544218.65.0.12New node, added by this procedure
n3_standby192.168.169.158545218.65.0.12Physical standby for n3, built in Step 4

The database is testdb, with the usual pgbench tables plus a couple of test tables, all in the default replication set. I put n3 and its standby on the same VM as n1 to keep the lab small. In production each node would be its own server.

How the Rolling Upgrade Works

The whole procedure is a per node loop. For each node you want to upgrade:

  • Build a fresh node on PostgreSQL 18 with the same Spock version as the cluster.

  • Add it to the cluster with Zodan, a set of scripts that ships with Spock and adds or removes a node with zero downtime. Its spock.add_node procedure copies the schema and data from an existing node and wires up subscriptions in both directions, while the cluster keeps taking writes.

  • Verify the new node both receives and sends changes.

  • If the old node had a physical standby, build a new standby from the new node under Patroni, a high availability manager for PostgreSQL. You don't upgrade the old standby, you replace it.

  • Move application traffic over and remove the old node with spock.remove_node.

Zodan ships with Spock as two SQL files, zodan.sql and zodremove.sql, which you load into the new node. The commands below use the nodes from the table above.

Before You Start

Start by looking at what the cluster is running today. You need the PostgreSQL version, the Spock version, the node names and the full role list.

-- on n1
SELECT version();
SELECT extversion FROM pg_extension WHERE extname = 'spock';
SELECT node_id, node_name FROM spock.node;
\du

In my cluster, that returns PostgreSQL 17.10, Spock 5.0.12, and nodes n1 and n2. The new node has to run the same Spock version, and every role on n1 has to exist on the new node too. Zodan checks both and refuses to continue if they do not match.

Next, turn off automatic DDL replication on every existing node for the duration of the upgrade:

-- on n1 and n2
ALTER SYSTEM SET spock.enable_ddl_replication = off;
SELECT pg_reload_conf();

That also means no schema changes from the application until the rollout is done. Plan the upgrade window around your deployment schedule.

Step 1: Build the PostgreSQL 18 Node

Install PostgreSQL 18 and the Spock package that matches the cluster. The pgEdge repo carries Spock 5.0 and 6.0 side by side, so pick the package name carefully. For a cluster on Spock 5.0.12 that is pgedge-spock50_18, not the 6.0 beta.

sudo dnf install -y pgedge-postgresql18-server pgedge-postgresql18-contrib pgedge-spock50_18
export PGBIN=/usr/pgsql-18/bin
$PGBIN/postgres --version
postgres (PostgreSQL) 18.6

Initialize the data directory and add the settings Spock needs:

mkdir -p ~/n3data && $PGBIN/initdb -D ~/n3data
cat >> ~/n3data/postgresql.conf <<'EOF'
port = 5442
listen_addresses = '*'
unix_socket_directories = '/tmp'
wal_level = logical
track_commit_timestamp = on
shared_preload_libraries = 'spock'
max_active_replication_origins = 10
max_replication_slots = 10
max_wal_senders = 10
max_worker_processes = 16
EOF
echo "host all all 192.168.169.0/24 scram-sha-256" >> ~/n3data/pg_hba.conf
$PGBIN/pg_ctl -D ~/n3data -l ~/n3data/n3.log start

On newer pgEdge builds, including the 18.6 build I used, check SHOW output_plugin_libraries;. If the parameter exists and does not list spock_output, add it and restart, otherwise the other nodes cannot create their slots on this node:

ALTER SYSTEM SET output_plugin_libraries = 'pgoutput', 'test_decoding', 'spock_output';

Finally, create the database, the roles that exist on n1, and the extensions:

-- on n3, as the initdb superuser
CREATE ROLE ahsan LOGIN SUPERUSER PASSWORD '...';
-- repeat for every other role \du showed on n1, with the same attributes
CREATE DATABASE testdb OWNER ahsan;
\c testdb
CREATE EXTENSION spock;
CREATE EXTENSION dblink;

Step 2: Add the Node with Zodan

Grab the Zodan scripts from the Spock release tag that matches your cluster, and load them into the new node:

curl -fO https://raw.githubusercontent.com/pgEdge/spock/v5.0.12/samples/Z0DAN/zodan.sql
curl -fO https://raw.githubusercontent.com/pgEdge/spock/v5.0.12/samples/Z0DAN/zodremove.sql
psql -p 5442 -d testdb -f zodan.sql

Then run add_node on the new node. I keep passwords out of the DSNs and put them in ~/.pgpass instead, because whatever DSN you pass here gets stored in spock.node_interface on every node in the cluster.

-- on n3
CALL spock.add_node(
  src_node_name := 'n1',
  src_dsn       := 'host=192.168.169.158 port=5432 dbname=testdb user=ahsan',
  new_node_name := 'n3',
  new_node_dsn  := 'host=192.168.169.158 port=5442 dbname=testdb user=ahsan',
  verb          := true
);

With verb := true it prints every phase: version check, prerequisite validation, slot creation on the other nodes, the structure and data copy from n1, and finally subscriptions from every node to the new one.

Step 3: Fix Replication Sets and Test Both Directions

At this point every subscription in the cluster showed replicating, so I ran a simple write test. An update on n1 arrived on n3. An update on n3 never left n3.

192.168.169.158:5432|2|0
192.168.169.159:5432|2|0
192.168.169.158:5442|2|333333

This is the most important thing in this post. The structure sync creates the tables on the new node, but with DDL replication turned off, nothing adds them to the new node's replication sets:

-- on n3
SELECT set_name, count(*) FROM spock.tables GROUP BY 1;

 set_name | count
----------+-------
          |     7

So the new node receives every change but publishes none, and every status check still says healthy. In a real upgrade, writes landing on the new node would never reach the rest of the cluster, and they would be gone for good once you retire the old node.

The fix is to copy replication set membership from the source node:

psql -h 192.168.169.158 -p 5432 -d testdb -Atc \
"SELECT format('SELECT spock.repset_add_table(%L, %L, false);', set_name, quote_ident(nspname)||'.'||quote_ident(relname)) FROM spock.tables WHERE set_name IS NOT NULL;" > n3_repsets.sql
psql -p 5442 -d testdb -v ON_ERROR_STOP=1 -f n3_repsets.sql

Then test writes in both directions, from an old node and from the new one:

psql -h 192.168.169.158 -p 5432 -d testdb -c "UPDATE pgbench_tellers SET tbalance = 111111 WHERE tid = 1;"
psql -h 192.168.169.158 -p 5442 -d testdb -c "UPDATE pgbench_tellers SET tbalance = 333334 WHERE tid = 2;"
sleep 3
for p in "192.168.169.158 5432" "192.168.169.159 5432" "192.168.169.158 5442"; do set -- $p
  psql -h $1 -p $2 -d testdb -Atc "SELECT '$1:$2', tid, tbalance FROM pgbench_tellers WHERE tid IN (1,2) ORDER BY tid;"
done
192.168.169.158:5432|1|111111
192.168.169.158:5432|2|333334
192.168.169.159:5432|1|111111
192.168.169.159:5432|2|333334
192.168.169.158:5442|1|111111
192.168.169.158:5442|2|333334

All three nodes agree. Do not skip this test - subscription status alone will not tell you if the new node is silently one-way.

Step 4: Rebuild the Standby from the New Node

The question I get most often is how the standby gets upgraded. It does not. You build a new standby from the PostgreSQL 18 node with pg_basebackup, and here, Patroni runs that for you.

Mark the Spock slots for failover first. The logical slots add_node creates on the new node, the ones the other nodes stream from, come out with failover = false. Native slot sync skips those, so after a promotion the other nodes would lose their place.

SELECT slot_name, failover FROM pg_replication_slots;

        slot_name        | failover
-------------------------+----------
 spk_testdb_n3_sub_n3_n1 | f
 spk_testdb_n3_sub_n3_n2 | f

A slot can only be altered while nothing is reading from it, so pause the two subscriptions, flip the flag over a replication connection, and resume. Your changes queue in the slots while the nodes are paused, so nothing is lost.

psql -h 192.168.169.158 -p 5432 -d testdb -c "SELECT spock.sub_disable('sub_n3_n1', true);"
psql -h 192.168.169.159 -p 5432 -d testdb -c "SELECT spock.sub_disable('sub_n3_n2', true);"
psql "host=127.0.0.1 port=5442 dbname=testdb replication=database" \
  -c "ALTER_REPLICATION_SLOT spk_testdb_n3_sub_n3_n1 (FAILOVER true);" \
  -c "ALTER_REPLICATION_SLOT spk_testdb_n3_sub_n3_n2 (FAILOVER true);"
psql -h 192.168.169.158 -p 5432 -d testdb -c "SELECT spock.sub_enable('sub_n3_n1', true);"
psql -h 192.168.169.159 -p 5432 -d testdb -c "SELECT spock.sub_enable('sub_n3_n2', true);"

Create the replication user. Patroni will take over the existing database rather than initialize a new one, so it never creates the users in its config. Do it yourself, along with a pg_hba.conf entry and a password for the superuser Patroni will connect as:

ALTER ROLE pgedge PASSWORD '...';
CREATE ROLE replicator LOGIN REPLICATION PASSWORD '...';
echo "host replication replicator 192.168.169.0/24 scram-sha-256" >> ~/n3data/pg_hba.conf

Bring the node under Patroni. I used Patroni 4.1.3 with a dedicated single member etcd for the new node's Patroni pair, on ports 2389/2390. The important lines in the new node's config:

scope: n3
namespace: /n3test/
name: n3
restapi:
  listen: 0.0.0.0:8009
  connect_address: 192.168.169.158:8009
etcd3:
  hosts: 127.0.0.1:2389
postgresql:
  listen: 0.0.0.0:5442
  connect_address: 192.168.169.158:5442
  data_dir: /home/pgedge/n3data
  bin_dir: /usr/pgsql-18/bin
  authentication:
    superuser:   {username: pgedge, password: ...}
    replication: {username: replicator, password: ...}

Patroni adopted the running instance without complaint, and then quietly replaced its configuration with Patroni's own defaults:

INFO: Changed shared_preload_libraries from 'spock' to 'None' (restart might be required)
INFO: Changed track_commit_timestamp from 'on' to 'off' (restart might be required)
INFO: Changed wal_level from 'logical' to 'replica' (restart might be required)

Nothing breaks until the next restart, and then the node either refuses to start or comes up without Spock. Patroni only accepts settings like wal_level from its cluster config in etcd, so add every Spock requirement there before you restart anything:

patronictl -c ~/patroni/n3.yml edit-config --force \
  -p wal_level=logical -p track_commit_timestamp=on \
  -p shared_preload_libraries=spock -p max_worker_processes=16 \
  -p max_wal_senders=10 -p max_replication_slots=10 \
  -p max_active_replication_origins=10 \
  -p sync_replication_slots=on -p hot_standby_feedback=on
patronictl -c ~/patroni/n3.yml restart n3 n3 --force

After the restart all six subscriptions in the cluster were back to replicating. Because these settings live in etcd, the standby inherits them too.

Start the standby. A second Patroni member with an empty data directory creates the standby. Patroni finds the leader in etcd and runs pg_basebackup itself. It also writes the standby configuration, creates a physical slot for the standby on the leader, and starts it. Its config is the same with name: n3_standby, port 5452, REST port 8010 and its own data_dir.

nohup patroni ~/patroni/n3_standby.yml > ~/patroni/n3_standby.log 2>&1 &
INFO: replica has been created using basebackup
INFO: bootstrapped from leader 'n3'
| Member     | Host                 | Role    | State     | TL | Lag |
| n3         | 192.168.169.158:5442 | Leader  | running   |  1 |     |
| n3_standby | 192.168.169.158:5452 | Replica | streaming |  1 |   0 |

Confirm slot sync. On the standby, both Spock slots should show up as synced:

-- on n3_standby
SELECT slot_name, failover, synced, confirmed_flush_lsn FROM pg_replication_slots;

        slot_name        | failover | synced | confirmed_flush_lsn
-------------------------+----------+--------+---------------------
 spk_testdb_n3_sub_n3_n1 | t        | t      | 0/5011F40
 spk_testdb_n3_sub_n3_n2 | t        | t      | 0/5011F40

Patroni 4.1.3 put a dbname into primary_conninfo on its own, which slot sync needs on PostgreSQL 17 and later.

Hold Spock back until the standby has the WAL. Finally, set synchronized_standby_slots on the leader so the other Spock nodes never get ahead of the standby, and add an on_role_change callback to both Patroni members so the setting follows a promotion. Patroni passes action role scope, so the role is $2:

#!/bin/bash
ROLE="$2"; PORT=5442; OTHER=n3_standby
if [ "$ROLE" = "primary" ] || [ "$ROLE" = "master" ]; then
  psql -h /tmp -p $PORT -d postgres -c "ALTER SYSTEM SET synchronized_standby_slots = '$OTHER'" -c "SELECT pg_reload_conf()"
else
  psql -h /tmp -p $PORT -d postgres -c "ALTER SYSTEM RESET synchronized_standby_slots" -c "SELECT pg_reload_conf()"
fi

The callback only fires on a role change, so set the value by hand on the current leader the first time. Be aware of the tradeoff: if the standby goes down, the leader stops sending changes to the other Spock nodes until it comes back.

Step 5: Retire the Old Node and Repeat

Once the new node and its standby are healthy, move application traffic to the new node and remove the old one. Run zodremove.sql and remove_node on the node you are retiring:

psql -h 192.168.169.158 -p 5432 -d testdb -f zodremove.sql
psql -h 192.168.169.158 -p 5432 -d testdb -c \
"CALL spock.remove_node('n1', 'host=192.168.169.158 port=5432 dbname=testdb user=ahsan', true);"

It drops the node's subscriptions, its replication sets, and its entry in the cluster registry, and prints a summary of what it removed. Confirm the remaining nodes no longer list the node and that no slots for it are left behind:

SELECT node_name FROM spock.node;
SELECT slot_name FROM pg_replication_slots WHERE slot_name LIKE '%n1%';

Then go back to Step 1 for the next node. Keep at least two nodes active at every point, so the cluster always has somewhere to take writes. When the last old node is gone, turn DDL replication back on:

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

Two things to set up properly before you do this in production. Run the new node under systemd or Patroni so it comes back after a reboot. And point the other nodes' Spock DSN for each new node at a VIP or DNS name rather than the server's own address, so their subscriptions follow a Patroni failover. I have not run a failover test yet, so I will cover that in a follow up post.

Wrapping Up

The core idea holds up. I added a PostgreSQL 18 node to a live PostgreSQL 17 cluster, it accepted writes in both directions, and it used a Patroni managed standby built straight from the new version. The application never lost a writable node.

The catch is that none of the problems I hit showed up in a status check. Here is what to watch for:

  • With DDL replication off, the new node's tables are in no replication set until you add them.

  • The new node's Spock slots need failover = true before slot sync will touch them.

  • Patroni overwrites Spock's settings unless they are in its etcd config.

Handle those, test writes in both directions before you remove anything, and you can move a distributed cluster to a new major version without taking the application down.

The Zodan scripts live in the Spock repository under samples/Z0DAN.