• Browse categories
• PostgreSQL releases
  • PostgreSQL 14 (dev)
  • PostgreSQL 13 (latest)
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10
  • PostgreSQL 9.6
  • PostgreSQL 9.5
  • PostgreSQL 9.4 (EOL)

Useful articles

Useful links

pg_stat_replication

pg_stat_replication is a system catalogue view showing information about the status of clients attached to the walsender.

pg_stat_replication was introduced in PostgreSQL 9.1.

Definition by PostgreSQL version

                    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 |           |          |
 spill_txns       | bigint                   |           |          |
 spill_count      | bigint                   |           |          |
 spill_bytes      | bigint                   |           |          |
    

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

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

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

          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                     |
    

          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                     |
    

          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                     |
    

          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                     |
    

          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                     |
    

          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                     |
    

Change history

• PostgreSQL 13
  • following columns added (commit 9290ad19):
    • spill_txns
    • spill_count
    • spill_bytes
• PostgreSQL 12
  • column reply_time added (commit 7fee252f)
• PostgreSQL 10
  • columns write_lag, flush_lag, and replay_lag added (commit 6912acc0)
  • following columns renamed as part of the xlog/location to lsn renaming (commit d10c626d):
    
    • sent_location to sent_lsn
    • write_location to write_lsn
    • flush_location to flush_lsn
    • replay_location to replay_lsn
• PostgreSQL 9.5
  • column sent will now be NULL, not zero, if it has no valid value (commit 28c36678)
• PostgreSQL 9.4
  • column backend_xmin added (commit dd1a3bcc)
  • data type of following columns changed from text to pg_lsn (commit 6615e774):
    • sent_location
    • write_location
    • flush_location
    • replay_location
• PostgreSQL 9.2
  • column procpid renamed to pid (commit 4f42b546)
• PostgreSQL 9.1
  • added (commit a755ea33)

References

Categories

See also