PostgreSQL 논리 복제의 숨겨진 복잡성

We were building a PostgreSQL Logical Replication monitoring system — topology visualization, lag alerts, health checks — across PostgreSQL 10 through 17. The problem we kept running into wasn’t missing documentation. It was that logical replication metadata is fundamentally decentralized: the publisher knows its publications, the subscriber knows its subscriptions, and neither side has the full picture. Everything we needed to build — topology, lag monitoring, health classification — had to be assembled from fragments spread across machines that don’t know about each other.

The rest of this article explores ten consequences of that architectural split.

This article assumes familiarity with PostgreSQL logical replication basics: publications, subscriptions, and replication slots.

Architecture: who knows what

This diagram is the foundation for everything that follows. The architectural lessons that follow are consequences of this split.

PUBLISHER                          SUBSCRIBER
─────────────────────────────      ─────────────────────────────
pg_publication                     pg_subscription
  └─ what is published               └─ where to connect
                                       └─ what to subscribe to

pg_replication_slots               pg_subscription_rel
  └─ how much WAL is retained        └─ sync progress per table

pg_stat_replication                pg_stat_subscription
  └─ write_lag                       └─ apply worker pid
  └─ flush_lag                       └─ last_msg_receipt_time
  └─ replay_lag                      └─ (no lag fields)
  └─ who is connected right now

pg_stat_replication                (no equivalent on subscriber)
  └─ subscriber is visible only
     while connected

Enter fullscreen mode Exit fullscreen mode

Lag lives only on the publisher. The subscriber list is stored nowhere on the publisher. WAL slot metrics are unavailable on the subscriber. These facts only surface when you try to build something on top of replication.

Part I — Architectural consequences

These aren’t quirks. They’re deliberate design decisions with implications that only become visible when you’re building on top of replication.

1. The publisher doesn’t know its subscribers — and that’s intentional

It seems natural to ask the publisher “who is subscribed to you.” But pg_publication contains no information about subscribers. pg_stat_replication only shows actively connected WAL senders — disabled and offline subscriptions never appear there.

This is a direct consequence of how PostgreSQL models replication. A subscription is a subscriber-side object. A publication is a publisher-side object. The publisher streams WAL to whoever connects, but maintains no registry of subscribers — the publisher must remain independent of the number and composition of its subscribers. Scalability is achieved precisely through this asymmetry.

To display the subscriber list for a publication, you have to query every other instance:

-- Run on EACH subscriber:
SELECT subname FROM pg_subscription WHERE 'pub_name' = ANY(subpublications)

Enter fullscreen mode Exit fullscreen mode

In one production installation, a single publication had up to 12 subscribers across different hosts. This required a two-tier strategy: live fan-out for instances managed by the same agent, plus a heartbeat JSONB cache for cross-host subscribers.

Once you realize this, you stop looking for “the query” that returns the topology. There isn’t one.

2. Replication lag exists only on the publisher

pg_stat_subscription on the subscriber has no write_lag, flush_lag, or replay_lag fields. All of that lives exclusively in pg_stat_replication on the publisher — as shown in the diagram above.

The reason is architectural: only the WAL sender knows two timestamps — when WAL was sent, and when confirmation arrived from the receiver. The subscriber physically cannot compute its own lag.

If the subscriber is offline, pg_stat_replication is empty and lag is None. You cannot distinguish “subscriber is disconnected” from “lag is critical” using only subscriber-side data.

There’s also a parsing problem: depending on the psycopg2 version and interval_style setting, lag intervals arrive as timedelta, float, or a "HH:MM:SS.fff" string. A normalizer that handles all three formats is mandatory.

Alert thresholds that worked in production:

| lag_ms                   | Slot wal_status | Subscription state | Alert level           |
|--------------------------|-----------------|--------------------|-----------------------|
| < 10 000                 | reserved        | running            | healthy               |
| 10 000 – 59 999          | —               | —                  | warning               |
| ≥ 60 000                 | —               | —                  | critical              |
| None (no WAL sender)     | —               | —                  | disconnected          |
| —                        | extended        | —                  | warning               |
| —                        | lost            | —                  | critical (blocking)   |
| —                        | —               | no_worker > 30s    | critical              |
| —                        | —               | disabled           | disconnected          |

Enter fullscreen mode Exit fullscreen mode

wal_status = lost is not recoverable by restarting anything — see lesson 5. The 30-second grace period on no_worker prevents false alerts during subscription creation, when a worker that’s starting and one that’s crashed look identical.

3. system_identifier: the only reliable guard against circular replication

PostgreSQL does not prevent CREATE SUBSCRIPTION sub CONNECTION 'host=localhost ...' PUBLICATION pub. The command will succeed and create an infinite loop — WAL will grow exponentially.

Our first instinct was to check the hostname. It didn’t work — one instance can have multiple interfaces, IPv4 and IPv6, hostname vs IP, or a VIP that moves during failover.

PostgreSQL already exposes something stronger: pg_control_system() returns system_identifier — a 64-bit fingerprint generated at initdb and immutable for the lifetime of the cluster:

if publisher_sysid == subscriber_sysid:
    raise HTTPError(400, "Cannot subscribe to self")

Enter fullscreen mode Exit fullscreen mode

This only guards against the direct cycle A→A. A multi-hop cycle A→B→C→A requires full graph analysis — we chose to protect only the obvious case. A promoted physical replica inherits the primary’s system_identifier, so the check has boundaries in high-availability setups.

4. DROP SUBSCRIPTION hangs when the publisher is unreachable

DROP SUBSCRIPTION automatically calls pg_drop_replication_slot() on the publisher. If the publisher is unreachable, the command hangs waiting for a connect timeout. A user cannot remove a broken subscription at exactly the moment they need to most — during an outage.

The reason is the same philosophy as lesson 5: an abandoned slot accumulates WAL indefinitely, and PostgreSQL avoids making destructive decisions automatically.

The recovery procedure has two steps:

ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET (slot_name = NONE)  -- detach slot reference
DROP SUBSCRIPTION IF EXISTS name                -- no publisher contact needed

Enter fullscreen mode Exit fullscreen mode

The slot becomes an orphan intentionally. We return an explicit instruction: run SELECT pg_drop_replication_slot('slot_name') on the publisher when it comes back.

5. wal_status = lost: the slot is dead, but PostgreSQL won’t drop it

The progression: reserved → extended (WAL exceeded max_slot_wal_keep_size) → lost (WAL deleted at the next checkpoint). The transition happens during a normal checkpoint with no default log warning.

Once a slot reaches lost, the apply worker fails on every retry. The slot isn’t dropped automatically — PostgreSQL leaves destructive decisions to the DBA.

On PG < 13, wal_status doesn’t exist. Detecting WAL loss without manually comparing LSN values is impossible on older versions.

Once a slot becomes lost, monitoring should stop treating it as a performance problem and start treating it as a recovery problem: drop the slot, recreate the subscription with copy_data=true, and accept the data resync.

Part II — Non-obvious catalog behavior

6. pg_stat_subscription has one row per worker, not per subscription

Everything looked correct in testing. Then one customer initialized a subscription with 180 tables — and suddenly our topology page showed every subscription 180 times.

When a subscription is created with copy_data=true, PostgreSQL parallelizes the initial sync by launching a separate tablesync worker per table. All of them appear in pg_stat_subscription alongside the main apply worker:

Apply worker        ← relid IS NULL  (coordinator)
├── TableSync #1   ← relid = 'orders'::regclass
├── TableSync #2   ← relid = 'events'::regclass
└── ...            (one per table)

Enter fullscreen mode Exit fullscreen mode

The bug only reproduced during active sync — by the time we ran tests, the 8-minute initial copy had finished.

The fix is one predicate:

LEFT JOIN pg_stat_subscription srss ON s.oid = srss.subid AND srss.relid IS NULL

Enter fullscreen mode Exit fullscreen mode

Without AND srss.relid IS NULL, any query against subscriptions is semantically incorrect during initial sync.

7. pg_subscription has no state field — you have to compute it

SELECT state FROM pg_subscription — this field doesn’t exist in any version of PostgreSQL. It appeared in pg_stat_subscription only in PG 14. On PG 10–13, there’s no explicit subscription status anywhere.

The only solution compatible with PG 10+:

CASE
    WHEN NOT s.subenabled   THEN 'disabled'
    WHEN srss.pid IS NULL   THEN 'no_worker'
    ELSE 'running'
END

Enter fullscreen mode Exit fullscreen mode

This can’t distinguish “worker is starting” from “worker has crashed” — both produce pid IS NULL, which is why the grace period from lesson 2 matters.

8. Version compatibility isn’t a maintenance problem — it’s an architectural constraint

Practically every major PostgreSQL version doesn’t just add new fields. It changes existing ones. Passing an unknown argument to CREATE SUBSCRIPTION WITH (...) on an older version is an error, not a no-op. The substream column changed from bool to char between PG 15 and PG 16 — same column name, same query, different Python type on the other side. A parser treating the result as boolean silently misreads every value on PG 16+.

Supporting multiple PostgreSQL versions becomes a code generation problem rather than a SQL problem:

def get_subscriptions_query(server_version: int) -> str:
    if server_version >= 160000:
        streaming_expr = "CASE s.substream WHEN 'f' THEN 'off' WHEN 't' THEN 'on' WHEN 'p' THEN 'parallel' END"
    elif server_version >= 140000:
        streaming_expr = "CASE WHEN s.substream THEN 'on' ELSE 'off' END"
    else:
        streaming_expr = "'off'"
    # ... rest of query

def get_create_logical_slot_query(server_version: int) -> str:
    if server_version >= 170000: return "... %(failover)s)"
    if server_version >= 150000: return "... %(twophase)s)"
    return "... %(temporary)s)"

Enter fullscreen mode Exit fullscreen mode

The same pattern applies to subscription DDL, slot DDL, and pg_subscription SELECT. One codebase, PG 10–17.

Part III — Where our assumptions broke

The previous lessons describe PostgreSQL’s architecture. The next two describe our own mistakes — both caused by misreading that architecture.

9. Every subscription showed disconnected

All subscriptions in the topology graph showed a disconnected icon. Replication was working normally. Tests were green..

Inside build_topology(), we were classifying subscriptions using classify_lag_status(lag_ms). But as the diagram at the top shows, lag fields live in pg_stat_replication, not pg_stat_subscription. For subscriptions, lag_ms is always None. The function returned disconnected for any None input.

In tests, we verified topology against static fixtures with manually set lag values. In production, data came from real PostgreSQL.

The fix: for subscriptions, use classify_subscription_state(state)["status"] — the source of truth is pid in pg_stat_subscription, not the WAL sender. Tests against static fixtures can’t catch this kind of bug — it only surfaces with a real PostgreSQL connection.

10. Fallback code needs its own tests

In check_prerequisites, we use a PL/pgSQL batch function. If unavailable, a Python fallback runs. The fallback referenced t.get('has_pk', False), but the actual field name is has_primary_key. Result: every table got a “no primary key” warning.

The fallback triggered on the very first prerequisites run, before the PL/pgSQL function existed. The fix was one word.

The interesting part isn’t the bug — it’s that fallback branches don’t break the main flow, so they pass code review and test suites. They’re needed in exactly the scenarios where nobody is watching. Any degraded path requires its own test with explicit mocking of the primary path.

Conclusion

Monitoring logical replication turned out to be less about querying PostgreSQL and more about accepting a fundamental constraint: the metadata you need is decentralized by design. Lag is on the publisher because only the WAL sender knows when data was sent. Subscribers aren’t tracked on the publisher because tracking them would create coupling. wal_status = lost doesn’t auto-delete because PostgreSQL doesn’t make destructive decisions unilaterally.

Once you accept that the picture has to be assembled rather than retrieved, PostgreSQL’s behavior stops looking inconsistent. Every piece of metadata lives exactly where it belongs. The monitor’s job is simply to put the pieces back together.

And yes — test your fallback paths.