Creating a Spock Cluster: End-to-End Installation Guide

This guide walks you through installing Spock and creating a two-node active-active replication cluster.

Before you begin, ensure you have the following prerequisites:

• Two nodes (physical or virtual machines) with network connectivity.
• Administrative access to both nodes.
• Basic familiarity with PostgreSQL and command-line operations.

Overview

The installation process follows these steps.

1. Adding the pgEdge repository and installing pgEdge Postgres with Spock.
2. Configuring PostgreSQL parameters for logical replication.
3. Creating the Spock extension on both nodes.
4. Setting up node definitions and bidirectional subscriptions.
5. Enabling automatic DDL replication across the cluster.
6. Testing the cluster to verify bidirectional replication works correctly.

Install pgEdge Enterprise Postgres (Recommended Path)

This section describes how to install pgEdge Enterprise Postgres with Spock on each node using the pgEdge repository.

On each node, add the pgEdge repository to your system.

For RHEL/Rocky Linux/AlmaLinux:

sudo yum install -y https://pgedge-download.s3.amazonaws.com/REPO/pgedge-repo-1.0-1.noarch.rpm

For Ubuntu/Debian:

curl -fsSL https://pgedge-download.s3.amazonaws.com/REPO/pgedge-repo.gpg | sudo gpg --dearmor -o /usr/share/keyrings/pgedge-archive-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/pgedge-archive-keyring.gpg] https://pgedge-download.s3.amazonaws.com/REPO/apt $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/pgedge.list
sudo apt update

After creating the repository, install your preferred PostgreSQL version. The examples use PostgreSQL 18, but you can substitute 15, 16, or 17.

For RHEL/Rocky Linux/AlmaLinux:

sudo yum install -y pgedge-postgres18-server pgedge-postgres18-contrib pgedge-spock18

For Ubuntu/Debian:

sudo apt install -y pgedge-postgres18-server pgedge-postgres18-contrib pgedge-spock18

On each node, initialize the PostgreSQL database cluster.

For RHEL/Rocky Linux/AlmaLinux:

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable postgresql-18
sudo systemctl start postgresql-18

For Ubuntu/Debian:

sudo /usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/data
sudo systemctl enable postgresql-18
sudo systemctl start postgresql-18

In the following example, the psql command connects to PostgreSQL to verify the installation:

sudo -u postgres psql -U postgres -p 5432

Once connected, confirm that you can see the configuration file locations.

In the following example, the SHOW commands display the PostgreSQL configuration file locations:

postgres=# SHOW data_directory;
     data_directory
------------------------
 /var/lib/pgsql/18/data
(1 row)

postgres=# SHOW config_file;
              config_file
----------------------------------------
 /var/lib/pgsql/18/data/postgresql.conf
(1 row)

postgres=# SHOW hba_file;
              hba_file
------------------------------------
 /var/lib/pgsql/18/data/pg_hba.conf
(1 row)

The pgEdge Enterprise Postgres installation automatically includes the following components:

• The latest minor version of your chosen Postgres version.
• The Spock extension (pre-installed, ready to enable).
• Spock functions and procedures.
• ACE consistency monitoring.
• Snowflake sequences.
• pgBackRest for simplified backup and restore.
• LOLOR large object support.
• Management tools for active-active distributed clusters.

Install from Source (Alternative Path)

If you need to build Spock from source instead of using pgEdge Enterprise Postgres, follow these steps.

1. Download PostgreSQL source code from the Postgres project and navigate to the source directory.

2. Clone the Spock repository with the following command:

git clone https://github.com/pgEdge/spock.git

1. Apply the version-specific patches with the following command:

patch -p1 < spock/patches/version/patch_name

1. Configure, build, and install PostgreSQL as described in the Postgres documentation.

2. Add the pg_config file location to your PATH with this command:

export PATH=path_to_pg_config_file

1. Install the jansson library (required for Spock).

2. Build and install Spock with the following commands:

cd spock
make
make install

Repeat the installation process on both nodes.

After building from source, use initdb to initialize a PostgreSQL cluster on each node.

Configure PostgreSQL for Spock Replication

On each node, edit the postgresql.conf file and add the required parameters to the end of the file.

In the following example, the configuration parameters enable logical replication and load the Spock extension:

 wal_level = logical
max_worker_processes = 10   # one per database on provider, one per node on subscriber
max_replication_slots = 10  # one per node on provider
max_wal_senders = 10        # one per node on provider
shared_preload_libraries = 'spock'
track_commit_timestamp = on # needed for conflict resolution
listen_addresses = '*'

After modifying postgresql.conf, restart PostgreSQL using your OS-specific command.

In the following example, the systemctl commands restart and verify the PostgreSQL service:

sudo systemctl restart postgresql-18
sudo systemctl status postgresql-18

Verify the service is active and running.

On each node, modify the pg_hba.conf file to allow connections between the nodes.

Add entries for both regular and replication connections. Replace <node_1_IP_address> and <node_2_IP_address> with your actual IP addresses.

In the following example, the configuration entries allow connections from both nodes:

# Regular connections
host    all          all          <node_1_IP_address>/32    trust
host    all          all          <node_2_IP_address>/32    trust
host    all          all          <subnet_address>/24     trust

# Replication connections
host    replication  all          <node_1_IP_address>/32    trust
host    replication  all          <node_2_IP_address>/32    trust

After modifying pg_hba.conf, restart PostgreSQL again to apply the changes.

Create the Spock Extension

On each node, connect to PostgreSQL and create the Spock extension.

In the following example, the CREATE EXTENSION command creates the Spock extension:

sudo -u postgres psql -U postgres -p 5432

postgres=# CREATE EXTENSION spock;
CREATE EXTENSION
postgres=#

Create Nodes and Configure Replication

Next, you will set up bidirectional replication between your two nodes. For this guide, the nodes are named n1 and n2.

On Node 1 (n1)

Follow these steps to create the node definition and add tables to the replication set.

1. Create the node definition with the following command:

SELECT spock.node_create(
    node_name := 'n1',
    dsn := 'host=<n1_ip_address> port=<n1_port> dbname=<db_name>'
);

1. Add tables to the default replication set with this command:

SELECT spock.repset_add_all_tables('default', ARRAY['public']);

If you are working in a schema other than public, adjust the schema name accordingly.

On Node 2 (n2)

Follow these steps to create the node definition, add tables, and create the subscription.

1. Create the node definition with the following command:

SELECT spock.node_create(
    node_name := 'n2',
    dsn := 'host=<n2_ip_address> port=<n2_port> dbname=<db_name>'
);

1. Add tables to the default replication set with this command:

SELECT spock.repset_add_all_tables('default', ARRAY['public']);

1. Create the subscription from n2 to n1 with these commands:

SELECT spock.sub_create(
    subscription_name := 'sub_n2_n1',
    provider_dsn := 'host=<n1_ip_address> port=<n1_port> dbname=<db_name>'
);

SELECT spock.sub_wait_for_sync('sub_n2_n1');

On Node 1 (n1)

Create the reverse subscription from n1 to n2 with the following commands:

SELECT spock.sub_create(
    subscription_name := 'sub_n1_n2',
    provider_dsn := 'host=<n2_ip_address> port=<n2_port> dbname=<db_name>'
);

SELECT spock.sub_wait_for_sync('sub_n1_n2');

On Both Nodes (n1 and n2)

To ensure DDL statements are automatically replicated across your cluster, run these commands on both nodes.

In the following example, the ALTER SYSTEM commands enable automatic DDL replication:

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

These settings enable the following features:

• Automatic replication of DDL statements through the default replication set.
• Automatic addition of new tables to replication sets; tables with primary keys are added to the default set, and tables without primary keys are added to the default_insert_only set.

Verify the Cluster

On each node, use the following command to verify that nodes are properly configured.

In the following example, the SELECT command displays the configured nodes:

SELECT * FROM spock.node;

Expected output:

postgres=# SELECT * FROM spock.node;
-[ RECORD 1 ]----
node_id   | 26863
node_name | n2
location  |
country   |
info      |
-[ RECORD 2 ]----
node_id   | 49708
node_name | n1
location  |
country   |
info      |

Then, use the following command to verify that subscriptions are active and replicating.

In the following example, the SELECT command displays the subscription status:

SELECT * FROM spock.sub_show_status();

Expected output:

 postgres=# SELECT * FROM spock.sub_show_status();
-[ RECORD 1 ]-----+--------------------------------------------------------------------
subscription_name | sub_n1
status            | replicating
provider_node     | n2
provider_dsn      | host=192.168.105.11 dbname=postgres user=postgres password=password
slot_name         | spk_postgres_n2_sub_n1
replication_sets  | {default,default_insert_only,ddl_sql}
forward_origins   |

The status field should display replicating.

Test Replication

Perform a simple replication test using the following steps.

1. On n1, create a test table with the following commands:

CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    message TEXT
);

INSERT INTO test (message) VALUES ('Hello from n1');

1. On n2, verify the table exists and contains the data with this query:

SELECT * FROM test;

Expected output:

postgres=# SELECT * FROM test;
-[ RECORD 1 ]------
id  | 1
val | Hello from n1

1. On n2, insert a new row with the following command:

INSERT INTO test (message) VALUES ('Hello from n2');

1. On n1, verify both rows are present with this query:

SELECT * FROM test;

Expected output:

postgres=# SELECT * FROM test;
-[ RECORD 1 ]------
id  | 1
val | Hello from n1
-[ RECORD 2 ]------
id  | 2
val | Hello from n2

You should see both messages on both nodes, confirming bidirectional replication is working.

Next Steps

Your two-node Spock cluster is now operational.

• The Configuring Spock document describes conflict resolution settings and advanced configuration options.
• The Spock Functions Reference document provides detailed information about monitoring functions and replication management.
• The Managing DDL Replication document explains how to control automatic DDL replication behavior.

Troubleshooting

If you encounter issues, review the following common problems and solutions.

1. Replication not starting: Check pg_hba.conf entries and ensure nodes can connect to each other.

2. Subscription stuck in initializing: Verify that max_replication_slots and max_wal_senders are sufficient.

3. DDL not replicating: Confirm automatic DDL replication settings are enabled on both nodes.

4. Check logs: Review PostgreSQL logs for detailed error messages.

For more information, see the following resources:

• The Spock Functions Reference document provides detailed function documentation.
• The Advanced Configuration Options document describes additional configuration parameters.
• The Managing DDL Replication document explains DDL replication features.