Upgrading the Spock Extension

If you're upgrading a secure production environment, shut down each node sequentially and perform the following steps:

1. Disable auto-DDL.
2. Stop the Postgres server.
3. Build the updated Spock binaries for your Postgres version.
4. Restart the Postgres server; this triggers extension schema upgrade checks (Spock's background worker detects the version mismatch and runs any required schema migrations automatically).
5. Use the psql command line to verify the Spock version in use and the node status.
6. After upgrading all of the nodes in your cluster, enable Auto-DDL and restart the service on each node.

A detailed description of each step in the upgrade process is provided below.

Disabling Auto-DDL

Before stopping a node, disable DDL replication to prevent schema changes during the upgrade. Connect to each node and run:

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

Or, set the following parameter in postgresql.conf on each node:

spock.enable_ddl_replication = off

Then, reload the configuration:

SELECT pg_reload_conf();

Stopping the Postgres Server

Before upgrading the Spock binary, stop the Postmaster on the server that you're working on:

pg_ctl -D /path/to/data1 stop -m fast

Building the New Spock Binaries

Before building the extension, you'll need to obtain updated Spock source code. If you regularly build the Spock extension from source code (available at https://github.com/pgEdge/spock), use git to obtain an updated source set; then:

cd /path/to/spock-5.0.1
make clean
make
sudo make install

The installation copies the following files:

• spock.so → $(pg_config --pkglibdir)/;
• spock.control → $(pg_config --sharedir)/extension/;
• spock--5.0.1.sql and spock--5.0.0--5.0.1.sql → $(pg_config --sharedir)/extension/.

Next, you should verify that the updated Spock files are installed. You can use the following commands to confirm the version update.

Check the library timestamp:

ls -l $(pg_config --pkglibdir)/spock.so

Check the control file:

ls -l $(pg_config --sharedir)/extension/spock.control

Check that an upgraded .sql file exists; for example:

ls -l $(pg_config --sharedir)/extension/spock--5.0.0--5.0.1.sql

Restarting the Postgres Server

Then, use pg_ctl to restart the Postgres postmaster process:

pg_ctl -D /path/to/data1 restart

When the server restarts, Spock's database manager background worker automatically detects the version mismatch and upgrades the extension schema (with spock_manage_extension() in spock.c).

Verifying the Extension Upgrade

After building the extension, you can verify the version of the extension that is in use and the state of your replication cluster. Use the psql command line to invoke the commands that follow.

Connect to the database with psql:

psql -d database_name -p port_number

Verify the Spock version that is in use:

SELECT extname, extversion FROM pg_extension WHERE extname = 'spock';

Then, verify that the node is replicating. Check that subscriptions are active with the following commands:

SELECT sub_name, sub_enabled, sub_slot_name, sub_replication_sets
FROM spock.subscription;

Check the replication slots:

SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

Check the node status:

SELECT * FROM spock.node;

Enabling Auto-DDL and Restarting the Service on Each Node

After upgrading all of the nodes in your cluster, connect to each node with psql and enable DDL replication:

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

Or you can modify the parameter in the postgresql.conf file:

spock.enable_ddl_replication = on

Reload the configuration:

SELECT pg_reload_conf();

Then, on each node, restart the service:

pg_ctl -D /path/to/data1 restart

Final Verification

After performing the steps listed above on each server in the cluster, use the following command to verify the replication status of each node:

SELECT application_name, state, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

You should confirm that:

• all nodes report consistent versions (the target version).
• replication lag is minimal or zero.
• no errors appear in PostgreSQL or Spock logs.