pg_stat_replication

A statistics view showing information about clients attached to the walsender

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 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

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

Categories

Replication, Statistics / monitoring view

See also

pg_stat_archiver, pg_stat_wal_receiver, pg_stat_replication_slots