Setting up logical replication between PostgreSQL 17 (master) and PostgreSQL 16 (replica)

Setting up logical replication between PostgreSQL 17 (master) and PostgreSQL 16 (replica), and vice versa, involves several key steps. The main goal is to ensure both systems are properly configured for logical replication, and then to establish the replication connection from PostgreSQL 17 (master) to PostgreSQL 16 (replica).

Prerequisites

PostgreSQL 17 (master) and PostgreSQL 16 (replica) should both be installed.
Both systems should be accessible from each other (network connectivity).
You should have superuser privileges on both systems.

High-Level Steps:

Configure the Master (PostgreSQL 17) for logical replication.
Configure the Replica (PostgreSQL 16) for logical replication.
Create a Replication User on the Master.
Create a Replication Subscription on the Replica.

Detailed Steps

CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'password';

1. Configure the Master (PostgreSQL 17)

Edit the postgresql.conf file on the master server:

Open the PostgreSQL configuration file (postgresql.conf) and set the following parameters:


# Enable logical replication

wal_level = logical

# Set max replication slots (if necessary)

max_replication_slots = 4

# Set max replication subscriptions (if necessary)

max_replication_slots = 4

# Optionally, increase the max number of replication workers

max_worker_processes = 8

# Allow replication connections

listen_addresses = '*'

Edit the pg_hba.conf file to allow replication connections from the replica:

Add a line to pg_hba.conf to allow connections from the replica server using replication privileges:

# Allow replication connections from the replica server

host replication replicator <replica_ip>/32 scram-sha-256

Replace <replica_ip> with the actual IP address of the PostgreSQL 16 replica.

Restart the PostgreSQL 17 server to apply the changes:

sudo systemctl restart postgresql

Create a Replication Role (if not already created):

Log into the PostgreSQL 17 instance and create a user with replication privileges:

CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'password';

2. Configure the Replica (PostgreSQL 16)

Edit the postgresql.conf file on the replica server:

Open the postgresql.conf file and set the following parameters:

# Enable logical replication on the replica

wal_level = logical

# Set max replication slots (if necessary)

max_replication_slots = 4

# Set max replication workers (if necessary)

max_worker_processes = 8

# Set the replication delay to a reasonable level

hot_standby = on

Edit the pg_hba.conf file to allow replication from the master server:

Add the following line to pg_hba.conf to allow replication connections from the master server:

# Allow replication connections from the master server

host replication replicator <master_ip>/32 scram-sha-256

Replace <master_ip> with the actual IP address of the PostgreSQL 17 master.

Restart the PostgreSQL 16 server to apply the changes:

sudo systemctl restart postgresql

3. Set up the Logical Replication (Master)

Create a publication on the PostgreSQL 17 (master) instance to specify which tables should be replicated:

Log into PostgreSQL 17 and create a publication for the desired tables:

-- CREATE PUBLICATION my_publication FOR TABLE my_table;

Replace my_publication with the name you want to give to the publication.
Replace my_table with the actual table name you wish to replicate, or omit it to replicate all tables.

Alternatively, you can create a publication for all tables:

CREATE PUBLICATION my_publication FOR ALL TABLES;

Verify the publication:

You can verify the publication by running:

\dRp+ my_publication

4. Set up the Logical Replication (Replica)

Before creating subscription on the PostgreSQL 16 (replica) instance to subscribe to the publication from the master. You will have to create the database structure with all the schema and tables. Using pg_dump on master execute:

pg_dump -s <dbname> > <dbname>.sql

Then create the database on the replica and use the dump to create the structure for replication.

Create a subscription on the PostgreSQL 16 (replica) instance to subscribe to the publication from the master:

Log into PostgreSQL 16 and create a subscription to the publication on the master server:

CREATE SUBSCRIPTION my_subscription

CONNECTION 'host=<master_ip> port=5432 dbname=<dbname> user=replicator password=replicator_password'

PUBLICATION my_publication;

Replace:

<master_ip> with the IP address of the master server.
<dbname> with the name of the database on the master server.
replicator_password with the password for the replicator user.
my_publication with the name of the publication you created.

Verify the subscription:

You can verify the subscription by running:

\dRs+ my_subscription

Check replication status:

You can check the replication status using the following query:

SELECT * FROM pg_stat_subscription;

This will show the status of the subscription, such as whether data is being replicated, and if there are any issues.

5. Troubleshooting

Check the replication status:

On the master server, check for any replication issues using the following:

SELECT * FROM pg_stat_replication;

Logs:

Check the PostgreSQL logs for any replication errors. Logs are typically found in /var/log/postgresql/ or /var/log/postgresql/postgresql-<version>-main.log depending on your system and configuration.

Resynchronize: If there are issues with data consistency, you can drop the subscription and recreate it. You may also need to reset replication slots drop the database and recreate all from scratch.

Additional Notes

Adding new table on master: After you add new table to master you will have to create the table onto the replica and for replication to work execute the following:

`ALTER SUBSCRIPTION my_subscription REFRESH publication;`

Replication Slot Limitation: The number of replication slots on both the master and replica is limited by the max_replication_slots setting in postgresql.conf.
Version Compatibility: Ensure that both PostgreSQL versions are compatible with each other. PostgreSQL 16 and 17 should support logical replication, but always check for any specific version-related bugs or issues in the release notes.
Continuous Replication: Logical replication is asynchronous, so there may be a delay between when changes are made on the master and when they appear on the replica.

This setup should help you get logical replication working between PostgreSQL 17 and PostgreSQL 16.

Last modified on 2024-12-15