Upgrade PostgreSQL in a Spock Cluster Without Losing Writes

A node can be taken offline during a major PostgreSQL upgrade without the distributed cluster stopping writes: the other nodes continue serving the application. The requirement is that the topology has enough nodes to keep two active while the third is being worked on. In pgEdge's test, the upgrade was from PostgreSQL 17.10 to 18.6 on a Spock node managed by Patroni.

This procedure does not keep the node being upgraded available. It keeps other write endpoints accessible while that node is stopped. The example follows the pgEdge lab: three Spock nodes, with n3 as the target, and a physical standby associated with n3. The other two nodes continue serving during the operation.

The core sequence is to drain n3, upgrade it with pg_upgrade, repair Spock replication state, return it to Patroni, and rebuild its standby. Upgrade one node at a time.

flowchart TD
A["Drenar n3"] --> B["Parar standby y nodo"]
B --> C["Preparar PostgreSQL 18"]
C --> D["Ejecutar pg_upgrade"]
D --> E["Restaurar orígenes Spock"]
E --> F["Reintegrar en Patroni"]
F --> G["Reconstruir standby"]
G --> H["Probar escrituras y seguir"]

Before stopping the node

Check the version, encoding, and Spock version on the node you are going to upgrade. Also note the checksum status: the new directory must match the old cluster. In PostgreSQL 18, initdb enables checksums by default; if the old cluster does not use them, you must explicitly disable them.

-- Ejecutar en n3 antes de detenerlo
SELECT version();
SHOW server_encoding;
SELECT extversion FROM pg_extension WHERE extname = 'spock';

# Comprobar checksums en el directorio de datos antiguo
/usr/pgsql-17/bin/pg_controldata ~/n3data | grep 'checksum version'

Install the new binaries and the same Spock version used by the cluster. The pgEdge example installs Spock 5.0.13 for PostgreSQL 18. Before starting the rollout, disable DDL replication on all nodes and reload the configuration. Keep it disabled until all nodes have been upgraded.

# Ejemplo del paquete usado en la prueba con Spock 5.0.13
sudo dnf install -y pgedge-postgresql18-server pgedge-postgresql18-contrib pgedge-spock50_18-5.0.13

-- Ejecutar en cada nodo antes del despliegue
ALTER SYSTEM SET spock.enable_ddl_replication = off;
SELECT pg_reload_conf();

Stop n3 and prepare the new directory

Remove n3 from the load balancer or pooler. Do not proceed until you have verified that incoming and outgoing subscriptions are up to date. Then pause Patroni and stop the standby first, followed by the node. pgEdge warns that when PostgreSQL is stopped, Patroni releases the leader lock even in maintenance mode: leaving the old standby active creates the risk that it will be promoted if Patroni is resumed too soon.

-- Confirmar el estado de las suscripciones antes de parar n3
SELECT subscription_name, status FROM spock.sub_show_status();

# Drenar n3 antes de ejecutar estos comandos
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

The remaining nodes must continue serving traffic. Initialize the PostgreSQL 18 directory with the same encoding and checksum settings as the old one. In this example, the cluster used UTF-8 and had no checksums. Copy the authentication file and configure Spock before running pg_upgrade: the extension must be in shared_preload_libraries so the check can load it.

# Inicializar el nuevo directorio con los ajustes de la prueba
/usr/pgsql-18/bin/initdb -D ~/n3data_new --encoding=UTF8 --no-data-checksums

# Configurar PostgreSQL y Spock antes de pg_upgrade
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

The parameters must match the old node's configuration. In particular, pgEdge notes that max_active_replication_origins is new in PostgreSQL 18 and that output_plugin_libraries is available in recent pgEdge builds, such as 18.6.

Run pg_upgrade and restore the origins

First run --check from a directory where the user has write permission: pg_upgrade writes its logs there. Include --link in the check to validate that mode. If it passes, repeat the command without --check. Link mode reuses data files via hard links; it requires the data directories to be on the same file system and, once the new cluster has started, the old one cannot be used again.

# Comprobar compatibilidad y modo link desde un directorio escribible
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 \
  --link --check

# Ejecutar la actualización tras superar la comprobación
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 \
  --link

In the test, PostgreSQL 17 retained the logical slots, but that was not enough for n3 to apply incoming changes. pg_upgrade does not preserve Spock origins: the other nodes could read from n3, but n3 did not apply what it received. Retrieve the confirmed position of each slot on the provider and recreate each origin on the upgraded node, advancing it to that LSN. The names in the following example correspond to the pgEdge topology; adjust them to match your cluster's actual slots.

# Leer en cada proveedor el último LSN confirmado por el slot
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';")

# Crear y avanzar los orígenes correspondientes en 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');"

Check that subscriptions are replicating again and perform test writes in both directions before moving on to the next node. In the case tested by pgEdge, a row written while n3 was still not applying changes appeared there after the origins were restored. Do not consider recovery successful based only on subscription status.

Return the node to Patroni and rebuild the standby

Start the new cluster and update the Patroni configuration to point to the PostgreSQL 18 directory and binaries. A simple patronictl reload was not sufficient in the test: Patroni continued looking at the old directory until its process was restarted.

# Arrancar n3 con PostgreSQL 18 antes de devolverlo a Patroni
/usr/pgsql-18/bin/pg_ctl -D ~/n3data_new -l ~/n3data_new/n3.log start

The system identifier changes after pg_upgrade, but etcd retains the old one. According to the tested procedure, you must stop Patroni, remove the cluster registration, and let Patroni adopt the running node. The Spock configuration must also be in bootstrap.dcs; otherwise, Patroni may replace parameters such as wal_level and shared_preload_libraries with its managed values.

# Fragmento de la configuración de Patroni
postgresql:
  data_dir: /home/pgedge/n3data_new
  bin_dir: /usr/pgsql-18/bin

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

# Detener Patroni, retirar el registro antiguo y arrancarlo de nuevo
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 &

The physical standby cannot follow the primary across a major upgrade. In the test, Patroni rejected reinitializing the old standby because of the system identifier mismatch. The procedure used was to delete its directory and restart Patroni, which created a new replica using pg_basebackup.

# Eliminar los datos antiguos del standby y dejar que Patroni lo reconstruya
rm -rf ~/n3standby
nohup patroni ~/patroni/n3_standby.yml > ~/patroni/n3_standby.log 2>&1 &

Afterward, restore the settings applied with ALTER SYSTEM: pg_upgrade does not migrate postgresql.auto.conf. In the pgEdge scenario, synchronized_standby_slots was configured again on the leader, and the standby was checked to ensure it had current copies of the Spock slots.

-- Ejecutar en el líder actualizado cuando el standby esté reconstruido
ALTER SYSTEM SET synchronized_standby_slots = 'n3_standby';
SELECT pg_reload_conf();

Finish the tasks left pending by pg_upgrade: update extensions, regenerate statistics, and retain the old directory until you are satisfied with the result. Only then remove the old data. Repeat the process node by node; when the last one is complete, re-enable DDL replication.

# Ejecutar en el nodo actualizado; borrar el clúster antiguo al final
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

Order matters: restore incoming replication before considering the node reintegrated, verify writes in both directions, and do not upgrade the next member until the previous one is operational. In the example, upgrading one node left the other two available throughout the work.

Sources: pgedge.com, PostgreSQL.de, PostgreSQL Documentation, GitHub