Installing and Configuring the Spock Extension

The Spock extension can be installed with pgEdge Postgres packages or built from source. pgEdge Postgres deployments provide a simplified upgrade path that allows you to take advantage of important new features quickly and easily.

Spock is open-source, with code available for review at Github.

For detailed information about using Spock to create a two-node cluster, visit here.

Installing Spock with pgEdge Distributed or Enterprise Postgres

The latest Spock extension is automatically installed and created with any pgEdge Distributed or Enterprise Postgres installation. A pgEdge deployment provides quick and easy access to:

• the latest minor version of your preferred Postgres version.
• Spock functions and procedures.
• ACE consistency monitoring.
• Snowflake sequences.
• pgBackRest for simplified backup and restore capabilities.
• LOLOR large object support.
• management tools for your active-active distributed cluster.

Building the Extension from Source

Spock is available as an open-source project that you can build from source code distributed at the Github project page. The Spock extension must be built on a version-specific build of Postgres that is patched to provide 'hooks' so spock can access the Postgres engine.

To patch the Postgres source code, download the source code from the Postgres project, and move into the source directory. Then, clone the spock directory:

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

Then, apply the .diff files from the spock/patches/version directory that match your build version. To apply a patch, use the command:

patch -p1 < path_to_patch/patch_name

After applying version-specific patches, you can configure, make, and make install the Postgres server as described in the Postgres documentation. When the build completes, add the location of your pg_config file to your PATH variable:

export PATH=path_to_pg_config_file

Before invoking make and make install, install the jansson library to meet Spock prerequisites.

Then, move into the spock directory, and use a build process much like any other Postgres extension:

make

make-install

Configuring Postgres for Spock Replication

After installing Postgres on each node that will host a spock instance, use initdb to initialize a Postgres cluster. After initializing the cluster, you can connect with psql, and query the server for file locations and cluster details:

SHOW data_directory;
SHOW config_file;
SHOW hba_file;

Then, use your choice of editor to update the postgresql.conf file, adding required Postgres parameters to the end of the file:

wal_level = logical
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'spock'
track_commit_timestamp = on

For detailed information about advanced configuration options (GUCs), see Using Advanced Configuration Options.

After modifying the Postgres parameters, use your OS-specific command to restart the Postgres server.

Then, connect to the psql command line, and create the spock extension:

CREATE EXTENSION spock;

On each node that will host spock, modify the pg_hba.conf file and allow connections between n1 and n2. The following snippet is provided as an example only, and is not recommended for a production system as they will open your system for connection from any client:

host    all          all          <node_1_IP_address>/32    trust
host    all          all          <node_2_IP_address>/32    trust

host    replication  all          <node_1_IP_address>/32    trust
host    replication  all          <node_2_IP_address>/32    trust

Creating a Replication Scenario

After configuring the nodes, connect to the psql command line of the first provider node, and use the spock.node_create command to create the provider node:

    SELECT spock.node_create(
        node_name := 'provider1',
        dsn := 'host=providerhost port=5432 dbname=db'
    );

Then, add the tables in the public schema to the default replication set.

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

Once the provider node is configured, you can add subscribers. First create the subscriber node:

    SELECT spock.node_create(
        node_name := 'subscriber1',
        dsn := 'host=thishost port=5432 dbname=db'
    );

Then, create the subscription which will start synchronization and replication on the subscriber node:

    SELECT spock.sub_create(
        subscription_name := 'subscription1',
        provider_dsn := 'host=providerhost port=5432 dbname=db'
    );

    SELECT spock.sub_wait_for_sync('subscription1');