Contents

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

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)

Categories

Cluster-wide table, Replication, System catalogue

See also

pg_subscription_rel, pg_subscription_tables, pg_publication