pgEdge/spock: Logical multi-master PostgreSQL replication

Logical multi-master PostgreSQL replication

Summary

pgEdge/spock is a C-based PostgreSQL extension that provides logical multi-master replication across PostgreSQL 15 through 19. It enables writeable replicas that each accept changes and propagate them to peers, with conflict handling and optional automatic DDL replication.

What it is useful for

Spock addresses scenarios where multiple PostgreSQL nodes need to accept writes concurrently and converge, such as multi-region deployments, write-scalability experiments, or version migrations where existing pgLogical2 nodes can be upgraded in place to Spock.

Beyond basic row replication, the extension includes automatic DDL replication, batch inserts, large object replication (Lolor), partitioned table support, Snowflake-style distributed sequences, replication set filtering, and lag tracking. Conflict resolution relies on track_commit_timestamp, which the build instructions require. A management tool called Zodan is included for adding or removing nodes with minimal or zero downtime, and pgBackRest integration supports lower-downtime node additions.

How engineers can use it

Building Spock requires a patched PostgreSQL source tree. The README outlines the steps:

1. Fetch PostgreSQL source and apply version-specific .diff files from spock/patches/Postgres-version/ in numerical order via patch -p1.
2. configure, make, and make install the patched PostgreSQL, then export the directory containing pg_config to your PATH.
3. Clone https://github.com/pgEdge/spock.git and run make followed by make install.
4. Update postgresql.conf with shared_preload_libraries = 'spock', track_commit_timestamp = on, and output_plugin_libraries = 'pgoutput, test_decoding, spock_output'. The last is required on PostgreSQL 15.19, 16.15, 17.11, 18.6, and 19 so the spock_output plugin can load.
5. Run CREATE EXTENSION spock; on each node.

To wire up a two-node cluster, call SELECT spock.node_create(node_name := 'n1', dsn := 'host=10.0.0.5 port=5432 dbname=acctg') on each node, then create subscriptions in both directions with SELECT spock.sub_create(...). With spock.enable_ddl_replication=on and spock.include_ddl_repset=on, tables created on one node appear on the other; the README demonstrates this by initializing pgbench tables on n1 and listing them on n2.

For production-style deployments, the README points to separate repositories: pgEdge/pgedge-ansible for Ansible playbooks and the docs.pgedge.com/container site for container images.

Documented limitations: tables must share identical names, schemas, columns, and primary keys across nodes; CHECK and NOT NULL constraints must match or be more permissive on subscriber-only standbys; and upgrades cannot be rolled back due to catalog changes.