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.

PostgreSQL - Logical Replication Reset

 

Introduction – Logical Replication Reset

A logical replication reset is performed when replication is not functioning correctly or when the source and target databases become out of sync. This may occur due to replication lag, failed table synchronization, replication slot issues, or configuration problems.

 

The reset process involves stopping the existing replication, cleaning up the old configuration, truncating subscriber data if required, and recreating the replication so that data can synchronize again correctly from the publisher (source) to the subscriber (target) database.

 

This document describes the basic steps and checks required to safely reset logical replication using generic database, schema, and table names.

1. Host Details

Publisher Host (Source):

• HOSTNAME_OR_IP_PUBLISHER

 Subscriber Host (Target):

• HOSTNAME_OR_IP_SUBSCRIBER

2. Drop the Existing Subscription (On Subscriber)

Drop the subscription on the subscriber database.

 DROP SUBSCRIPTION subscription_name;

 

Important Subscription Attributes:

• subname → Name of the logical replication subscription.

• subenabled → Indicates whether subscription is enabled.

• subconninfo → Connection details of the publisher database.

• subslotname → Logical replication slot name on publisher.

• subpublications → Publication used by the subscription.

• subsynccommit → Usually off for logical replication.

3. Verify and Remove Replication Slot (On Publisher)

After dropping the subscription, verify whether the replication slot still exists on the publisher.

 

SELECT slot_name FROM pg_replication_slots;

 

If the slot still exists, drop it manually:

 

SELECT pg_drop_replication_slot('subscription_name');

4. Truncate Tables on Subscriber

Before reinitializing replication, truncate the tables on the subscriber to avoid conflicts.

 

You can retrieve the list of tables included in the publication using the following query:

Query to List Tables in Publication

SELECT

    p.pubname,

    n.nspname AS schema_name,

    c.relname AS table_name

FROM pg_publication p

JOIN pg_publication_rel pr ON p.oid = pr.prpubid

JOIN pg_class c ON pr.prrelid = c.oid

JOIN pg_namespace n ON c.relnamespace = n.oid

WHERE p.pubname = 'publication_name'

ORDER BY schema_name, table_name;

5. Create a New Subscription

Recreate the subscription on the subscriber database.

 

CREATE SUBSCRIPTION subscription_name

CONNECTION 'host=PUBLISHER_HOST port=5432 dbname=database_name user=replication_user password=PASSWORD'

PUBLICATION publication_name

WITH (

    copy_data = true,

    create_slot = true,

    enabled = true

);

Parameter Explanation

copy_data = true

→ Copies existing data from publisher to subscriber during initialization.

 

create_slot = true

→ Automatically creates a replication slot on the publisher.

 

enabled = true

→ Starts replication immediately after creation.

6. Monitor Replication Status (On Publisher)

Monitor replication until the subscriber catches up.

 

SELECT

    pid,

    usename,

    application_name,

    state,

    date_trunc('minute', backend_start) AS backend_start,

    sent_lsn,

    ROUND(((pg_current_wal_lsn() - sent_lsn) / (1024 * 1024.0)), 2) AS mb_lag

FROM pg_stat_replication;

Best Practices

• Perform replication reset during a maintenance window.

• Ensure network connectivity between publisher and subscriber.

• Validate row counts after replication completes.

• Monitor replication slots to prevent WAL accumulation.

• Confirm that replication user has required privileges.

Conclusion

Resetting logical replication is a safe and effective way to recover from replication issues when performed with proper validation and cleanup steps. Following a structured approach ensures minimal downtime and consistent data synchronization between environments.

Streaming Replication Useful Queries

How to Check Replication Status Properly

On Primary -

SELECT pid, usename, application_name, state

 --, client_addr

 , date_trunc('minute', backend_start) as backend_start

 , sent_lsn, round(((pg_current_wal_lsn()-sent_lsn)/(1024*1024.0)), 2) as mb_diff

from pg_stat_replication;

SELECT pid, state, sync_state, write_lag, flush_lag, replay_lag

FROM pg_stat_replication;

1) SELECT pg_current_wal_lsn();

2) SELECT pg_current_wal_insert_lsn();

3) WAL Switch Functions -

#SELECT pg_switch_wal();

Run this on STANDBY

SELECT 

    pg_last_wal_receive_lsn()  AS receive_lsn,

    pg_last_wal_replay_lsn()   AS replay_lsn,

    pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS byte_lag;

1) Last received LSN

SELECT pg_last_wal_receive_lsn();

        

2) Last replayed LSN

        SELECT pg_last_wal_replay_lsn();

    

3) WAL difference

    SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn());

    SELECT pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.replay_lsn) as byte_lag        FROM pg_stat_replication