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 | 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"
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
- 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
Categories
See also
pg_subscription_rel, pg_subscription_tables, pg_publication