# Manual replication monitoring

Track replication health and performance.

Monitor replication lag and status for manual replication setups.

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](/docs/guides/observability/reports).
- **Database**: Run the queries in [Primary](#primary) on the source and those in [Subscriber](#subscriber) on the destination Postgres database.
- **Metrics**: Use your project's [Prometheus endpoint](/docs/guides/observability/metrics) 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`:

```sql
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.

```sql
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:

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

### Check the LSN

```sql
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`](https://www.postgresql.org/docs/15/catalog-pg-subscription-rel.html) 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

```sql
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`](https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION) to inspect the WAL positions reported by subscription workers:

```sql
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.