Contents

pg_publication

A system table storing all publications created in the database

pg_publication is a system catalogue table storing all publications created in the database.

pg_publication was added in PostgreSQL 10.

Individual tables are associated with a scription via the pg_publication_rel table.

psql commands

  • \dRp[+] - lists available publications

Definition by PostgreSQL version

pg_publication (PostgreSQL 15)

            Table "pg_catalog.pg_publication"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 oid          | oid     |           | not null | 
 pubname      | name    |           | not null | 
 pubowner     | oid     |           | not null | 
 puballtables | boolean |           | not null | 
 pubinsert    | boolean |           | not null | 
 pubupdate    | boolean |           | not null | 
 pubdelete    | boolean |           | not null | 
 pubtruncate  | boolean |           | not null | 
 pubviaroot   | boolean |           | not null | 
Indexes:
    "pg_publication_oid_index" PRIMARY KEY, btree (oid)
    "pg_publication_pubname_index" UNIQUE CONSTRAINT, btree (pubname)

Documentation: pg_publication

pg_publication (PostgreSQL 14)

            Table "pg_catalog.pg_publication"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 oid          | oid     |           | not null | 
 pubname      | name    |           | not null | 
 pubowner     | oid     |           | not null | 
 puballtables | boolean |           | not null | 
 pubinsert    | boolean |           | not null | 
 pubupdate    | boolean |           | not null | 
 pubdelete    | boolean |           | not null | 
 pubtruncate  | boolean |           | not null | 
 pubviaroot   | boolean |           | not null | 
Indexes:
    "pg_publication_oid_index" PRIMARY KEY, btree (oid)
    "pg_publication_pubname_index" UNIQUE CONSTRAINT, btree (pubname)

Documentation: pg_publication

pg_publication (PostgreSQL 13)

            Table "pg_catalog.pg_publication"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 oid          | oid     |           | not null | 
 pubname      | name    |           | not null | 
 pubowner     | oid     |           | not null | 
 puballtables | boolean |           | not null | 
 pubinsert    | boolean |           | not null | 
 pubupdate    | boolean |           | not null | 
 pubdelete    | boolean |           | not null | 
 pubtruncate  | boolean |           | not null | 
 pubviaroot   | boolean |           | not null | 
Indexes:
    "pg_publication_oid_index" UNIQUE, btree (oid)
    "pg_publication_pubname_index" UNIQUE, btree (pubname)

Documentation: pg_publication

pg_publication (PostgreSQL 12)

            Table "pg_catalog.pg_publication"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 oid          | oid     |           | not null | 
 pubname      | name    |           | not null | 
 pubowner     | oid     |           | not null | 
 puballtables | boolean |           | not null | 
 pubinsert    | boolean |           | not null | 
 pubupdate    | boolean |           | not null | 
 pubdelete    | boolean |           | not null | 
 pubtruncate  | boolean |           | not null | 
Indexes:
    "pg_publication_oid_index" UNIQUE, btree (oid)
    "pg_publication_pubname_index" UNIQUE, btree (pubname)

Documentation: pg_publication

pg_publication (PostgreSQL 11)

            Table "pg_catalog.pg_publication"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 pubname      | name    |           | not null | 
 pubowner     | oid     |           | not null | 
 puballtables | boolean |           | not null | 
 pubinsert    | boolean |           | not null | 
 pubupdate    | boolean |           | not null | 
 pubdelete    | boolean |           | not null | 
 pubtruncate  | boolean |           | not null | 
Indexes:
    "pg_publication_oid_index" UNIQUE, btree (oid)
    "pg_publication_pubname_index" UNIQUE, btree (pubname)

Documentation: pg_publication

pg_publication (PostgreSQL 10)

            Table "pg_catalog.pg_publication"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 pubname      | name    |           | not null | 
 pubowner     | oid     |           | not null | 
 puballtables | boolean |           | not null | 
 pubinsert    | boolean |           | not null | 
 pubupdate    | boolean |           | not null | 
 pubdelete    | boolean |           | not null | 
Indexes:
    "pg_publication_oid_index" UNIQUE, btree (oid)
    "pg_publication_pubname_index" UNIQUE, btree (pubname)

Documentation: pg_publication

Change history

Examples

pubtestdb=# CREATE SUBSCRIPTION test_subscription
  CONNECTION 'host=node1 dbname=testdb user=produser'
  PUBLICATION test_publication;
NOTICE:  created replication slot "test_subscription" on publisher
CREATE SUBSCRIPTION

pubtestdb=# SELECT * FROM pg_publication\gx
-[ RECORD 1 ]+-----------------
oid          | 91147
pubname      | test_publication
pubowner     | 10
puballtables | f
pubinsert    | t
pubupdate    | t
pubdelete    | t
pubtruncate  | t
pubviaroot   | f

pubtestdb=# \dRp
                                     List of publications
       Name       |  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
------------------+----------+------------+---------+---------+---------+-----------+----------
 test_publication | postgres | f          | t       | t       | t       | t         | f
(1 row)

pubtestdb=# \dRp+
                        Publication test_publication
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.object"

Categories

Replication, System catalogue

See also

pg_publication_rel, ALTER PUBLICATION, CREATE PUBLICATION, DROP PUBLICATION