pg_subscription
A system table containing logical replication subscriptions
pg_subscription is a system catalogue table containing all existing logical replication subscriptions.
pg_subscription was added in PostgreSQL 10.
This table is shared across all databases in a cluster. Tables from individual databases are associated with a subscription via the pg_subscription_rel table.
psql commands
\dRs[+]- lists available subscriptions
Definition by PostgreSQL version
pg_subscription (PostgreSQL 15)
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 |
subtwophasestate | "char" | | 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: 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"
Documentation: pg_subscription
Change history
- PostgreSQL 15
- column
subtwophasestateadded (commit a8fd13ca)
- column
- PostgreSQL 14
- PostgreSQL 10
- added (commit 665d1fad)
Examples
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)
References
- PostgreSQL documentation: pg_subscription