PostgreSQL - Step by Step Logical Replication Setup

 

Prerequisites

• PostgreSQL version 10 or higher on both servers

• Network connectivity between source and target servers

• wal_level must be set to logical on the source server

• Superuser or replication privileges available

1. Configure Source Server

1.1 Ensure wal_level is logical (postgresql.conf):

wal_level = logical

max_replication_slots = 10

max_wal_senders = 10

 

Reload configuration:

SELECT pg_reload_conf();

 

1.2 Allow target server to connect (pg_hba.conf):

host replication replication_user <TARGET_IP>/32 md5

host all replication_user <TARGET_IP>/32 md5

 

Reload configuration:

SELECT pg_reload_conf();

 

1.3 Create replication user:

CREATE USER replication_user WITH REPLICATION LOGIN PASSWORD 'StrongPassword';

 

1.4 Grant SELECT on tables to replication user:

GRANT SELECT ON TABLE schema_name.table1 TO replication_user;

GRANT SELECT ON TABLE schema_name.table2 TO replication_user;

GRANT USAGE ON SCHEMA schema_name TO replication_user;

2. Create Publication on Source

CREATE PUBLICATION publication_name FOR TABLE table1, table2;

 

Verify:

\dRp+ publication_name

3. Ensure Tables Have Primary Keys

Logical replication requires primary keys or unique indexes.

 

Check tables:

\d table1

\d table2

 

If missing:

ALTER TABLE table1 ADD PRIMARY KEY (id);

ALTER TABLE table2 ADD PRIMARY KEY (id);

4. Prepare Target Server

Create same tables on target with identical structure (excluding constraints/triggers).

 

Using schema-only dump:

pg_dump -s -t table1 -t table2 source_db > schema.sql

psql target_db < schema.sql

5. Create Subscription on Target

CREATE SUBSCRIPTION subscription_name

CONNECTION 'host=<SOURCE_IP> port=5432 dbname=source_db user=replication_user password=StrongPassword'

PUBLICATION publication_name;

 

This will:

• Copy initial data

• Start ongoing replication

6. Check Replication Status

On target:

SELECT * FROM pg_subscription;

SELECT * FROM pg_stat_subscription;

 

On source:

SELECT * FROM pg_stat_replication;

7. What Gets Replicated Automatically

INSERT → Yes

UPDATE → Yes

DELETE → Yes

8. Add More Tables Later

On source:

ALTER PUBLICATION publication_name ADD TABLE new_table;

 

On target:

ALTER SUBSCRIPTION subscription_name REFRESH PUBLICATION;

9. Remove Replication

On target:

DROP SUBSCRIPTION subscription_name;

 

On source:

DROP PUBLICATION publication_name;

Conclusion

This guide provides a generic approach for configuring logical replication between PostgreSQL servers. Following these steps ensures reliable data synchronization between environments.