Configure Logical Replication
This guide helps you set up PostgreSQL Logical Replication — replicating data changes in real time from a source cluster (Publisher) to a destination cluster (Subscriber). One or both clusters can be hosted on GreenNode vDB.
Prerequisites
At least one of the two clusters (Publisher or Subscriber) must be a PostgreSQL Cluster on GreenNode vDB (version 16 or 17).
Both clusters must run the same major version of PostgreSQL.
The user running SQL commands must be the owner of the tables to replicate (to create a Publication).
How Logical Replication Works

Publisher: the source cluster holding the original data. You create a
PUBLICATIONto define which tables are replicated.Subscriber: the destination cluster receiving changes. You create a
SUBSCRIPTIONto connect to the Publisher and pull data.
Part A: vDB PostgreSQL Cluster as Publisher
Follow the steps in this section if your GreenNode vDB cluster is the data source (Publisher).
Step A.1: Request Logical Replication Activation
Contact GreenNode Support to request Logical Replication activation on your cluster. GreenNode Support will grant the REPLICATION privilege, along with any other privileges needed, directly to your cluster's existing admin account.
When managing Subscriptions, do not delete or modify replication slots that do not belong to you. These slots may belong to the system or other Subscriptions — accidentally dropping one will cause replication loss immediately.
Step A.2: Configure PostgreSQL Parameters
Logical Replication requires three PostgreSQL parameters to be correctly configured on the Publisher cluster.
wal_level
logical
Required — default is replica, which is not sufficient for logical replication
max_replication_slots
≥ total slots needed
Total replication slots for all replicas, subscriptions, and CDC connectors
max_wal_senders
≥ total senders needed
Total WAL sender processes (typically equals max_replication_slots)
How to calculate max_replication_slots and max_wal_senders:
Each replica node in the cluster
1 slot + 1 sender
Each Subscription (logical replication)
1 slot + 1 sender
Each CDC connector (Debezium)
1 slot + 1 sender
Example: 3-node cluster (2 replicas) + 1 subscription → max_replication_slots = 3, max_wal_senders = 3.
Changing wal_level, max_replication_slots, and max_wal_senders requires a cluster restart. See PostgreSQL Cluster Parameters for instructions.
Step A.3: Create a Publication
Connect to the Publisher cluster using an account with owner rights on the tables to replicate.
Create a Publication for the tables to replicate:
Verify the Publication was created:
Part B: vDB PostgreSQL Cluster as Subscriber
Follow the steps in this section if your GreenNode vDB cluster is the data destination (Subscriber).
Step B.1: Request Logical Replication Activation
Contact GreenNode Support to request Logical Replication activation on your cluster. GreenNode Support will grant the REPLICATION privilege, along with any other privileges needed, directly to your cluster's existing admin account.
When managing Subscriptions, do not delete or modify replication slots that do not belong to you. These slots may belong to the system or other Subscriptions — accidentally dropping one will cause replication loss immediately.
Step B.2: Create Tables on Subscriber
Logical Replication does not create tables automatically on the Subscriber. You must create the schema and tables on the Subscriber before creating the Subscription.
The schema and data types of tables on the Subscriber must exactly match the Publisher. A mismatch will cause the Subscription to fail when applying changes.
You can create the tables using one of the following methods:
Create the table manually
Write the CREATE TABLE statement directly, matching the Publisher's schema. Suitable when only a few simple tables need to be replicated.
Create the table using pg_dump
Instead of writing DDL by hand, use pg_dump --schema-only to dump the table structure from the Publisher, review the file, then apply it to the Subscriber with psql -f.
To dump multiple tables, repeat --table for each one. Omit --table to dump the entire schema.
Step B.3: Create a Subscription
Connect to the Subscriber cluster using the admin account granted
REPLICATIONin Step B.1.Create the Subscription:
host
Publisher hostname
port
PostgreSQL connection port
dbname
Source database name on Publisher
user
Username with replication rights on Publisher
password
Password with replication rights on Publisher
sslmode
SSL encryption mode
publication_name
Publication name on Publisher
The user/password in the CONNECTION string above is the Publisher's account, not the Subscriber account you used to connect and run the CREATE SUBSCRIPTION command.
By default, PostgreSQL performs an initial sync: copying all existing data from the Publisher to the Subscriber before switching to streaming new changes.
If the Subscriber already has data (for example, restored earlier via pg_dump), you can skip the initial sync:
When copy_data = false, the Subscriber only receives changes that occur after the Subscription is created.
Verify Replication Status
On Publisher — check connected Subscriptions:
On Subscriber — check Subscription status:
Replication is working correctly when state = streaming on the Publisher and, on the Subscriber, pid is non-NULL with received_lsn advancing.
Result
Once complete, data from the tables in your Publication on the Publisher will be continuously synchronized to the Subscriber in real time. All changes (INSERT, UPDATE, DELETE) are applied automatically.
Set up CDC with Debezium
View cluster configuration parameters
Last updated

