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

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

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

Version charts

Useful overviews

Useful links

Contents

pg_stat_replication

Definition by PostgreSQL version

pg_stat_replication (PostgreSQL 16)

pg_stat_replication (PostgreSQL 15)

pg_stat_replication (PostgreSQL 14)

pg_stat_replication (PostgreSQL 13)

pg_stat_replication (PostgreSQL 12)

pg_stat_replication (PostgreSQL 11)

pg_stat_replication (PostgreSQL 10)

pg_stat_replication (PostgreSQL 9.6)

pg_stat_replication (PostgreSQL 9.5)

pg_stat_replication (PostgreSQL 9.4)

pg_stat_replication (PostgreSQL 9.3)

pg_stat_replication (PostgreSQL 9.2)

pg_stat_replication (PostgreSQL 9.1)

Change history

Examples

References

Categories

See also