Subscriber-side visibility

Table-level progress: pg_subscription_rel

Table synchronization state for each subscription is exposed through pg_subscription_rel. Joining with pg_subscription and pg_class makes the output easier to read.

SELECT s.subname,
       c.relname AS table_name,
       r.srsubstate,
       r.srsublsn
FROM pg_subscription_rel AS r
JOIN pg_subscription AS s ON s.oid = r.srsubid
JOIN pg_class AS c ON c.oid = r.srrelid
WHERE c.relkind = 'r'
ORDER BY s.subname, c.relname;

Example result:

 subname | table_name | srsubstate | srsublsn  
---------+------------+------------+-----------
 sub1    | t_a        | r          | 0/453EF50
 sub1    | t_b        | r          | 0/453EF88
 sub1    | t_c        | r          | 0/453EFC0

State codes:

• i: initializing (table entry created; sync worker not yet copying)
• d: copying data (base table snapshot in progress)
• s: synchronized (initial copy done and caught up to apply worker)
• r: ready/replicating (normal steady-state)

srsublsn records the publisher WAL end position captured when the base copy finishes (relevant for states s and r). After the base copy (i → d) completes, the sync worker records this LSN into srsublsn and then either marks the table synchronized (s) or lets the apply worker take over (r).

Worker status: pg_stat_subscription

Each subscription spawns one apply worker and may temporarily create one or more sync workers for initial table loads. For sync workers, relid points to the table being initialized; for the apply worker, relid is NULL.

The latest_end_lsn on the apply worker is the WAL end position of the last successful applied (or skipped) transaction from the publisher.

SELECT subid, subname, pid, relid, received_lsn,
       last_msg_send_time, last_msg_receipt_time,
       latest_end_lsn, latest_end_time
FROM pg_stat_subscription
ORDER BY subname, pid;

Example result:

 subid  | subname |  pid  |  relid  | received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |          latest_end_time          
--------+---------+-------+---------+--------------+-------------------------------+-------------------------------+----------------+-----------------------------------
 18515  | sub1    | 20112 |   18446 |              | 2023-06-01 10:12:40.121+00    | 2023-06-01 10:12:40.121+00    |                | 2023-06-01 10:12:40.121+00
 18515  | sub1    | 20001 |         | 0/4566B50    | 2023-06-01 10:12:35.987+00    | 2023-06-01 10:12:35.989+00    | 0/4566B50      | 2023-06-01 10:12:35.987+00

Origin progress: pg_replication_origin_status

Logical subscribers track the last applied remote position per origin in pg_replication_origin_status.

SELECT local_id, external_id, remote_lsn, local_lsn
FROM pg_replication_origin_status;

Example result:

 local_id | external_id | remote_lsn | local_lsn 
----------+-------------+------------+-----------
        1 | pg_18465    | 0/4540208  | 0/470FFD8

remote_lsn is the starting LSN of the last remote transaction record the subscriber applied from that origin. WAL generated later on the publisher for unrelated relations or system activity is not reflected here until needed by the publication and applied by the subscriber.

Publisher-side visibility

Logical replication slots: pg_replication_slots

The publisher’s logical slot for a subscription exposes the retention and acknowledgment horizon.

SELECT slot_name, plugin, slot_type, database,
       active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

Example result:

 slot_name | plugin   | slot_type | database | active | restart_lsn | confirmed_flush_lsn 
-----------+----------+-----------+----------+--------+-------------+---------------------
 sub1      | pgoutput | logical   | postgres | t      | 0/4540818   | 0/4540850

• restart_lsn: the oldest WAL location still required by the slot; WAL before this point will not be removed by checkpoints.
• confirmed_flush_lsn: the WAL location up to which the subscriber has confirmed receipt; older data can be discarded for this slot.

confirmed_flush_lsn usually matches the subscriber’s apply worker latest_end_lsn. restart_lsn can point to the beginning of the last record that spans confirmed_flush_lsn due to record boundaries.

If a subscriber cannot apply a particular change (for example, due to a lock on the target relation), confirmed_flush_lsn will stop advancing before the WAL record containing that change.

Illustrative WAL dump around a blocked INSERT on the publisher:

pg_waldump $PGDATA/pg_wal/000000010000000000000004 -s 0/045407A8 -n 5
rmgr: XLOG        len (rec/tot): 106/106, tx:      0, lsn: 0/045407A8, prev 0/04540770, desc: CHECKPOINT_ONLINE ...
rmgr: Standby     len (rec/tot):  50/ 50, tx:      0, lsn: 0/04540818, prev 0/045407A8, desc: RUNNING_XACTS ...
rmgr: Heap        len (rec/tot):  69/130, tx:    755, lsn: 0/04540850, prev 0/04540818, desc: INSERT off 2, blkref #0: rel 1663/13451/17988 blk 0 FPW
rmgr: Transaction len (rec/tot):  46/ 46, tx:    755, lsn: 0/045408D8, prev 0/04540850, desc: COMMIT ...
rmgr: Standby     len (rec/tot):  50/ 50, tx:      0, lsn: 0/04540908, prev 0/045408D8, desc: RUNNING_XACTS ...

Publisher view of the subscriber: pg_stat_replication

Logical replication apply and temporary sync workers showup in pg_stat_replication on the publisher. The apply worker’s application_name equals the subscription name. For sync workers, the application_name typically includes the subscription and a relid or relation hint.

write_lsn/flush_lsn/replay_lsn here should align with confirmed_flush_lsn for the logical slot.

SELECT pid, application_name, state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication
ORDER BY application_name, pid;

Example result:

  pid  |      application_name       |  state    | sent_lsn | write_lsn | flush_lsn | replay_lsn 
-------+-----------------------------+-----------+----------+-----------+-----------+------------
 19861 | sub1_18515_sync_18446       | startup   |          |           |           | 
 19500 | sub1                        | streaming | 0/4566B50| 0/4566B50 | 0/4566B50 | 0/4566B50

Initial sync worker flow

A sync worker performs table initialization using these steps:

1. Create a temporary logical replication slot dedicated to the sync worker.
2. Copy the table snapshot from the publisher to the subscriber.
3. Store the publisher LSN at the end of the copy into pg_subscription_rel.srsublsn.
4. Compare srsublsn with the apply worker’s latest_end_lsn:
   • If srsublsn <= latest_end_lsn, mark the table synchronized (state → s).
   • If srsublsn > latest_end_lsn, stream the table’s incremental changes via the temporary slot until the apply worker catches up, then mark the table synchronized (state → s).
5. Hand off ongoing change application to the apply worker and drop the temporary slot.

Determining when a subscription is caught up

A subscription should be considered in-sync when both conditions hold:

• Every table in pg_subscription_rel is in state s or r.
• The subscriber’s apply worker latest_end_lsn has reached the desired publisher LSN.

Practical checklist:

1. Wait for base copies to complete

SELECT COUNT(*) FILTER (WHERE srsubstate IN ('i','d')) AS still_copying,
       COUNT(*) FILTER (WHERE srsubstate IN ('s','r')) AS ready
FROM pg_subscription_rel;

2. Quiesce writes on the publisher for the published tables (for example, stop writers or lock the tables if operationally feasible).

3. Capture the publisher’s current WAL position

-- run on publisher
SELECT pg_current_wal_lsn() AS pub_lsn;

4. Observe the subscriber’s apply position

-- run on subscriber
SELECT subname, latest_end_lsn
FROM pg_stat_subscription
WHERE relid IS NULL;  -- apply worker

5. Compare LSNs

-- replace with values from steps 3 and 4
SELECT pg_lsn_cmp('0/4566B50', '0/4566B50') AS cmp;  -- 0 means equal, 1 means first > second

If the subscriber’s latest_end_lsn is greater than or equal to the captured publisher LSN, the data relevant to the publication has been applied.

Important nuances

• The following locations reflect progress only for relations already in state s or r:
  • pg_replication_slots.confirmed_flush_lsn
  • pg_stat_replication.{write_lsn,flush_lsn,replay_lsn}
  • pg_replication_origin_status.remote_lsn
• Tables still in i/d are handled by per-table sync workers using temporary slots. Those workers will pull incremental WAL until they meet or exceed the apply worker’s position, then switch the table to s and relinquish control.
• When assessing overall subscription progress, ensure all tables have finished initial sync (no i/d) before relying on apply worker or slot LSNs.