Installing the pgEdge Platform

In preparation for configuring a replication cluster, you should install the pgEdge platform on each node of your replication cluster. Ensure that curl and python 3.9 (or greater) are installed on each system and:

1. Visit the pgEdge website and complete the download form (opens in a new tab). The download form will redirect you to the download page.

2. On the download page, use the Copy button located to the right of the Installation field to copy the installation command.

3. Paste the command into your command line client and press Return.

4. Move into the pgedge directory, and install the pgEdge Platform with the command:

./nodectl install pgedge -U superuser_name -P superuser_password -d database_name

Where

superuser_name is the name of the PostgreSQL database superuser; pgEdge Platform will create this user after installing PostgreSQL. The database superuser cannot be the name of an OS superuser, pgEdge, or any of the PostgreSQL reserved words (opens in a new tab).

superuser_password is the password that will be associated with the superuser name.

database_name is the name of the database that will be created by the pgEdge Platform.

After installing pgEdge, follow the steps below, or to review a tutorial configuration, see the Getting Started Guide (opens in a new tab).

During the installation, pgEdge creates a database replication user with the same name as the OS user that invokes the installation command, and adds that username and the jumbled password to the ./pgpass file. Use the replication role when performing the rest of the steps that follow.

Configuring a Cluster

When you install the pgEdge platform, you create the artifacts you need to create and register publishers and subscribers in your replication cluster. To configure replication, you need to:

1. Create each node (opens in a new tab) in the replication cluster.
2. Add a replication set (opens in a new tab) to each node.
3. Create subscriptions that link the node (opens in a new tab) to the other nodes in the cluster.
4. Add the tables that will be replicated (opens in a new tab) to the replication set.
5. Add the replication sets to the subscriptions (opens in a new tab).

After performing these steps on each node of the cluster, you can confirm replication by adding a row to a table on the provider node; the row will be replicated to all of the subscriber nodes.

Configuration Steps

After downloading and installing the pgEdge platform on each node of your cluster, you're ready to start the process of configuring a cluster.

Creating a node

After installing the pgEdge platform, move into the pgedge directory on each node of your cluster, and use the spock node-create (opens in a new tab) command to create the node, providing a name and connection information for the node. The signature is:

./nodectl spock node-create node_name 'connection_string' database_name

Where

node_name is a unique name for the node.

connection_string specifies a set of connection properties that other nodes will use to subscribe to the node.

database_name is the name of the PostgreSQL database that contains the tables from which you're replicating; the database name must be provided in the connection string, and again at the end of the command.

For example, the following command creates a node named n1, that resides on a host with the IP address of 10.1.2.5. The user that will authenticate to connect (in our example, admin) is the replication user created during the pgEdge installation; this user has the same name as your OS user. Our database is named accts:

./nodectl spock node-create n1 'host=10.1.2.5 user=admin dbname=accts' accts

You need to execute the spock node-create command on each node of the replication cluster, specifying a unique node name, a connection string that allows connections from each subscriber to that node, followed by the name of the PostgreSQL database that holds the tables that the cluster replicates. A second occurrence of the database name follows the command.

To review a list of nodes, you can use the ./nodectl spock node-list database_name command.

Adding a replication set

Next, invoke the spock repset-create (opens in a new tab) command on each node of a replication cluster, identifying a named set of tables that will be replicated, that reside in the specified database. The command signature is:

./nodectl spock repset-create repset_name database_name

Where

repset_name is the name of the replication set.

database_name is the name of the PostgreSQL database you're replicating.

The following example adds a node to a replication set named accts_replication_set; the name of the database (accts) follows:

./nodectl spock repset-create accts_replication_set accts

Invoke this command on each node of the cluster. For a two or three node cluster with replication between all nodes, you can create the same replication set on each node and add that replication set to every subscription. You can use the ./nc spock node-list database_name command to review a list of nodes.

Creating subscriptions between nodes

Next, use the spock sub-create (opens in a new tab) command to create subscriptions between the nodes. Invoke the command on each subscription node, specifying the connection string of the publisher. The signature is:

./nodectl spock sub-create subscription_name 'connection_string' database_name

Where

subscription_name is the unique name of the subscription; this name must be unique for the scope of the replication cluster.

connection_string specifies how to connect to the publication node. Include the host address, the port number on which the node will connect, the name used to authenticate the connection, and the database name.

database_name is the name of the PostgreSQL database you're replicating; the database name must be provided in the connection string, and again at the end of the command.

Invoke this command between each cluster pair to create the subscriptions - for example:

• If you have two nodes in your cluster, you will invoke two commands that start uniquely-named subscriptions; one command:

  • subscribes the first node to the second node.
  • subscribes the second node to the first.

• If you have three nodes in your cluster, you will invoke six commands; one command:

  • subscribes the first node to the second node.
  • subscribes the first node to the third node.
  • subscribes the second node to the first node.
  • subscribes the second node to the third node.
  • subscribes the third node to the first node.
  • subscribes the third node to the second node.

For example, if you have a three-node cluster, you'll create two subscriptions on the first node that specify connection information for the two other nodes. The first command creates a subscription to the second node; it connects to port 5432 on 10.2.2.5, using the credentials of the replication user (in our example, admin). admin is created during the pgEdge installation; this user has the same name as your OS user. The subscription (named sub_n1n2) is for the accts database. The second command creates a subscription to the third node; it connects to to port 5432 on 10.27.1.5, again, using the credentials of the admin user. The subscription is named sub_n1n3:

./nodectl spock sub-create sub_n1n2 'host=10.2.2.5 port=5432 user=admin dbname=accts' accts ./nodectl spock sub-create sub_n1n3 'host=10.27.1.5 port=5432 user=admin dbname=accts' accts

On the second node of the cluster, you'll create subscriptions to the other two nodes. The first command creates a subscription that connects to port 5432 on 10.1.2.5 (the first node), using the credentials of the admin user. The subscription (named sub_n2n1) is for the accts database. The second command creates a subscription to the first node; it connects to to port 5432 on 10.27.1.5, using the credentials of the admin user. The subscription is named sub_n2n3:

./nodectl spock sub-create sub_n2n1 'host=10.1.2.5 port=5432 user=admin dbname=accts' accts ./nodectl spock sub-create sub_n2n3 'host=10.27.1.5 port=5432 user=admin dbname=accts' accts

On the third node of the cluster, you'll create subscriptions to the other two nodes. The first command creates a subscription that connects to port 5432 on 10.1.2.5 (the first node), using the credentials of the admin user. The subscription (named sub_n3n1) is for the accts database. The first command creates a subscription to the second node; it connects to port 5432 on 10.2.2.5, using the credentials of the admin user. The subscription (named sub_n3n2) is for the accts database.

./nodectl spock sub-create sub_n2n1 'host=10.1.2.5 port=5432 user=admin dbname=accts' accts ./nodectl spock sub-create sub_n3n2 'host=10.2.2.5 port=5432 user=admin dbname=accts' accts

To check the status of a subscription, you can use the ./nc spock sub-show-status subscription_name database_name command.

Adding tables to a replication set

For each replication set, use the nodectl spock repset-add-table (opens in a new tab) command to add the tables that will be replicated to the replication set. The signature is:

./nodectl spock repset-add-table repset_name 'table_name' database_name

Where

repset_name is the name of the replication set.

table_name is the (optionally schema-qualified) name of the table that you are adding to the replication set.

database_name is the name of the PostgreSQL database you're replicating.

You can use wildcards to specify more than one table that matches a selection criteria. For example, to add all of the tables whose names are prefixed with the words retail_ to the accts_replication_set, in the accts database, invoke the following command on each node:

./nodectl spock repset-add-table accts_replication_set 'retail_*' accts

Use the ./nc spock repset-list-tables database_name to review a list of tables that are members of a replication set.

Adding replication sets to a subscription

Last, on each node of the replication cluster, use the nodectl spock sub-add-repset (opens in a new tab) command to add the replication set to each subscription. The signature is:

./nodectl spock sub-add-repset subscription_name repset_name database_name

Where

subscription_name is the name of the subscription created in the second step.

repset_name is the name of the replication set.

database_name is the name of the PostgreSQL database in which you're replicating.

Each replication set must be added to the subscription; for example, if your cluster has three nodes, you should use the command to add the replication set name to each subscription name you created earlier:

./nodectl spock sub-add-repset sub_n1n2 accts_replication_set accts ./nodectl spock sub-add-repset sub_n1n3 accts_replication_set accts ./nodectl spock sub-add-repset sub_n2n1 accts_replication_set accts ./nodectl spock sub-add-repset sub_n2n3 accts_replication_set accts ./nodectl spock sub-add-repset sub_n3n1 accts_replication_set accts ./nodectl spock sub-add-repset sub_n3n2 accts_replication_set accts

To see a list of the replication sets that belong to a subscription, use the following command:

./nodectl spock sub-show-status subscription_name database_name

After adding the replication set(s) to the subscription(s), replication is configured; any transactions performed on the provider node will be replicated on the subscriber nodes of the replication cluster.

To review a tutorial walk-through that creates a two-node cluster and configures replication between the nodes, review the pgEdge Getting Started Guide (opens in a new tab).