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:

1
select
2
  slot_name,
3
  active,
4
  wal_status,
5
  pg_size_pretty(safe_wal_size) as wal_retention_remaining,
6
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
7
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.

1
select
2
  pid,
3
  application_name,
4
  state,
5
  sent_lsn,
6
  write_lsn,
7
  flush_lsn,
8
  replay_lsn,
9
  sync_state
10
from pg_stat_replication;

WAL size#

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

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

Check the LSN#

1
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

1
select
2
  sub.subname as subscription_name,
3
  srel.srrelid::regclass as table_name,
4
  srel.srsubstate as replication_state,
5
  case srel.srsubstate
6
    when 'i' then 'Initializing'
7
    when 'd' then 'Copying data'
8
    when 'f' then 'Finished copying'
9
    when 's' then 'Synchronized'
10
    when 'r' then 'Ready'
11
    else 'Unknown'
12
  end as state_description,
13
  srel.srsublsn as synchronization_lsn
14
from pg_subscription sub
15
join pg_subscription_rel srel on sub.oid = srel.srsubid
16
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:

1
select
2
  subname,
3
  pid,
4
  received_lsn,
5
  latest_end_lsn,
6
  latest_end_time
7
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.