Spock Multi-Master Replication for PostgreSQL
Table of Contents
Building the Spock Extension
Building the Spock Documentation
Basic Configuration and Usage
Upgrading a Spock Installation
Advanced Configuration Options
Spock Management Features
Modifying a Cluster
Monitoring your Cluster
Spock Functions
Using spockctrl Management Functions
Release Notes
Limitations
FAQ
Spock Multi-Master Replication for PostgreSQL – Prerequisites and Requirements
The Spock extension provides multi-master replication for PostgreSQL versions 15 and later. Take the following requirements into consideration as you design your cluster:
You will need to install the Spock extension on each node in your cluster. If you’re performing a major version upgrade, the old node can be running a recent version of pgLogical2 before upgrading it to become a Spock node.
On each node in your cluster, tables must have the same name and reside in the same schema. To check the table name and schema name of an existing table, you can connect to the database with psql and use the d meta-command:
SELECT schemaname, tablename FROM pg_tables ORDER BY schemaname, tablename;
For example:
lcdb=# d
List of relations
Schema | Name | Type | Owner
——–+—————-+———-+———-
public | table_a | table | ec2-user
public | table_a_id_seq | sequence | ec2-user
public | table_b | table | ec2-user
public | table_b_id_seq | sequence | ec2-user
public | table_c | table | ec2-user
public | table_c_id_seq | sequence | ec2-user
(6 rows)
Each table must also have the same columns and primary keys, with the same data types in each column. To review detailed information for all tables within a specific schema, connect to the database with psql and use the d schema_name.* command; for example:
lcdb=# d public.*
Table “public.table_a”
Column | Type | Collation | Nullable | Default
————+————————–+———–+———-+——————————
id | bigint | | not null | generated always as identity
name | text | | not null |
qty | integer | | not null |
created_at | timestamp with time zone | | not null | now()
Indexes:
“table_a_pkey” PRIMARY KEY, btree (id)
Sequence “public.table_a_id_seq”
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
——–+——-+———+———————+———–+———+——-
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Sequence for identity column: public.table_a.id
Index “public.table_a_pkey”
Column | Type | Key? | Definition
——–+——–+——+————
id | bigint | yes | id
primary key, btree, for table “public.table_a”
…
CHECK constraints and NOT NULL constraints must be the same or more permissive on any standby node that acts only as a subscriber.
For more information about the Spock extension’s advanced functionality, visit here.
Building the Spock Extension
You will need to build the Spock extension on a patched PostgreSQL source tree to which you have applied version-specific .diff files from the spock/patches/Postgres-version directory. The high-level steps to build Postgres and the spock extension are:
Get the Postgres source.
Copy the patch files to the base repository; the patches for each Postgres version are in a version-specific subdirectory of the spock repo. Then, apply each patch, use the command:
patch -p1
