Skip to content
← Catalog atlas / pg_subscription

SYSTEM CATALOG · System catalog

pg_subscription

PostgreSQL documentation ↗Source definition ↗

The catalog pg_subscription contains all existing logical replication subscriptions. For more information about logical replication see Chapter 29. Unlike most system catalogs, pg_subscription is shared across all databases of a cluster: there is only one copy of pg_subscription per cluster, not one per database. Access to the column subconninfo is revoked from normal users, because it could contain plain-text passwords.

First seen PG 10Last seen PG 19Columns 18
9.09.19.29.39.49.59.610111213141516171819β3

Checked column by column against a PostgreSQL 18.6 instance · Raw measurement record ↗

Columns PostgreSQL 18

pg_subscription
COLUMNTYPEDESCRIPTION
oid oidNOT NULL Row identifier
subdbid oidNOT NULL OID of the database that the subscription resides inReferences:pg_database.oid
subskiplsn pg_lsnNOT NULL Finish LSN of the transaction whose changes are to be skipped, if a valid LSN; otherwise 0/0.
subname nameNOT NULL Name of the subscription
subowner oidNOT NULL Owner of the subscriptionReferences:pg_authid.oid
subenabled booleanNOT NULLDocumented as:bool If true, the subscription is enabled and should be replicating
subbinary booleanNOT NULLDocumented as:bool If true, the subscription will request that the publisher send data in binary format
substream "char"NOT NULLDocumented as:char Controls how to handle the streaming of in-progress transactions: f = disallow streaming of in-progress transactions, t = spill the changes of in-progress transactions to disk and apply at once after the transaction is committed on the publisher and received by the subscriber, p = apply changes directly using a parallel apply worker if available (same as t if no worker is available)
subtwophasestate "char"NOT NULLDocumented as:char State codes for two-phase mode: d = disabled, p = pending enablement, e = enabled
subdisableonerr booleanNOT NULLDocumented as:bool If true, the subscription will be disabled if one of its workers detects an error
subpasswordrequired booleanNOT NULLDocumented as:bool If true, the subscription will be required to specify a password for authentication
subrunasowner booleanNOT NULLDocumented as:bool If true, the subscription will be run with the permissions of the subscription owner
subfailover booleanNOT NULLDocumented as:bool If true, the associated replication slots (i.e. the main slot and the table synchronization slots) in the upstream database are enabled to be synchronized to the standbys
subconninfo textNOT NULL Connection string to the upstream database
subslotname name Name of the replication slot in the upstream database (also used for the local replication origin name); null represents NONE
subsynccommit textNOT NULL The synchronous_commit setting for the subscription's workers to use
subpublications text[]NOT NULL Array of subscribed publication names. These reference publications defined in the upstream database. For more on publications see Section 29.1.
suborigin text The origin value must be either none or any. The default is any. If none, the subscription will request the publisher to only send changes that don't have an origin. If any, the publisher sends changes regardless of their origin.

Columns marked "implicit" are not included in SELECT *; some early statistics views are completed from same-version source, and the extra provenance is shown in the column description.

Common system columns 6

System attributes PostgreSQL provides, normally not expanded by SELECT *. Kept separately so they are not mixed in with ordinary columns.

ColumnsTypeattnum
tableoidoid-6
cmaxcid-5
xmaxxid-4
cmincid-3
xminxid-2
ctidtid-1

Column evolution matrix

Hover a cell for the column type
PresentAdded / type or attribute changeRemoved
COLUMN9.09.19.29.39.49.59.610111213141516171819
oid – – – – – – –
subdbid – – – – – – –
subname – – – – – – –
subowner – – – – – – –
subenabled – – – – – – –
subconninfo – – – – – – –
subslotname – – – – – – –
subsynccommit – – – – – – –
subpublications – – – – – – –
subbinary – – – – – – – – – – –
substream – – – – – – – – – – –
subskiplsn – – – – – – – – – – – –
subtwophasestate – – – – – – – – – – – –
subdisableonerr – – – – – – – – – – – –
subpasswordrequired – – – – – – – – – – – – –
subrunasowner – – – – – – – – – – – – –
suborigin – – – – – – – – – – – – –
subfailover – – – – – – – – – – – – – –
subretaindeadtuples – – – – – – – – – – – – – – – –
submaxretention – – – – – – – – – – – – – – – –
subretentionactive – – – – – – – – – – – – – – – –
subserver – – – – – – – – – – – – – – – –
subwalrcvtimeout – – – – – – – – – – – – – – – –

9.0 is the coverage baseline; a column already present in the baseline is not marked as added. The matrix aligns columns by name, so a rename appears as the old column removed and a new column added.

Evolution timeline

Changes between adjacent majors

PostgreSQL 19 ← 18

+ subretaindeadtuples+ submaxretention+ subretentionactive+ subserver+ subwalrcvtimeout~ subconninfo: not_null: true → false2 column description updates

PostgreSQL 18 ← 17

1 column description updates

PostgreSQL 17 ← 16

+ subfailover2 column description updatesCatalog description updated

PostgreSQL 16 ← 15

+ subpasswordrequired+ subrunasowner+ suborigin~ substream: boolean → "char"1 column description updates

PostgreSQL 15 ← 14

+ subskiplsn+ subtwophasestate+ subdisableonerr

PostgreSQL 14 ← 13

+ subbinary+ substream4 column description updatesCatalog description updated

PostgreSQL 13 ← 12

~ subslotname: not_null: true → false

PostgreSQL 12 ← 11

~ oid: implicit → ordinary2 column description updatesCatalog description updated

PostgreSQL 11 ← 10

Catalog description updated

PostgreSQL 10 ← 9.6Catalog added

+ oid+ subdbid+ subname+ subowner+ subenabled+ subconninfo+ subslotname+ subsynccommit+ subpublications

Compare any two versions

Pick two versions to see added, removed, and retyped columns. The interactive comparison needs JavaScript; the evolution timeline reads fine without it.