pg_subscription 是一个 系统目录 表，其中包含所有现有的逻辑复制订阅。
pg_subscription 在 PostgreSQL 10 中被添加。
此表在集群的所有数据库之间共享。来自单个数据库的表通过 pg_subscription_rel 表与订阅关联。
psql 命令
\dRs[+]- 列出可用的订阅
按 PostgreSQL 版本定义
pg_subscription (PostgreSQL 19)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
oid | oid | | not null |
subdbid | oid | | not null |
subskiplsn | pg_lsn | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subbinary | boolean | | not null |
substream | "char" | | not null |
subtwophasestate | "char" | | not null |
subdisableonerr | boolean | | not null |
subpasswordrequired | boolean | | not null |
subrunasowner | boolean | | not null |
subfailover | boolean | | not null |
subconninfo | text | C | not null |
subslotname | name | | |
subsynccommit | text | C | not null |
subpublications | text[] | C | not null |
suborigin | text | C | |
Indexes:
"pg_subscription_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE CONSTRAINT, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 18)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
oid | oid | | not null |
subdbid | oid | | not null |
subskiplsn | pg_lsn | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subbinary | boolean | | not null |
substream | "char" | | not null |
subtwophasestate | "char" | | not null |
subdisableonerr | boolean | | not null |
subpasswordrequired | boolean | | not null |
subrunasowner | boolean | | not null |
subfailover | boolean | | not null |
subconninfo | text | C | not null |
subslotname | name | | |
subsynccommit | text | C | not null |
subpublications | text[] | C | not null |
suborigin | text | C | |
Indexes:
"pg_subscription_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE CONSTRAINT, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 17)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
oid | oid | | not null |
subdbid | oid | | not null |
subskiplsn | pg_lsn | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subbinary | boolean | | not null |
substream | "char" | | not null |
subtwophasestate | "char" | | not null |
subdisableonerr | boolean | | not null |
subpasswordrequired | boolean | | not null |
subrunasowner | boolean | | not null |
subfailover | boolean | | not null |
subconninfo | text | C | not null |
subslotname | name | | |
subsynccommit | text | C | not null |
subpublications | text[] | C | not null |
suborigin | text | C | |
Indexes:
"pg_subscription_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE CONSTRAINT, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 16)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
oid | oid | | not null |
subdbid | oid | | not null |
subskiplsn | pg_lsn | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subbinary | boolean | | not null |
substream | "char" | | not null |
subtwophasestate | "char" | | not null |
subdisableonerr | boolean | | not null |
subpasswordrequired | boolean | | not null |
subrunasowner | boolean | | not null |
subconninfo | text | C | not null |
subslotname | name | | |
subsynccommit | text | C | not null |
subpublications | text[] | C | not null |
suborigin | text | C | |
Indexes:
"pg_subscription_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE CONSTRAINT, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 15)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
oid | oid | | not null |
subdbid | oid | | not null |
subskiplsn | pg_lsn | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subbinary | boolean | | not null |
substream | boolean | | not null |
subtwophasestate | "char" | | not null |
subdisableonerr | boolean | | not null |
subconninfo | text | C | not null |
subslotname | name | | |
subsynccommit | text | C | not null |
subpublications | text[] | C | not null |
Indexes:
"pg_subscription_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE CONSTRAINT, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 14)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
oid | oid | | not null |
subdbid | oid | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subbinary | boolean | | not null |
substream | boolean | | not null |
subconninfo | text | C | not null |
subslotname | name | | |
subsynccommit | text | C | not null |
subpublications | text[] | C | not null |
Indexes:
"pg_subscription_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE CONSTRAINT, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 13)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
oid | oid | | not null |
subdbid | oid | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subconninfo | text | C | not null |
subslotname | name | | |
subsynccommit | text | C | not null |
subpublications | text[] | C | not null |
Indexes:
"pg_subscription_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 12)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
oid | oid | | not null |
subdbid | oid | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subconninfo | text | C | not null |
subslotname | name | | not null |
subsynccommit | text | C | not null |
subpublications | text[] | C | not null |
Indexes:
"pg_subscription_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 11)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
subdbid | oid | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subconninfo | text | | not null |
subslotname | name | | not null |
subsynccommit | text | | not null |
subpublications | text[] | | not null |
Indexes:
"pg_subscription_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
pg_subscription (PostgreSQL 10)
Table "pg_catalog.pg_subscription"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
subdbid | oid | | not null |
subname | name | | not null |
subowner | oid | | not null |
subenabled | boolean | | not null |
subconninfo | text | | not null |
subslotname | name | | not null |
subsynccommit | text | | not null |
subpublications | text[] | | not null |
Indexes:
"pg_subscription_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_subscription_subname_index" UNIQUE, btree (subdbid, subname), tablespace "pg_global"
Tablespace: "pg_global"
文档: pg_subscription
变更历史
- PostgreSQL 19
- 添加了
submaxretention和subretentionactive列 (提交 a850be2f)
- 添加了
- PostgreSQL 17
- 添加了
subfailover列 (提交 776621a5)
- 添加了
- PostgreSQL 16
- PostgreSQL 15
- PostgreSQL 14
- PostgreSQL 10
- 添加于 (提交 665d1fad)
示例
subtestdb=# CREATE SUBSCRIPTION test_subscription CONNECTION 'host=node1 dbname=testdb user=produser' PUBLICATION test_publication; NOTICE: created replication slot "test_subscription" on publisher CREATE SUBSCRIPTION subtestdb=# SELECT * FROM pg_subscription; -[ RECORD 1 ]---+---------------------------------------- oid | 16468 subdbid | 13942 subname | test_subscription subowner | 10 subenabled | t subbinary | f substream | f subconninfo | host=node1 dbname=testdb user=produser subslotname | test_subscription subsynccommit | off subpublications | {test_publication} subtestdb=# \dRs List of subscriptions Name | Owner | Enabled | Publication -------------------+----------+---------+-------------------- test_subscription | postgres | t | {test_publication} (1 row) subtestdb=# \dRs+ List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Synchronous commit | Conninfo -------------------+----------+---------+--------------------+--------+-----------+--------------------+---------------------------------------- test_subscription | postgres | t | {test_publication} | f | f | off | host=node1 dbname=testdb user=produser (1 row)
参考资料
- PostgreSQL 文档: pg_subscription