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.