pg_subscription — PostgreSQL system catalog

Summary

The catalog pg_subscription contains all existing logical replication subscriptions. For more information about logical replication see logical_replication.

(Description quoted from the official PostgreSQL documentation.)

Columns

The pg_subscription system catalog exposes the following columns (names, types and descriptions are taken verbatim from the PostgreSQL documentation):

oid oid
Row identifier
subdbid oid references pg_database.oid
OID of the database that the subscription resides in
subskiplsn pg_lsn
Finish LSN of the transaction whose changes are to be skipped, if a valid LSN; otherwise 0/0000000.
subname name
Name of the subscription
subowner oid references pg_authid.oid
Owner of the subscription
subenabled bool
If true, the subscription is enabled and should be replicating
subbinary bool
If true, the subscription will request that the publisher send data in binary format
substream 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
State codes for two-phase mode: d = disabled, p = pending enablement, e = enabled
subdisableonerr bool
If true, the subscription will be disabled if one of its workers detects an error
subpasswordrequired bool
If true, the subscription will be required to specify a password for authentication
subrunasowner bool
If true, the subscription will be run with the permissions of the subscription owner
subfailover 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
subretaindeadtuples bool
If true, the detection of conflict_update_deleted is enabled and the information (e.g., dead tuples, commit timestamps, and origins) on the subscriber that is useful for conflict detection is retained.
submaxretention int4
The maximum duration (in milliseconds) for which information (e.g., dead tuples, commit timestamps, and origins) useful for conflict detection can be retained.
subretentionactive bool
The retention status of information (e.g., dead tuples, commit timestamps, and origins) useful for conflict detection. True if retain_dead_tuples is enabled, and the retention duration has not exceeded max_retention_duration, when defined.
subserver oid references pg_foreign_server.oid
Foreign server to use for the connection string. Zero if subconninfo is nonnull.
subconninfo text
Connection string to the upstream database. NULL if subserver is nonzero.
subslotname name
Name of the replication slot in the upstream database (also used for the local replication origin name); null represents NONE
subsynccommit text
The synchronous_commit setting for the subscription’s workers to use
subwalrcvtimeout text
The wal_receiver_timeout setting for the subscription’s workers to use
subpublications text[]
Array of subscribed publication names. These reference publications defined in the upstream database. For more on publications see logical_replication_publication.
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.

Related catalogs

This object references the following other system catalogs:

subdbid → pg_database
subowner → pg_authid
subserver → pg_foreign_server

Version applicability

Present in PostgreSQL 17, 18, 19 (verified against each release’s documentation). This is a long-standing system object that also exists in earlier PostgreSQL releases.

Related & references

Reference: PostgreSQL documentation — pg_subscription.