The Complete pglogical Troubleshooting Guide: Architecture, Methodology, and Fixes

When pglogical stops replicating, the symptoms are often deceptively vague: rows stop appearing on the subscriber, disk space on the provider rapidly fills up, or PostgreSQL logs repeat generic worker termination errors.

This guide covers the core architectural principles of pglogical, a structured diagnostic workflow, and step-by-step solutions for the most common failure modes in production.

---

1. Core Concepts and Architecture

Understanding how data flows between PostgreSQL nodes is essential before running diagnostic queries.

+-----------------------------------------------------------------------------------+
| PROVIDER (Publisher)                                                              |
|                                                                                   |
|  [ User Tables ]  --->  WAL (wal_level = logical)                                 |
|                                |                                                  |
|                                v                                                  |
|                     [ Logical Replication Slot ]                                  |
|                                | (pglogical_output plugin)                        |
|                                v                                                  |
|                     [ WALSender Process ]                                         |
+--------------------------------|--------------------------------------------------+
                                 |  TCP / libpq Connection
                                 v
+-----------------------------------------------------------------------------------+
| SUBSCRIBER                                                                        |
|                                                                                   |
|  [ pglogical Supervisor ] ---> [ pglogical Manager ]                              |
|                                          |                                        |
|                                          v                                        |
|                                [ pglogical Apply Worker ]                         |
|                                (or Sync Worker for initial copy)                  |
|                                          |                                        |
|                                          v                                        |
|                                 [ Local PostgreSQL ]                              |
+-----------------------------------------------------------------------------------+

Key Components

1. Nodes (pglogical.node): Defines each database participant (a Provider node publishes data; a Subscriber node consumes it).
2. Replication Sets (pglogical.replication_set): Logical collections of tables and sequences. By default, tables added to replication belong to the default replication set.
3. Subscriptions (pglogical.subscription): Configured on the subscriber node. Manages connection parameters (dsn), which replication sets to synchronize, and whether to perform an initial data copy.
4. Logical Decoding & Replication Slots:
   • The provider uses a logical replication slot with the pglogical_output plugin.
   • The slot records the subscriber’s progress (confirmed flush LSN).
   • Crucial Rule: The provider will not discard WAL logs until the subscriber acknowledges reading them. If a subscriber disconnects, the provider’s pg_wal directory will grow until disk space runs out.
5. Background Workers:
   • On Provider: A standard walsender background process reads WAL and decodes it via the slot.
   • On Subscriber: pglogical supervisor maintains the subsystem, pglogical manager watches catalogs, and per-subscription apply workers write transactions to local storage. During initial table synchronization, temporary sync workers copy existing rows.

---

2. Systematic Troubleshooting Methodology

Do not jump straight to restarting PostgreSQL or dropping subscriptions. Work top-down through these four diagnostic layers:

[Layer 1: Infrastructure & Engine]
  -> Check postgresql.conf parameters (wal_level, workers, slots)
  -> Verify libpq network connectivity & pg_hba.conf

[Layer 2: Subscription & Slot Health]
  -> Check pglogical.show_subscription_status() on subscriber
  -> Check pg_replication_slots & pg_stat_replication on provider

[Layer 3: Table & Schema Alignment]
  -> Verify primary keys and REPLICA IDENTITY
  -> Check pglogical.local_sync_status on subscriber

[Layer 4: Application & Conflict Layer]
  -> Inspect PostgreSQL server error logs on both nodes
  -> Check for unique key constraint violations or missing foreign keys

---

3. Step-by-Step Diagnostic Queries

Step 1: Check Subscription State on the Subscriber

Run this query inside the subscriber database:

SELECT 
    subscription_name,
    status,
    provider_node,
    slot_name,
    replication_sets
FROM pglogical.show_subscription_status();

Status	Meaning	Immediate Action
replicating	Healthy streaming state.	Check replication lag if rows are delayed.
initializing	Copying schema / initial data.	Check table sync progress in pglogical.local_sync_status.
down	Apply worker is not running.	Inspect subscriber PostgreSQL error log for crashes.
failed	Fatal error during apply.	Check constraints, missing primary keys, or schema mismatches.

Step 2: Check Replication Slot & Lag on the Provider

Run this query inside the provider database:

SELECT 
    slot_name,
    plugin,
    active,
    active_pid,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

Then check streaming activity:

SELECT 
    pid,
    application_name,
    client_addr,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) AS write_lag,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) AS flush_lag,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag
FROM pg_stat_replication;

• If active = false: The subscriber is not connected. The provider is accumulating WAL.
• If retained_wal_bytes or replay_lag is continuously growing: Replication is stalled or severely bottlenecked by subscriber disk I/O.

Step 3: Check Table Synchronization Status

If the subscription is replicating or initializing but specific tables lack data, query the subscriber:

SELECT 
    sync_kind,
    sync_subid,
    sync_nspname AS schema_name,
    sync_relname AS table_name,
    sync_status
FROM pglogical.local_sync_status;

Status codes:

• i: Init / queued.
• d: Data copy in progress.
• s: Synchronized (normal streaming).
• f: Failed (check server logs for specific table errors).

---

4. Common Failure Modes and Solutions

Issue 1: Subscription Status is down or Constantly Crashing

Symptoms: pglogical.show_subscription_status() displays down. Subscriber logs show:

See also: Mastering the Linux Command Line — Your Complete Linux Training Course

LOG: background worker "pglogical apply ..." exited with exit code 1

Root Causes & Fixes:

1. Network or Authentication Failure: The subscriber cannot reach the provider using the DSN configured during create_subscription.
   • Test connectivity from the subscriber terminal:psql "host=provider_ip port=5432 dbname=mydb user=repl_user"
   • Ensure the provider’s pg_hba.conf permits both standard connections and replication connections:host mydb repl_user subscriber_ip/32 scram-sha-256 host replication repl_user subscriber_ip/32 scram-sha-256
2. Worker Starvation: PostgreSQL has exceeded its background worker budget. Verify on both provider and subscriber:SHOW max_worker_processes; -- Recommended: >= 10 SHOW max_replication_slots; -- Must be >= count of subscribers SHOW max_wal_senders; -- Must be >= count of subscribers If worker limits are reached, raise them in postgresql.conf and restart PostgreSQL.

---

Issue 2: Data Conflicts (Unique Constraint Violations)

Symptoms: The subscription drops into failed or apply workers crash with:

ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (id)=(42) already exists.

Root Cause: A row was inserted or updated directly on the subscriber with an ID that the provider subsequently tried to replicate.

Solutions:

1. Option A: Remove or update the conflicting row on the subscriber:-- Run on Subscriber DELETE FROM public.users WHERE id = 42; Once removed, restart the subscription:SELECT pglogical.alter_subscription_enable('my_subscription', true);
2. Option B: Adjust conflict resolution policy: pglogical supports automated conflict resolution. You can set the policy at the subscription or node level:-- Policies: 'error', 'apply_remote', 'keep_local', 'last_update_wins' SELECT pglogical.alter_subscription_conflict_resolution( 'my_subscription', 'last_update_wins' ); Note: last_update_wins requires track_commit_timestamp = on in postgresql.conf on both nodes.

---

Issue 3: Missing Primary Keys / Replica Identity

Symptoms: Table exists in the replication set, but updates and deletes do not replicate or cause errors:

ERROR: cannot update table "orders" because it has no replica identity and no primary key

Root Cause: Logical replication requires a unique identifier to locate the corresponding row on the subscriber for UPDATE and DELETE operations.

Fix:

1. Add a primary key to the table on both provider and subscriber:ALTER TABLE public.orders ADD PRIMARY KEY (id);
2. If adding a primary key is not immediately possible, set a unique index as the replica identity:CREATE UNIQUE INDEX orders_uuid_idx ON public.orders (order_uuid); ALTER TABLE public.orders REPLICA IDENTITY USING INDEX orders_uuid_idx;

---

Issue 4: Replication Slot Accumulating Unbounded WAL on Provider

Symptoms: Provider disk usage spikes toward 100% in pg_wal. pg_replication_slots.active is false.

Warning: If the provider runs out of disk space, PostgreSQL will perform an emergency shutdown.

Diagnostic & Emergency Procedure:

1. Identify the lagging or orphaned slot on the provider:SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag FROM pg_replication_slots;
2. If the subscriber is permanently lost or abandoned, drop the replication slot to release WAL files:SELECT pg_drop_replication_slot('pgl_mydb_provider_sub');
3. Set a safety limit in postgresql.conf on PostgreSQL 13+ to prevent future disk exhaustion:max_slot_wal_keep_size = 50GB If a slot lags beyond 50GB, PostgreSQL invalidates the slot rather than letting the disk fill up.

---

Issue 5: Schema Mismatch Between Provider and Subscriber

Symptoms: Apply worker fails with:

ERROR: table "public"."invoices" does not match between provider and subscriber: attribute 4 has different type

Root Cause: Unlike physical streaming replication, pglogical only replicates data, not DDL (unless explicitly executed via pglogical.replicate_ddl_command). If a column was added or altered on the provider without making the exact same alteration on the subscriber, the apply worker halts.

Fix:

1. Align the schema on the subscriber manually:-- On Subscriber: apply the missing DDL alteration ALTER TABLE public.invoices ADD COLUMN notes text;
2. Re-enable the subscription:SELECT pglogical.alter_subscription_enable('my_subscription', true);

---

Issue 6: Resynchronizing a Single Broken Table

If a single table gets corrupted, out of sync, or stuck in an error loop, you do not need to rebuild the entire subscription.

-- Run on Subscriber
-- 1. Resynchronize just this table from the provider
SELECT pglogical.alter_subscription_resynchronize_table(
    'my_subscription', 
    'public.users'
);

-- 2. Verify sync status starts in 'i' or 'd' and transitions back to 's'
SELECT * FROM pglogical.local_sync_status WHERE sync_relname = 'users';

---

5. Production Health-Check Runbook

Add these periodic checks to your monitoring system (Prometheus, Datadog, or Nagios):

1. Check Replication Lag in Seconds:-- On Subscriber SELECT subscription_name, status, received_lsn, last_applied_lsn FROM pglogical.show_subscription_status();
2. Alert on Inactive Slots with Significant Size: Alert if active = false AND pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 5GB on the provider.
3. Monitor Background Worker Logs: Alert on log patterns matching background worker "pglogical ... exited with exit code 1.
4. Sequence Sync: pglogical does not auto-sync sequence values on every nextval(). If doing failover preparation, periodically sync sequences:SELECT pglogical.synchronize_sequence('public.my_sequence');