PostgreSQL Logical Replication

This tutorial introduces how to implement PostgreSQL Logical Replication in a bi-directional architecture using ClusterControl. It is particularly suited for environments that require multi-region write availability, zero-downtime upgrades, or active-active application architectures.

Introduction

PostgreSQL supports two replication paradigms:

Streaming Replication — built-in, synchronous or asynchronous binary-level replication from primary to read-only standbys.
Logical Replication — table-level, row-based, asynchronous replication using a publish/subscribe model.

Logical replication is ideal when:

You want granular control (replicating specific tables or schemas).
You need to replicate between different PostgreSQL versions.
You require multi-primary setups, like bi-directional replication.

Warning

Logical replication does not replicate DDL changes or automatically resolve conflicts.

Architecture

The bi-directional setup supported by ClusterControl consists of two PostgreSQL streaming replication clusters, each configured with:

One primary node
One or more read-only replicas
Logical replication enabled between the two primary nodes

Below is a high-level architecture diagram of the final architecture:

architecture-beta
    group topology(database)[Basic Topology]

    group clusterA [Cluster A] in topology
    service db1(database)[Primary] in clusterA
    service db2(database)[Readonly Replica] in clusterA

    group clusterB [Cluster B] in topology
    service db3(database)[Primary] in clusterB
    service db4(database)[Readonly Replica] in clusterB

    db1:L <-[Logical Replication]-> R:db3
    db2:T <-[Streaming Replication]- B:db1
    db4:T <-[Streaming Replication]- B:db3

Prerequisites

Ensure you have:

ClusterControl version 2.3.1 or higher. If not, follow the instructions in Quickstart or use the Installer Script.
The number of nodes that you want to include in the clusters with a supported OS.
Network connectivity between all the database nodes and ClusterControl.
SSH access to all the nodes.

Step 1: Set up SSH key-based authentication

On the ClusterControl server, generate a new SSH key as root user:
```
ssh-keygen -t rsa
```

Copy the SSH public key to all target nodes (specify the password if prompted):

ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db1
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db2
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db3
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db4

If the target node only requires a key-based authentication, you can use the -o flag to customize the SSH option:

ssh-copy-id -i /root/.ssh/id_rsa -p 22 -o 'IdentityFile /root/myprivatekey.pem' [email protected]  # db1
ssh-copy-id -i /root/.ssh/id_rsa -p 22 -o 'IdentityFile /root/myprivatekey.pem' [email protected]  # db2
ssh-copy-id -i /root/.ssh/id_rsa -p 22 -o 'IdentityFile /root/myprivatekey.pem' [email protected]  # db3
ssh-copy-id -i /root/.ssh/id_rsa -p 22 -o 'IdentityFile /root/myprivatekey.pem' [email protected]  # db4

Perform some tests and make sure you can execute the following commands from the ClusterControl server without any password prompt:

ssh [email protected] "ls /root"
ssh [email protected] "ls /root"
ssh [email protected] "ls /root"
ssh [email protected] "ls /root"

Step 2: Deploy a new cluster

Open your browser and navigate to your ClusterControl server’s IP or domain name.
From the ClusterControl dashboard, click on Deploy a cluster in the top-right corner and choose Create a database cluster.
Choose "PostgreSQL Logical" from the Database dropdown and the appropriate Vendor and Version (e.g., "PostgreSQL" and "17") from the wizard. Click Continue.
In the Deploy PostgreSQL Logical cluster wizard, configure the cluster as follows:
(1) Cluster details(2) SSH configuration(3) Node configuration(4) Add nodes(5) Extensions(6) Preview
Name: Enter a name for your cluster (e.g., My First PostgreSQL Cluster).

Tags: Enter one or more tags, separated by pressing "Enter" (e.g., production, replication, dc1).
SSH user: root

SSH user key path: /root/.ssh/id_rsa

SSH port: 22

SSH sudo password: (leave blank if using key-based auth)

Install software: On (default)

Disable firewall: Checked (default)

Disable SELinux/AppArmor: Checked (default)
Server data directory: /var/lib/pgsql (default varies by distro)

Server port: 5432 (default)

User: postgres

Password: Enter a secure password for the PostgreSQL postgres superuser

Repository: Use vendor repositories (default)

Enable SSL encryption: On (default)
Cluster 1: It means the first PostgreSQL Streaming Replication cluster.

Primary node:

Specify "192.168.99.101" as the primary node for Cluster 1 and press Enter.

Replica node:

Specify "192.168.99.102" as replica and press Enter.

You can add more replica nodes here.

Turn "On" Synchronous Replication if required.

Wait until everything is green. If a red warning appears, inspect the error and fix it.

Cluster 2: It means the second PostgreSQL Streaming Replication cluster.

Primary node:

Specify "192.168.99.103" as the primary node for Cluster 2 and press Enter.

Replica node:

Specify "192.168.99.104" as replica and press Enter.

You can add more replica nodes here.

Turn "On" Synchronous Replication if required.

Wait until everything is green. If a red warning appears, inspect the error and fix it.

Proceed only if all nodes are reachable (shown in green).
Leave the toggle button "Off" if you don't require the extensions, else turn "On" "pgvector" or "PostGIS" if you want to enable them.
Review the summary. You can go back to modify any configuration. The settings persist until you exit the wizard.
Click Finish to start the deployment.
ClusterControl will now start provisioning both PostgreSQL streaming replication clusters. You can monitor the progress from Activity center, where you will see detailed logs of the deployment. This process can take a few minutes. Once the deployment is complete, the clusters will appear in the Home page.

Step 3: Publication and Subscription

Now, for PostgreSQL Logical Replication, you have to create a Publication (a set of tables to be replicated), and a Subscription to access this Publication and stream changes.

Open your browser and navigate to your ClusterControl server’s IP or domain name.
Choose your PostgreSQL Logical cluster from the list.
Go to Create publications / subscriptions.
Create a new publication to publish changes to a set of tables, which can then be consumed by logical replication subscribers when creating a subscription:
(1) Create publication(2) Create subscription
Logical cluster: The selected PostgreSQL Logical cluster.

Publisher cluster: Choose one of the PostgreSQL Streaming Replication clusters.

Database: Choose the databases to be replicated.

Publication name: Create a name for the new publication.

Include all tables: On (default).

Then, press on Create publication.
Logical cluster: The selected PostgreSQL Logical cluster.

Subscriber cluster: Choose one of the PostgreSQL Streaming Replication clusters.

Subscription name: Create a name for the new subscription.

Publication: Choose an existing publication.

Database: Choose the database where to replicate data.

Copy data: On (default). This flag should be false when creating second subscription on a bi-directional link. Specifies whether to copy pre-existing data in the publications that are being subscribed to when the replication starts.

Origin: None (default). Set in None means the subscription will only replicate changes that do not have an associated origin (i.e., changes made locally). If origin is set in Any, the subscription will replicate all changes regardless of their origin, including those already replicated from other nodes.

Then, press on Create subscription.

WARNING: Setting origin=any can lead to a potential infinite loop in bi-directional replication setups, as changes replicated from one node can be sent back to the originating node.

For more information about each PostgreSQL Streaming Replication setup, refer to PostgreSQL Streaming Replication