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
\dSp[+]- lists available subscriptions
Definition by PostgreSQL version
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
This table has not been modified since it was added in PostgreSQL 10.
- 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