엑셈

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, _location was renamed to _lsn.

Starting with PostgreSQL 10, the write_lag, flush_lag, and replay_lag columns were added.

Starting with PostgreSQL 12, the replay_time column 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_host and sender_port columns were added.

Starting with PostgreSQL 13, received_lsn was split into written_lsn and flushed_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 temporary column was added.

Starting with PostgreSQL 13, the wal_status and safe_wal_size columns were added.

Starting with PostgreSQL 14, the two_phase column 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 pubtruncate column was added.

Starting with PostgreSQL 13, the pubviaroot column 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 oid column was added.

Starting with PostgreSQL 15, the prqual and prattrs columns 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 attnames and rowfilter columns 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 oid column was added.

Starting with PostgreSQL 13, the subslotname column can be NULL.

Starting with PostgreSQL 14, the subbinary and substream columns were added.

Starting with PostgreSQL 15, the subskiplsn, subtwophasestate, and subdisableonerr columns were added.

Starting with PostgreSQL 16, the suborigin column 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 srsublsn column 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