pg_stat_replication — PostgreSQL statistics view

The PostgreSQL pg_stat_replication statistics view: full column reference (names, types, descriptions), catalog relationships and version support.

The Sev-1 Admin
The Sev-1 Admin  ·  4 min read

Summary

The pg_stat_replication view will contain one row per WAL sender process, showing statistics about replication to that sender’s connected standby server. Only directly connected standbys are listed; no information is available about downstream standby servers.

(Description quoted from the official PostgreSQL documentation.)

Columns

The pg_stat_replication statistics view exposes the following columns (names, types and descriptions are taken verbatim from the PostgreSQL documentation):

  • pid integer
    Process ID of a WAL sender process
  • usesysid oid
    OID of the user logged into this WAL sender process
  • usename name
    Name of the user logged into this WAL sender process
  • application_name text
    Name of the application that is connected to this WAL sender
  • client_addr inet
    IP address of the client connected to this WAL sender. If this field is null, it indicates that the client is connected via a Unix socket on the server machine.
  • client_hostname text
    Host name of the connected client, as reported by a reverse DNS lookup of client_addr. This field will only be non-null for IP connections, and only when log_hostname is enabled.
  • client_port integer
    TCP port number that the client is using for communication with this WAL sender, or -1 if a Unix socket is used
  • backend_start timestamp with time zone
    Time when this process was started, i.e., when the client connected to this WAL sender
  • backend_xmin xid
    This standby’s xmin horizon reported by hot_standby_feedback. This field will be null if a replication slot is used (in this case, the standby’s xmin is shown in pg_replication_slots) or if hot_standby_feedback is disabled.
  • state text
    Current WAL sender state. Possible values are: startup: This WAL sender is starting up. catchup: This WAL sender’s connected standby is catching up with the primary. streaming: This WAL sender is streaming changes after its connected standby server has caught up with the primary. backup: This WAL sender is sending a backup. stopping: This WAL sender is stopping.
  • sent_lsn pg_lsn
    Last write-ahead log location sent on this connection
  • write_lsn pg_lsn
    Last write-ahead log location written to disk by this standby server
  • flush_lsn pg_lsn
    Last write-ahead log location flushed to disk by this standby server
  • replay_lsn pg_lsn
    Last write-ahead log location replayed into the database on this standby server
  • write_lag interval
    Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it (but not yet flushed it or applied it). This can be used to gauge the delay that synchronous_commit level remote_write incurred while committing if this server was configured as a synchronous standby.
  • flush_lag interval
    Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (but not yet applied it). This can be used to gauge the delay that synchronous_commit level on incurred while committing if this server was configured as a synchronous standby.
  • replay_lag interval
    Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it. This can be used to gauge the delay that synchronous_commit level remote_apply incurred while committing if this server was configured as a synchronous standby.
  • sync_priority integer
    Priority of this standby server for being chosen as the synchronous standby in a priority-based synchronous replication. This has no effect in a quorum-based synchronous replication.
  • sync_state text
    Synchronous state of this standby server. Possible values are: async: This standby server is asynchronous. potential: This standby server is now asynchronous, but can potentially become synchronous if one of current synchronous ones fails. sync: This standby server is synchronous. quorum: This standby server is considered as a candidate for quorum standbys.
  • reply_time timestamp with time zone
    Send time of last reply message received from standby server

Version applicability

Present in PostgreSQL 17, 18, 19 (verified against each release’s documentation). This is a long-standing system object that also exists in earlier PostgreSQL releases.

Related & references

Reference: PostgreSQL documentation — pg_stat_replication.