pg_replication_slots 是一个列出所有复制槽的系统目录视图。
pg_replication_slots 在 PostgreSQL 9.4 中添加。
用法
底层函数 pg_replication_slots 直接读取磁盘上的复制槽结构，这意味着此视图的内容始终可见，而不管事务状态如何，并且可能在事务中发生更改。因此，在将其与其他关系（如 pg_stat_activity）结合使用时应注意，因为这些内容可能对当前事务不可见。
按 PostgreSQL 版本定义
pg_replication_slots (PostgreSQL 17)
View "pg_catalog.pg_replication_slots"
Column | Type | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+---------
slot_name | name | | |
plugin | name | | |
slot_type | text | | |
datoid | oid | | |
database | name | | |
temporary | boolean | | |
active | boolean | | |
active_pid | integer | | |
xmin | xid | | |
catalog_xmin | xid | | |
restart_lsn | pg_lsn | | |
confirmed_flush_lsn | pg_lsn | | |
wal_status | text | | |
safe_wal_size | bigint | | |
two_phase | boolean | | |
inactive_since | timestamp with time zone | | |
conflicting | boolean | | |
invalidation_reason | text | | |
failover | boolean | | |
synced | boolean | | |
pg_replication_slots (PostgreSQL 16)
View "pg_catalog.pg_replication_slots"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
slot_name | name | | |
plugin | name | | |
slot_type | text | | |
datoid | oid | | |
database | name | | |
temporary | boolean | | |
active | boolean | | |
active_pid | integer | | |
xmin | xid | | |
catalog_xmin | xid | | |
restart_lsn | pg_lsn | | |
confirmed_flush_lsn | pg_lsn | | |
wal_status | text | | |
safe_wal_size | bigint | | |
two_phase | boolean | | |
conflicting | boolean | | |
pg_replication_slots (PostgreSQL 15)
View "pg_catalog.pg_replication_slots"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
slot_name | name | | |
plugin | name | | |
slot_type | text | | |
datoid | oid | | |
database | name | | |
temporary | boolean | | |
active | boolean | | |
active_pid | integer | | |
xmin | xid | | |
catalog_xmin | xid | | |
restart_lsn | pg_lsn | | |
confirmed_flush_lsn | pg_lsn | | |
wal_status | text | | |
safe_wal_size | bigint | | |
two_phase | boolean | | |
pg_replication_slots (PostgreSQL 14)
View "pg_catalog.pg_replication_slots"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
slot_name | name | | |
plugin | name | | |
slot_type | text | | |
datoid | oid | | |
database | name | | |
temporary | boolean | | |
active | boolean | | |
active_pid | integer | | |
xmin | xid | | |
catalog_xmin | xid | | |
restart_lsn | pg_lsn | | |
confirmed_flush_lsn | pg_lsn | | |
wal_status | text | | |
safe_wal_size | bigint | | |
two_phase | boolean | | |
pg_replication_slots (PostgreSQL 13)
View "pg_catalog.pg_replication_slots"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
slot_name | name | | |
plugin | name | | |
slot_type | text | | |
datoid | oid | | |
database | name | | |
temporary | boolean | | |
active | boolean | | |
active_pid | integer | | |
xmin | xid | | |
catalog_xmin | xid | | |
restart_lsn | pg_lsn | | |
confirmed_flush_lsn | pg_lsn | | |
wal_status | text | | |
safe_wal_size | bigint | | |
pg_replication_slots (PostgreSQL 12)
View "pg_catalog.pg_replication_slots"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
slot_name | name | | |
plugin | name | | |
slot_type | text | | |
datoid | oid | | |
database | name | | |
temporary | boolean | | |
active | boolean | | |
active_pid | integer | | |
xmin | xid | | |
catalog_xmin | xid | | |
restart_lsn | pg_lsn | | |
confirmed_flush_lsn | pg_lsn | | |
pg_replication_slots (PostgreSQL 11)
View "pg_catalog.pg_replication_slots"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
slot_name | name | | |
plugin | name | | |
slot_type | text | | |
datoid | oid | | |
database | name | | |
temporary | boolean | | |
active | boolean | | |
active_pid | integer | | |
xmin | xid | | |
catalog_xmin | xid | | |
restart_lsn | pg_lsn | | |
confirmed_flush_lsn | pg_lsn | | |
pg_replication_slots (PostgreSQL 10)
View "pg_catalog.pg_replication_slots"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
slot_name | name | | |
plugin | name | | |
slot_type | text | | |
datoid | oid | | |
database | name | | |
temporary | boolean | | |
active | boolean | | |
active_pid | integer | | |
xmin | xid | | |
catalog_xmin | xid | | |
restart_lsn | pg_lsn | | |
confirmed_flush_lsn | pg_lsn | | |
pg_replication_slots (PostgreSQL 9.6)
View "pg_catalog.pg_replication_slots"
Column | Type | Modifiers
---------------------+---------+-----------
slot_name | name |
plugin | name |
slot_type | text |
datoid | oid |
database | name |
active | boolean |
active_pid | integer |
xmin | xid |
catalog_xmin | xid |
restart_lsn | pg_lsn |
confirmed_flush_lsn | pg_lsn |
pg_replication_slots (PostgreSQL 9.5)
View "pg_catalog.pg_replication_slots"
Column | Type | Modifiers
--------------+---------+-----------
slot_name | name |
plugin | name |
slot_type | text |
datoid | oid |
database | name |
active | boolean |
active_pid | integer |
xmin | xid |
catalog_xmin | xid |
restart_lsn | pg_lsn |
pg_replication_slots (PostgreSQL 9.4)
View "pg_catalog.pg_replication_slots"
Column | Type | Modifiers
--------------+---------+-----------
slot_name | name |
plugin | name |
slot_type | text |
datoid | oid |
database | name |
active | boolean |
xmin | xid |
catalog_xmin | xid |
restart_lsn | pg_lsn |
更改历史记录
- PostgreSQL 17
- PostgreSQL 16
- 添加列
conflicting(提交 be87200e)
- 添加列
- PostgreSQL 14
- 添加列
two_phase(提交 19890a06)
- 添加列
- PostgreSQL 13
- PostgreSQL 10
- 添加列
temporary(提交 a924c327)
- 添加列
- PostgreSQL 9.6
- 添加列
confirmed_flush_lsn(提交 3f811c2d)
- 添加列
- PostgreSQL 9.5
- 添加列
active_pid(提交 d811c037)
- 添加列
- PostgreSQL 9.4
- 添加 (提交 858ec118)
示例
当单个物理复制槽正在使用时，pg_replication_slots 的示例内容（截至 PostgreSQL 13）
postgres=# SELECT * FROM pg_replication_slots\gx -[ RECORD 1 ]-------+-------------- slot_name | node2 plugin | slot_type | physical datoid | database | temporary | f active | t active_pid | 1597000 xmin | catalog_xmin | restart_lsn | 0/3002470 confirmed_flush_lsn | wal_status | reserved safe_wal_size |
参考文献
- PostgreSQL 文档： pg_replication_slots