In this article, we look at the catalogs related to PostgreSQL replication.
Catalog Name | Description |
pg_stat_replication | Replication statistics |
pg_stat_wal_receiver | WAL receiver statistics |
pg_replication_slots | Replication slot information |
pg_stat_replication_slots | [Since. v14] Replication slot statistics |
pg_publication | Information about logical replication publications |
pg_publication_rel | Mapping between relations and publications |
pg_publication_tables | Tables included in a publication |
pg_subscription | Subscription information |
pg_subscription_rel | Tables included in a subscription |
pg_stat_subscription_stats | [Since. v15] Statistics on logical replication subscription errors |
pg_stat_replication
Shows one row per WAL sender process, with information and statistics about the standby server connected to that WAL sender process. There is one row for each standby server connected to a WAL sender; downstream standby servers are not shown. Only streaming and logical replication are shown.
Changes
- Starting with PostgreSQL 10,
_locationwas renamed to_lsn.
- Starting with PostgreSQL 10, the
write_lag,flush_lag, andreplay_lagcolumns were added.
- Starting with PostgreSQL 12, the
replay_timecolumn was added.
Column Name | Description |
pid | PID of the WAL sender process |
usesysid | OID of the user connected to the WAL sender process |
username | Name of the user connected to the WAL sender process |
application_name | Name of the application connected to the WAL sender |
client_addr | IP of the client connected to the WAL sender. NULL means the connection is through a Unix socket |
client_hostname | Hostname of the client connected to the WAL sender (shown when resolved by DNS lookup). NULL when log_hostname=off in postgresql.conf |
client_port | TCP port of the client connected to the WAL sender. (Also visible in the process list, ps -ef) -1 for connections through a Unix socket |
backend_start | Time the client connected to the WAL sender |
backend_xmin | xmin reported by the standby server through hot_standby_feedback |
state | State of the WAL sender- startup : WAL sender is starting- catchup : the standby server is catching up with the main server- streaming : the standby server is streaming changes from the main server- backup : the WAL sender is sending a backup.- stopping : the WAL sender is stopping |
sent_lsn | [Rename. v10] Last WAL LSN sent |
write_lsn | [Rename. v10] Last WAL LSN written to disk by the standby server |
flush_lsn | [Rename. v10] Last WAL LSN flushed to disk by the standby server |
replay_lsn | [Rename. v10] Last WAL LSN replayed into the standby server's database |
write_lag | [Since. v10] Time elapsed between flushing recent WAL on the main server and receiving notification that the standby server has written it. |
flush_lag | [Since. v10] Time elapsed between flushing recent WAL on the main server and receiving notification that the standby server has written and flushed it. |
replay_lag | [Since. v10] Time elapsed between flushing recent WAL on the main server and receiving notification that the standby server has written, flushed, and applied it. |
sync_priority | Priority of the standby server for being chosen as the synchronous standby in priority-based synchronous replication. |
sync_state | Synchronous state. (async, potential, sync, quorum)- async : the standby server is asynchronous.- potential : the standby server is asynchronous, but can potentially become synchronous if one of the current synchronous servers fails.- sync : the standby server is synchronous- quorum : the standby server is considered a candidate for quorum standbys |
reply_time | [Since. v12] Send time of the last reply message received from the standby server |
📢 If there are no changes in the WAL files, write_lag, flush_lag, and replay_lag may show NULL.
pg_stat_wal_receiver
Shows statistics about the current WAL receiver. Since this catalog is about the receiver, it returns nothing on the main server and can be queried on the standby server. It is available only in a streaming replication setup.
Changes
- Starting with PostgreSQL 11, the
sender_hostandsender_portcolumns were added.
- Starting with PostgreSQL 13,
received_lsnwas split intowritten_lsnandflushed_lsn.
Column Name | Description |
pid | PID of the WAL receiver process |
status | State of the WAL receiver process |
receive_start_lsn | First WAL LSN used when the WAL receiver starts |
receive_start_tli | First timeline number used when the WAL receiver starts |
written_lsn | [Since. v13] Last WAL LSN already received and written to disk (not flushed) |
flushed_lsn | [Since. v13] Last WAL LSN already received and flushed to disk |
received_lsn | [Deprecated. v13] Last WAL LSN already received and flushed to disk |
received_tli | Timeline number of the last WAL LSN received and flushed to disk |
last_msg_sned_time | Send time of the last message received from the origin WAL sender |
last_msg_receipt_time | Receipt time of the last message received from the origin WAL sender |
latest_end_lsn | Last WAL LSN reported to the origin WAL sender |
latest_end_time | Time of the last WAL LSN reported to the origin WAL sender |
slot_name | Name of the replication slot used by the WAL receiver |
sender_host | [Since. v11] Host of the WAL sender the WAL receiver is connected to. (Can be a hostname, IP, or directory path) |
sender_port | [Since. v11] Port of the WAL sender the WAL receiver is connected to |
conninfo | Connection information for connecting to the WAL sender |
pg_replication_slots
Lists the replication slots that currently exist in PostgreSQL.
Changes
- Starting with PostgreSQL 10, the
temporarycolumn was added.
- Starting with PostgreSQL 13, the
wal_statusandsafe_wal_sizecolumns were added.
- Starting with PostgreSQL 14, the
two_phasecolumn was added.
Column Name | Description |
slot_name | Unique identifier (name) of the replication slot |
plugin | For logical replication, the name of the shared object containing the output plugin. NULL for physical replication |
slot_type | Whether the slot type is physical or logical |
datoid | For logical replication, the OID of the database the slot is associated with. (pg_database.oid) NULL for physical replication |
database | For logical replication, the name of the database the slot is associated with. (pg_database.datname) NULL for physical replication |
temporary | [Since. v10] Whether the slot is temporary (TRUE / FALSE) |
active | Slot state (Active : TRUE / Inactive : FALSE) |
active_pid | PID of the session using the slot when the slot is active (active = TRUE) |
xmin | Oldest transaction the database must retain for this slot |
catalog_xmin | Oldest transaction affecting the system catalogs that the database must retain for this slot |
restart_lsn | Address (LSN) of the oldest WAL that the slot's consumer may still need |
confirmed_flush_lsn | For logical replication, the address (LSN) up to which the slot's consumer has confirmed receiving data. NULL for physical replication |
wal_status | [Since. v13] Availability of the WAL files requested by the slot- reserved : the requested WAL files are within max_wal_size- extended : max_wal_size is exceeded, but the files are retained by the replication slot or wal_keep_size- unreserved : the slot no longer retains the WAL files and they will be removed at the next checkpoint- lost : the WAL files have been removed and the slot can no longer be used- The unreserved and lost states appear only when max_slot_wal_keep_size is not negative, and NULL is shown if restart_lsn = NULL. |
safe_wal_size | [Since. v13] Number of bytes that can be written to WAL without the slot being at risk of entering the "lost" state. NULL if the state is lost or max_slot_wal_keep_size = -1 |
two_phase | [Since. v14] For logical replication, whether the slot is enabled for decoding prepared transactions. Always FALSE for physical replication |
pg_stat_replication_slots
Shows statistics on logical replication slot usage.
Changes
- This catalog was added in PostgreSQL 14.
Column Name | Description |
slot_name | Unique identifier (name) of the replication slot |
spill_txns | Number of transactions spilled to disk because the memory used by logical decoding to decode WAL changes exceeded logical_decoding_work_mem |
spill_count | Number of times transactions were spilled to disk while decoding WAL changes |
spill_bytes | Amount of decoded transaction data spilled to disk while decoding WAL changes |
stream_txns | Number of in-progress transactions streamed to the decoding output plugin after the memory used by logical decoding to decode WAL changes exceeded logical_decoding_work_mem |
stream_count | Number of times in-progress transactions were streamed to the decoding output plugin while decoding WAL changes |
stream_bytes | Amount of decoded transaction data streamed to the decoding output plugin while decoding WAL changes |
total_txns | Number of decoded transactions sent to the decoding output plugin |
total_bytes | Amount of decoded transaction data sent to the decoding output plugin while decoding WAL changes |
stats_reset | Time the statistics were last reset |
pg_publication
Logical replication was introduced in PostgreSQL 10, and the pg_publication catalog was created for monitoring it. It contains information about publishers.
Changes
- Starting with PostgreSQL 11, the
pubtruncatecolumn was added.
- Starting with PostgreSQL 13, the
pubviarootcolumn was added.;
Column Name | Description |
oid | Row Identifier |
pubname | Publication name |
pubowner | Publication owner (pg_authid.oid) |
puballtables | Whether to include all tables in the database- TRUE : automatically includes all tables in the database, including tables created later- FALSE : the tables included in the publication are specified |
pubinsert | Whether INSERT operations on publication tables are replicated (TRUE / FALSE) |
pubupdate | Whether UPDATE operations on publication tables are replicated (TRUE / FALSE) |
pubdelete | Whether DELETE operations on publication tables are replicated (TRUE / FALSE) |
pubtruncate | Whether TRUNCATE operations on publication tables are replicated (TRUE / FALSE) |
pubviaroot | If true, operations on a leaf partition are replicated using the identity and schema of its topmost partitioned ancestor mentioned in the publication instead of its own. |
pg_publication_rel
Contains the relationship between the database and publications. You can look up the tables included in a publication, but since it shows OIDs it is hard to read, so you can query the pg_publication_tables catalog instead. Publications created with FOR ALL TABLES do not appear in the pg_publication_rel catalog and can be queried through the pg_publication_tables catalog.
Changes
- Starting with PostgreSQL 12, the
oidcolumn was added.
- Starting with PostgreSQL 15, the
prqualandprattrscolumns were added.
Column Name | Description |
oid | Row Identifier |
prpubid | Reference to the publication (pg_publication.oid) |
prrelid | Reference to the relation (pg_class.oid) |
prqual | Expression tree (in nodeToString() representation) for the relation's publication qualifying condition. Null if there is no publication qualifying condition. |
prattrs | This is an array of values that indicates which table columns are part of the publication. For example, a value of 1 3 would mean that the first and the third table columns are published. A null value indicates that all columns are published. |
pg_publication_tables
Contains the relationship between the database and publications. You can look up the tables included in a publication. Publications created with FOR ALL TABLES do not appear in the pg_publication_rel catalog and can be queried through the pg_publication_tables catalog.
Changes
- Starting with PostgreSQL 15, the
attnamesandrowfiltercolumns were added.
Column Name | Description |
pubname | Publication name (pg_publication.pubname) |
schemaname | Name of the schema containing the table (pg_namespace.nspname) |
tablename | Table name (pg_class.relname) |
attnames | Names of the table columns included in the publication (pg_attribute.attname). If no columns are specified when setting up the publication, all columns of the table are included |
rowfilter | Expression (condition) for the table's publication qualifying condition |
pg_subscription
The pg_subscription catalog shows information about logical replication subscriptions.
- Starting with PostgreSQL 12, the
oidcolumn was added.
- Starting with PostgreSQL 13, the
subslotnamecolumn can be NULL.
- Starting with PostgreSQL 14, the
subbinaryandsubstreamcolumns were added.
- Starting with PostgreSQL 15, the
subskiplsn,subtwophasestate, andsubdisableonerrcolumns were added.
- Starting with PostgreSQL 16, the
suborigincolumn was added.
Column Name | Description |
oid | Row Identifier |
subdbid | OID of the database where the subscription resides (pg_database.oid) |
subskiplsn | [Since. v15] Finish LSN of the transaction whose changes are to be skipped; otherwise 0/0 |
subname | Subscription name |
subowner | Subscription owner (pg_authid.oid) |
subenabled | Whether the subscription is enabled (TRUE / FALSE) |
subbinary | [Since. v14] Whether to request that data be sent in binary format (TRUE / FALSE) |
substream | [Since. v14] Whether streaming of in-progress transactions is allowed for the subscription (TRUE / FALSE) |
subtwophasestate | [Since. v15] two-phase mode state ( d: disabled, p: pending enablement, e: enabled) |
subdisableonerr | [Since. v15] Whether to disable the subscription when an error is detected (TRUE / FALSE) |
subconninfo | Connection information for connecting to the publication |
subslotname | Name of the slot for connecting to the publication. NULL = NONE |
subsynccommit | Contains the synchronous_commit setting for the subscriber (ON / OFF) |
subpublications | Array of the names of subscribed publications |
suborigin | [Since. v16] none or any- none : the publisher sends changes regardless of origin (Default)- any : asks the publisher to send only changes that have no origin |
pg_subscription_rel
Shows the state of the relationship with the subscription.
Changes
- Starting with PostgreSQL 13, the
srsublsncolumn can be NULL.
Column Name | Description |
srsubid | Reference to the subscription (pg_subscription.oid) |
srrelid | ID of the object included in the subscription |
srsubstate | Object state ( i: initialize, d: data is being copied, s: synchronized, r: ready) |
srsublsn | Last LSN for the s and r states |
pg_stat_subscription_stats
Shows logical replication subscription errors.
Changes
- This catalog was added in PostgreSQL 15.
Column Name | Description |
subid | Subscription oid |
subname | Subscription name |
apply_error_count | Number of errors that occurred while applying changes |
sync_error_count | Number of errors that occurred during initial table synchronization |
stats_reset | Time the current statistics were last reset |
Related Articles
