Fix PostgreSQL Logical Replication Errors - Complete Guide

# PostgreSQL Logical Replication Error - Troubleshooting Guide

Logical replication in PostgreSQL allows selective table replication and cross-version upgrades. However, setup issues, conflicts, and network problems can disrupt replication. Let's work through the most common logical replication errors.

Understanding Logical Replication Architecture

Logical replication uses these components:

Publication: Defines what data to replicate (tables, operations)
Subscription: Defines where to receive data (publisher connection info)
Replication slots: Ensure WAL is retained for subscribers
WAL sender processes: Stream changes to subscribers

```bash # Check logical replication is enabled psql -U postgres -c "SHOW wal_level;" # Must be 'logical' or 'replica' (for mixed physical/logical)

# Check max_replication_slots psql -U postgres -c "SHOW max_replication_slots;"

# Check max_wal_senders psql -U postgres -c "SHOW max_wal_senders;" ```

Publication Errors

Error: "publication does not exist"

```sql -- On publisher, check publications SELECT * FROM pg_publication;

-- Create publication if missing CREATE PUBLICATION my_pub FOR TABLE users, orders, products;

-- For all tables in database CREATE PUBLICATION all_tables FOR ALL TABLES;

-- For specific schemas CREATE PUBLICATION sales_pub FOR TABLES IN SCHEMA sales;

-- Verify publication tables SELECT * FROM pg_publication_tables WHERE pubname = 'my_pub'; ```

Error: "table is not a member of publication"

```sql -- Add table to existing publication ALTER PUBLICATION my_pub ADD TABLE new_table;

-- Remove table from publication ALTER PUBLICATION my_pub DROP TABLE old_table;

-- Replace all tables in publication ALTER PUBLICATION my_pub SET TABLE users, orders, products;

-- Add tables from schema ALTER PUBLICATION my_pub ADD TABLES IN SCHEMA analytics; ```

Error: "permission denied for publication"

```sql -- Grant replication privilege to user ALTER USER replication_user REPLICATION;

-- Grant SELECT on replicated tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;

-- For future tables in schema ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user; ```

Subscription Errors

Error: "could not connect to the publisher"

```sql -- Check subscription status SELECT subname, subenabled, subconninfo, subslotname, subpublications FROM pg_subscription;

-- Check subscription worker status SELECT subid, subname, pid, relid::regclass, received_lsn, latest_end_lsn FROM pg_stat_subscription; ```

Test the connection manually:

```bash # Test connection with subscription credentials psql "host=publisher-host port=5432 user=replication_user password=secret dbname=mydb"

# Check connectivity and authentication psql -h publisher-host -U replication_user -d mydb -c "SELECT 1;" ```

Error: "subscription already exists"

```sql -- Disable subscription before major changes ALTER SUBSCRIPTION my_sub DISABLE;

-- Drop subscription (stops replication, keeps slot on publisher) DROP SUBSCRIPTION my_sub;

-- Drop subscription and remove slot from publisher DROP SUBSCRIPTION my_sub; -- Then on publisher: SELECT pg_drop_replication_slot('subscription_slot_name'); ```

Error: "slot does not exist"

The replication slot was dropped on the publisher:

```sql -- Check slot on publisher SELECT * FROM pg_replication_slots WHERE slot_name = 'my_sub_slot';

-- If slot missing, create it SELECT pg_create_logical_replication_slot('my_sub_slot', 'pgoutput');

-- Refresh subscription to use new slot ALTER SUBSCRIPTION my_sub DISABLE; ALTER SUBSCRIPTION my_sub SET (slot_name = 'my_sub_slot'); ALTER SUBSCRIPTION my_sub ENABLE; ```

Replication Slot Issues

Error: "no free replication slots"

```sql -- Check current slots SELECT slot_name, slot_type, active, restart_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes FROM pg_replication_slots;

-- Increase max_replication_slots ALTER SYSTEM SET max_replication_slots = 20; SELECT pg_reload_conf();

-- Or drop unused slots SELECT pg_drop_replication_slot('unused_slot'); ```

Error: "replication slot is active"

```sql -- Check what's using the slot SELECT s.slot_name, a.pid, a.usename, a.client_addr, a.state FROM pg_replication_slots s LEFT JOIN pg_stat_activity a ON a.pid = s.active_pid WHERE s.slot_name = 'my_slot';

-- If process is stuck, terminate it SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = <pid>;

-- Then slot should be available ```

Data Synchronization Issues

Initial Sync Failed

When subscription's initial data sync fails:

```sql -- Check sync status SELECT srsubid, srrelid::regclass, srsubstate, srsublsn FROM pg_subscription_rel WHERE srsubid = (SELECT oid FROM pg_subscription WHERE subname = 'my_sub');

-- States: i = initialize, d = data, s = sync, r = ready -- If stuck in 'i' or 'd', check logs ```

bash

# Check PostgreSQL logs for sync errors
sudo grep "replication\|subscription\|sync" /var/log/postgresql/postgresql-*-main.log | tail -50

Refresh Subscription Data

```sql -- Refresh all tables in subscription ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION;

-- Refresh specific tables ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION WITH (copy_data = false);

-- Re-sync specific table ALTER SUBSCRIPTION my_sub DISABLE; -- Manually truncate table on subscriber TRUNCATE target_table; ALTER SUBSCRIPTION my_sub ENABLE; ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION WITH (copy_data = true, copy_table = 'target_table'); ```

Conflict Resolution

Logical replication can encounter conflicts when subscriber has different data:

Error: "duplicate key value violates unique constraint"

```sql -- Check conflicting rows SELECT * FROM target_table WHERE id = <conflicting_id>;

-- Option 1: Delete conflicting row on subscriber DELETE FROM target_table WHERE id = <conflicting_id>;

-- Option 2: Skip the conflicting transaction -- Use pg_replication_origin functions to skip SELECT pg_replication_origin_advance( pg_replication_origin_oid('pg_' || oid), '<lsn_of_conflict>' ) FROM pg_subscription WHERE subname = 'my_sub';

-- Get LSN from error message or subscriber logs ```

Error: "update or delete on table violates foreign key constraint"

```sql -- Replication order matters for foreign keys -- Ensure tables are replicated in correct order

-- Option 1: Defer constraints ALTER TABLE child_table DROP CONSTRAINT fk_constraint, ADD CONSTRAINT fk_constraint FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;

-- Option 2: Replicate parent tables first -- Create separate publications for parent and child tables CREATE PUBLICATION parent_pub FOR TABLE parent_table; CREATE PUBLICATION child_pub FOR TABLE child_table;

-- Create subscriptions in order CREATE SUBSCRIPTION parent_sub CONNECTION '...' PUBLICATION parent_pub; -- Wait for sync, then: CREATE SUBSCRIPTION child_sub CONNECTION '...' PUBLICATION child_pub; ```

Performance Issues

Replication Lag

```sql -- On publisher, check lag per subscriber SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_size FROM pg_stat_replication;

-- On subscriber, check apply lag SELECT subname, received_lsn, latest_end_lsn, pg_wal_lsn_diff(latest_end_lsn, received_lsn) AS lag_bytes FROM pg_stat_subscription; ```

Reduce Replication Lag

bash

# On publisher, tune WAL sender
sudo nano /etc/postgresql/16/main/postgresql.conf

```ini # Increase WAL sender processes max_wal_senders = 10

# Keep more WAL for subscribers wal_keep_size = 2GB

# Optimize for logical replication max_replication_slots = 20 wal_sender_timeout = 60s ```

```sql -- On subscriber, tune apply rate -- Increase parallel workers (PostgreSQL 14+) ALTER SUBSCRIPTION my_sub SET (streaming = on);

-- Or for PostgreSQL 16+ ALTER SUBSCRIPTION my_sub SET (parallel = 4); ```

Monitoring Logical Replication

```sql -- Create monitoring view on publisher CREATE OR REPLACE VIEW logical_replication_status AS SELECT s.slot_name, s.slot_type, s.active, s.restart_lsn, a.pid, a.client_addr, a.state, a.sent_lsn, a.replay_lsn, pg_wal_lsn_diff(a.sent_lsn, a.replay_lsn) AS lag_bytes, pg_size_pretty(pg_wal_lsn_diff(a.sent_lsn, a.replay_lsn)) AS lag_size, a.sync_state FROM pg_replication_slots s LEFT JOIN pg_stat_replication a ON a.pid = s.active_pid;

-- Create monitoring view on subscriber CREATE OR REPLACE VIEW subscription_status AS SELECT s.subname, s.subenabled, s.subpublications, st.pid, st.received_lsn, st.latest_end_lsn, st.latest_end_time, pg_wal_lsn_diff(st.latest_end_lsn, st.received_lsn) AS apply_lag_bytes FROM pg_subscription s LEFT JOIN pg_stat_subscription st ON st.subid = s.oid; ```

Common Error Patterns and Solutions

Error: "schema must be created before publication"

```sql -- Create schema on subscriber first CREATE SCHEMA IF NOT EXISTS target_schema;

-- Then refresh subscription ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION; ```

Error: "subscription needs to be disabled to change parameter"

sql

-- Disable before changes
ALTER SUBSCRIPTION my_sub DISABLE;
ALTER SUBSCRIPTION my_sub SET (slot_name = 'new_slot');
ALTER SUBSCRIPTION my_sub ENABLE;

Error: "logical decoding requires wal_level >= logical"

```sql -- Check and set wal_level SHOW wal_level; ALTER SYSTEM SET wal_level = logical; SELECT pg_reload_conf();

-- Requires restart -- sudo systemctl restart postgresql ```

Rebuilding Logical Replication

When replication is beyond repair:

```sql -- On subscriber ALTER SUBSCRIPTION my_sub DISABLE; DROP SUBSCRIPTION my_sub;

-- On publisher SELECT pg_drop_replication_slot('old_slot_name');

-- Recreate from scratch -- On publisher CREATE PUBLICATION my_pub FOR ALL TABLES;

-- On subscriber CREATE SUBSCRIPTION my_sub CONNECTION 'host=publisher user=replicator password=secret dbname=mydb' PUBLICATION my_pub WITH (create_slot = true, copy_data = true); ```

Logical replication provides flexibility but requires careful attention to publications, subscriptions, and slot management. Monitor lag regularly and address conflicts promptly to maintain healthy replication.