Manual replication monitoring

Use source-side slot metrics to monitor any logical replication consumer. If the destination is another Postgres database, also check its subscription state.

• Dashboard: View replication lag in Reports.
• Database: Run the queries in Primary on the source and those in Subscriber on the destination Postgres database.
• Metrics: Use your project's Prometheus endpoint to track replication slot lag over time.

Primary

Run these queries on the source database.

Replication slot status

A replication slot has separate connection and WAL availability fields:

• active is true when a consumer is connected to the slot.
• wal_status is reserved or extended while required WAL is available, unreserved when the slot is at risk of losing required WAL, and lost after required WAL has been removed.
• safe_wal_size estimates how many more bytes can be written before the slot is at risk. It is NULL when max_slot_wal_keep_size is unlimited or the slot is already lost.

Check these fields and the amount of WAL retained from the slot's restart_lsn:

select
  slot_name,
  active,
  wal_status,
  pg_size_pretty(safe_wal_size) as wal_retention_remaining,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
from pg_replication_slots;

An inactive slot can still retain WAL. Investigate a slot whose retained WAL keeps growing, whose remaining retention keeps shrinking, or whose wal_status becomes unreserved. A lost slot can't continue from its previous position and usually requires a new initial sync.

Replication status and lag

The pg_stat_replication table shows the status of any replicas connected to the primary database.

select
  pid,
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  sync_state
from pg_stat_replication;

WAL size

The WAL size can be checked using the pg_ls_waldir() function:

select pg_size_pretty(sum(size)) as wal_directory_size
from pg_ls_waldir();

Check the LSN

select pg_current_wal_lsn();

Subscriber

Run these queries on the destination only when it is a Postgres logical subscriber. For other destination systems, use their monitoring tools.

Subscription status

The pg_subscription table shows the status of any subscriptions on a replica and the pg_subscription_rel table shows the status of each table within a subscription.

The srsubstate column in pg_subscription_rel records each table's synchronization state:

• i: Initializing
• d: Copying existing data
• f: Finished copying; synchronization is not yet complete
• s: Synchronized
• r: Ready for normal replication

select
  sub.subname as subscription_name,
  srel.srrelid::regclass as table_name,
  srel.srsubstate as replication_state,
  case srel.srsubstate
    when 'i' then 'Initializing'
    when 'd' then 'Copying data'
    when 'f' then 'Finished copying'
    when 's' then 'Synchronized'
    when 'r' then 'Ready'
    else 'Unknown'
  end as state_description,
  srel.srsublsn as synchronization_lsn
from pg_subscription sub
join pg_subscription_rel srel on sub.oid = srel.srsubid
order by table_name;

synchronization_lsn coordinates the table's initial synchronization. It is not a continuously updated replication checkpoint.

Check the LSN

Use pg_stat_subscription to inspect the WAL positions reported by subscription workers:

select
  subname,
  pid,
  received_lsn,
  latest_end_lsn,
  latest_end_time
from pg_stat_subscription;

A missing pid means that worker is not running. received_lsn shows the last WAL position received, while latest_end_lsn and latest_end_time show the latest position and time reported to the publisher. These fields do not by themselves confirm that all tables have finished initial synchronization.