pg_stat_replication
pg_stat_replication
is a statistics view showing information about the status of clients attached to the walsender.
pg_stat_replication
was added in PostgreSQL 9.1.
Definition by PostgreSQL version
pg_stat_replication (PostgreSQL 15)
View "pg_catalog.pg_stat_replication" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | backend_xmin | xid | | | state | text | | | sent_lsn | pg_lsn | | | write_lsn | pg_lsn | | | flush_lsn | pg_lsn | | | replay_lsn | pg_lsn | | | write_lag | interval | | | flush_lag | interval | | | replay_lag | interval | | | sync_priority | integer | | | sync_state | text | | | reply_time | timestamp with time zone | | |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 14)
View "pg_catalog.pg_stat_replication" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | backend_xmin | xid | | | state | text | | | sent_lsn | pg_lsn | | | write_lsn | pg_lsn | | | flush_lsn | pg_lsn | | | replay_lsn | pg_lsn | | | write_lag | interval | | | flush_lag | interval | | | replay_lag | interval | | | sync_priority | integer | | | sync_state | text | | | reply_time | timestamp with time zone | | |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 13)
View "pg_catalog.pg_stat_replication" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | backend_xmin | xid | | | state | text | | | sent_lsn | pg_lsn | | | write_lsn | pg_lsn | | | flush_lsn | pg_lsn | | | replay_lsn | pg_lsn | | | write_lag | interval | | | flush_lag | interval | | | replay_lag | interval | | | sync_priority | integer | | | sync_state | text | | | reply_time | timestamp with time zone | | |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 12)
View "pg_catalog.pg_stat_replication" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | backend_xmin | xid | | | state | text | | | sent_lsn | pg_lsn | | | write_lsn | pg_lsn | | | flush_lsn | pg_lsn | | | replay_lsn | pg_lsn | | | write_lag | interval | | | flush_lag | interval | | | replay_lag | interval | | | sync_priority | integer | | | sync_state | text | | | reply_time | timestamp with time zone | | |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 11)
View "pg_catalog.pg_stat_replication" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | backend_xmin | xid | | | state | text | | | sent_lsn | pg_lsn | | | write_lsn | pg_lsn | | | flush_lsn | pg_lsn | | | replay_lsn | pg_lsn | | | write_lag | interval | | | flush_lag | interval | | | replay_lag | interval | | | sync_priority | integer | | | sync_state | text | | |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 10)
View "pg_catalog.pg_stat_replication" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | backend_xmin | xid | | | state | text | | | sent_lsn | pg_lsn | | | write_lsn | pg_lsn | | | flush_lsn | pg_lsn | | | replay_lsn | pg_lsn | | | write_lag | interval | | | flush_lag | interval | | | replay_lag | interval | | | sync_priority | integer | | | sync_state | text | | |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 9.6)
View "pg_catalog.pg_stat_replication" Column | Type | Modifiers ------------------+--------------------------+----------- pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start | timestamp with time zone | backend_xmin | xid | state | text | sent_location | pg_lsn | write_location | pg_lsn | flush_location | pg_lsn | replay_location | pg_lsn | sync_priority | integer | sync_state | text |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 9.5)
View "pg_catalog.pg_stat_replication" Column | Type | Modifiers ------------------+--------------------------+----------- pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start | timestamp with time zone | backend_xmin | xid | state | text | sent_location | pg_lsn | write_location | pg_lsn | flush_location | pg_lsn | replay_location | pg_lsn | sync_priority | integer | sync_state | text |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 9.4)
View "pg_catalog.pg_stat_replication" Column | Type | Modifiers ------------------+--------------------------+----------- pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start | timestamp with time zone | backend_xmin | xid | state | text | sent_location | pg_lsn | write_location | pg_lsn | flush_location | pg_lsn | replay_location | pg_lsn | sync_priority | integer | sync_state | text |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 9.3)
View "pg_catalog.pg_stat_replication" Column | Type | Modifiers ------------------+--------------------------+----------- pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start | timestamp with time zone | state | text | sent_location | text | write_location | text | flush_location | text | replay_location | text | sync_priority | integer | sync_state | text |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 9.2)
View "pg_catalog.pg_stat_replication" Column | Type | Modifiers ------------------+--------------------------+----------- pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start | timestamp with time zone | state | text | sent_location | text | write_location | text | flush_location | text | replay_location | text | sync_priority | integer | sync_state | text |
Documentation: pg_stat_replication
pg_stat_replication (PostgreSQL 9.1)
View "pg_catalog.pg_stat_replication" Column | Type | Modifiers ------------------+--------------------------+----------- procpid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start | timestamp with time zone | state | text | sent_location | text | write_location | text | flush_location | text | replay_location | text | sync_priority | integer | sync_state | text |
Documentation: pg_stat_replication
Change history
- PostgreSQL 13
- following columns added (commit 9290ad19):
spill_txns
spill_count
spill_bytes
- following columns added (commit 9290ad19):
- PostgreSQL 12
- column
reply_time
added (commit 7fee252f)
- column
- PostgreSQL 10
- columns
write_lag
,flush_lag
, andreplay_lag
added (commit 6912acc0) - following columns renamed as part of the xlog/location to lsn renaming (commit d10c626d):
sent_location
tosent_lsn
write_location
towrite_lsn
flush_location
toflush_lsn
replay_location
toreplay_lsn
- columns
- PostgreSQL 9.5
- column
sent
will now be NULL, not zero, if it has no valid value (commit 28c36678)
- column
- PostgreSQL 9.4
- PostgreSQL 9.2
- column
procpid
renamed topid
(commit 4f42b546)
- column
- PostgreSQL 9.1
- added (commit a755ea33)
Examples
Example output with one streaming replication standby attached:
postgres=# SELECT * FROM pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 3289529 usesysid | 16384 usename | repuser application_name | node2 client_addr | 192.168.1.4 client_hostname | client_port | 47268 backend_start | 2020-12-22 16:17:45.979908+01 backend_xmin | state | streaming sent_lsn | 0/30005C8 write_lsn | 0/30005C8 flush_lsn | 0/30005C8 replay_lsn | 0/30005C8 write_lag | 00:00:00.00002 flush_lag | 00:00:00.000026 replay_lag | 00:00:00.000094 sync_priority | 0 sync_state | async reply_time | 2020-12-22 16:17:46.119517+01
References
- PostgreSQL documentation: pg_stat_replication View