Catalogs and views v4

Catalogs and views are presented here in alphabetical order.

User-visible catalogs and views

bdr.conflict_history

This table is the default table where conflicts are logged. The table is RANGE partitioned on column local_time and is managed by Autopartition. The default data retention period is 30 days.

Access to this table is possible by any table owner, who can see all conflicts for the tables they own, restricted by row-level security.

For details, see Logging conflicts to a table.

bdr.conflict_history columns

NameTypeDescription
sub_idoidWhich subscription produced this conflict; can be joined to bdr.subscription table
local_xidxidLocal transaction of the replication process at the time of conflict
local_lsnpg_lsnLocal transaction of the replication process at the time of conflict
local_timetimestamp with time zoneLocal time of the conflict
remote_xidxidTransaction that produced the conflicting change on the remote node (an origin)
remote_commit_lsnpg_lsnCommit LSN of the transaction which produced the conflicting change on the remote node (an origin)
remote_commit_timetimestamp with time zoneCommit timestamp of the transaction that produced the conflicting change on the remote node (an origin)
conflict_typetextDetected type of the conflict
conflict_resolutiontextConflict resolution chosen
conflict_indexregclassConflicting index (valid only if the index wasn't dropped since)
reloidoidConflicting relation (valid only if the index wasn't dropped since)
nspnametextName of the schema for the relation on which the conflict has occurred at the time of conflict (doesn't follow renames)
relnametextName of the relation on which the conflict has occurred at the time of conflict (does not follow renames)
key_tuplejsonJson representation of the key used for matching the row
remote_tuplejsonJson representation of an incoming conflicting row
local_tuplejsonJson representation of the local conflicting row
apply_tuplejsonJson representation of the resulting (the one that has been applied) row
local_tuple_xminxidTransaction that produced the local conflicting row (if local_tuple is set and the row isn't frozen)
local_tuple_node_idoidNode that produced the local conflicting row (if local_tuple is set and the row isn't frozen)
local_tuple_commit_timetimestamp with time zoneLast known change timestamp of the local conflicting row (if local_tuple is set and the row isn't frozen)

bdr.conflict_history_summary

A view containing user-readable details on row conflict.

bdr.conflict_history_summary columns

NameTypeDescription
nspnametextName of the schema
relnametextName of the table
local_timetimestamp with time zoneLocal time of the conflict
local_tuple_commit_timetimestamp with time zoneTime of local commit
remote_commit_timetimestamp with time zoneTime of remote commit
conflict_typetextType of conflict
conflict_resolutiontextResolution adopted

bdr.consensus_kv_data

A persistent storage for the internal Raft-based KV store used by bdr.consensus_kv_store() and bdr.consensus_kv_fetch() interfaces.

bdr.consensus_kv_data Columns

NameTypeDescription
kv_keytextUnique key
kv_valjsonArbitrary value in json format
kv_create_tstimestamptzLast write timestamp
kv_ttlintTime to live for the value in milliseconds
kv_expire_tstimestamptzExpiration timestamp (kv_create_ts + kv_ttl)

bdr.camo_decision_journal

A persistent journal of decisions resolved by a CAMO partner node after a failover, in case bdr.logical_transaction_status was invoked. Unlike bdr.node_pre_commit, this doesn't cover transactions processed under normal operational conditions (i.e., both nodes of a CAMO pair are running and connected). Entries in this journal aren't ever cleaned up automatically. This is a diagnostic tool that the system doesn't depend on.

bdr.camo_decision_journal columns

NameTypeDescription
origin_node_idoidOID of the node where the transaction executed
origin_xidoidTransaction ID on the remote origin node
decisionchar'c' for commit, 'a' for abort
decision_tstimestamptzDecision time

bdr.crdt_handlers

This table lists merge ("handlers") functions for all CRDT data types.

bdr.crdt_handlers Columns

NameTypeDescription
crdt_type_idregtypeCRDT data type ID
crdt_merge_idregprocMerge function for this data type

bdr.ddl_replication

This view lists DDL replication configuration as set up by current DDL filters.

bdr.ddl_replication columns

NameTypeDescription
set_ddl_namenameName of DDL filter
set_ddl_tagtextThe command tags it applies on (regular expression)
set_ddl_roletextThe roles it applies to (regular expression)
set_namenameName of the replication set for which this filter is defined

bdr.depend

This table tracks internal object dependencies inside BDR catalogs.

bdr.global_consensus_journal

This catalog table logs all the Raft messages that were sent while managing global consensus.

As for the bdr.global_consensus_response_journal catalog, the payload is stored in a binary encoded format, which can be decoded with the bdr.decode_message_payload() function. See the bdr.global_consensus_journal_details view for more details.

bdr.global_consensus_journal columns

NameTypeDescription
log_indexint8ID of the journal entry
termint8Raft term
originoidID of node where the request originated
req_idint8ID for the request
req_payloadbyteaPayload for the request
trace_contextbyteaTrace context for the request

bdr.global_consensus_journal_details

This view presents Raft messages that were sent and the corresponding responses, using the bdr.decode_message_payload() function to decode their payloads.

bdr.global_consensus_journal_details columns

NameTypeDescription
log_indexint8ID of the journal entry
termint8Raft term
request_idint8ID of the request
origin_idoidID of the node where the request originated
req_payloadbyteaPayload of the request
origin_node_namenameName of the node where the request originated
message_type_nooidID of the BDR message type for the request
message_typetextName of the BDR message type for the request
message_payloadtextBDR message payload for the request
response_message_type_nooidID of the BDR message type for the response
response_message_typetextName of the BDR message type for the response
response_payloadtextBDR message payload for the response
response_errcode_notextSQLSTATE for the response
response_errcodetextError code for the response
response_messagetextError message for the response

bdr.global_consensus_response_journal

This catalog table collects all the responses to the Raft messages that were received while managing global consensus.

As for the bdr.global_consensus_journal catalog, the payload is stored in a binary-encoded format, which can be decoded with the bdr.decode_message_payload() function. See the bdr.global_consensus_journal_details view for more details.

bdr.global_consensus_response_journal columns

NameTypeDescription
log_indexint8ID of the journal entry
res_statusoidStatus code for the response
res_payloadbyteaPayload for the response
trace_contextbyteaTrace context for the response

bdr.global_lock

This catalog table stores the information needed for recovering the global lock state on server restart.

For monitoring usage, the bdr.global_locks view is preferable because the visible rows in bdr.global_lock don't necessarily reflect all global locking activity.

Don't modify the contents of this table. It is an important BDR catalog.

bdr.global_lock columns

NameTypeDescription
ddl_epochint8DDL epoch for the lock
origin_node_idoidOID of the node where the global lock has originated
lock_typeoidType of the lock (DDL or DML)
nspnamenameSchema name for the locked relation
relnamenameRelation name for the locked relation
groupidoidOID of the top level group (for Advisory locks)
key1integerFirst 32-bit key or lower order 32-bits of 64-bit key (for advisory locks)
key2integerSecond 32-bit key or higher order 32-bits of 64-bit key (for advisory locks)
key_is_bigintbooleanTrue if 64-bit integer key is used (for advisory locks)

bdr.global_locks

A view containing active global locks on this node. The bdr.global_locks view exposes BDR's shared-memory lock state tracking, giving administrators greater insight into BDR's global locking activity and progress.

See Monitoring global locks for more information about global locking.

bdr.global_locks columns

NameTypeDescription
origin_node_idoidThe OID of the node where the global lock has originated
origin_node_namenameName of the node where the global lock has originated
lock_typetextType of the lock (DDL or DML)
relationtextLocked relation name (for DML locks) or keys (for advisory locks)
pidint4PID of the process holding the lock
acquire_stagetextInternal state of the lock acquisition process
waitersint4List of backends waiting for the same global lock
global_lock_request_timetimestamptzTime this global lock acquire was initiated by origin node
local_lock_request_timetimestamptzTime the local node started trying to acquire the local lock
last_state_change_timetimestamptzTime acquire_stage last changed

Column details:

  • relation: For DML locks, relation shows the relation on which the DML lock is acquired. For global advisory locks, relation column actually shows the two 32-bit integers or one 64-bit integer on which the lock is acquired.

  • origin_node_id and origin_node_name: If these are the same as the local node's ID and name, then the local node is the initiator of the global DDL lock, i.e., it is the node running the acquiring transaction. If these fields specify a different node, then the local node is instead trying to acquire its local DDL lock to satisfy a global DDL lock request from a remote node.

  • pid: The process ID of the process that requested the global DDL lock, if the local node is the requesting node. Null on other nodes. Query the origin node to determine the locker pid.

  • global_lock_request_time: The timestamp at which the global-lock request initiator started the process of acquiring a global lock. Can be null if unknown on the current node. This time is stamped at the beginning of the DDL lock request and includes the time taken for DDL epoch management and any required flushes of pending-replication queues. Currently only known on origin node.

  • local_lock_request_time: The timestamp at which the local node started trying to acquire the local lock for this global lock. This includes the time taken for the heavyweight session lock acquire but doesn't include any time taken on DDL epochs or queue flushing. If the lock is reacquired after local node restart, it becomes the node restart time.

  • last_state_change_time: The timestamp at which the bdr.global_locks.acquire_stage field last changed for this global lock entry.

bdr.local_consensus_snapshot

This catalog table contains consensus snapshots created or received by the local node.

bdr.local_consensus_snapshot columns

NameTypeDescription
log_indexint8ID of the journal entry
log_termint8Raft term
snapshotbyteaRaft snapshot data

bdr.local_consensus_state

This catalog table stores the current state of Raft on the local node.

bdr.local_consensus_state columns

NameTypeDescription
node_idoidID of the node
current_termint8Raft term
apply_indexint8Raft apply index
voted_foroidVote cast by this node in this term
last_known_leaderoidnode_id of last known Raft leader

bdr.local_node

This table identifies the local node in the current database of the current Postgres instance.

bdr.local_node columns

NameTypeDescription
node_idoidID of the node
pub_repsetstext[]Published replication sets
sub_repsetstext[]Subscribed replication sets

bdr.local_node_summary

A view containing the same information as bdr.node_summary but only for the local node.

bdr.local_sync_status

Information about status of either subscription or table synchronization process.

bdr.local_sync_status columns

NameTypeDescription
sync_kindcharThe kind of synchronization done
sync_subidoidID of subscription doing the synchronization
sync_nspnamenameSchema name of the synchronized table (if any)
sync_relnamenameName of the synchronized table (if any)
sync_statuscharCurrent state of the synchronization
sync_remote_relidoidID of the synchronized table (if any) on the upstream
sync_end_lsnpg_lsnPosition at which the synchronization state last changed

bdr.network_path_info

A catalog view that stores user-defined information on network costs between node locations.

bdr.network_path_info columns

NameTypeDescription
node_group_namenameName of the BDR group
node_region1textNode region name, from bdr.node_location
node_region2textNode region name, from bdr.node_location
node_location1textNode location name, from bdr.node_location
node_location2textNode location name, from bdr.node_location
network_costnumericNode location name, from bdr.node_location

bdr.node

This table lists all the BDR nodes in the cluster.

bdr.node columns

NameTypeDescription
node_idoidID of the node
node_namenameName of the node
node_group_idoidID of the node group
source_node_idoidID of the source node
synchronize_structure"char"Schema synchronization done during the join
node_stateoidConsistent state of the node
target_stateoidState that the node is trying to reach (during join or promotion)
seq_idint4Sequence identifier of the node used for generating unique sequence numbers
dbnamenameDatabase name of the node
node_dsncharConnection string for the node
proto_version_rangesint[]Supported protocol version ranges by the node

bdr.node_catchup_info

This catalog table records relevant catchup information on each node, either if it is related to the join or part procedure.

bdr.node_catchup_info columns

NameTypeDescription
node_idoidID of the node
node_source_idoidID of the node used as source for the data
slot_namenameSlot used for this source
min_node_lsnpg_lsnMinimum LSN at which the node can switch to direct replay from a peer node
catchup_stateoidStatus code of the catchup state
origin_node_idoidID of the node from which we want transactions

If a node(node_id) needs missing data from a parting node(origin_node_id), it can get it from a node that already has it(node_source_id) by forwarding. The records in this table persists until the node(node_id) is a member of the BDR cluster.

bdr.node_conflict_resolvers

Currently configured conflict resolution for all known conflict types.

bdr.node_conflict_resolvers columns

NameTypeDescription
conflict_typetextType of the conflict
conflict_resolvertextResolver used for this conflict type

bdr.node_group

This catalog table lists all the BDR node groups.

bdr.node_group columns

NameTypeDescription
node_group_idoidID of the node group
node_group_namenameName of the node group
node_group_default_repsetoidDefault replication set for this node group
node_group_default_repset_extoidDefault replication set for this node group
node_group_parent_idoidID of parent group (0 if this is a root group)
node_group_flagsintThe group flags
node_group_uuiduuidThe uuid of the group
node_group_apply_delayintervalHow long a subscriber waits before applying changes from the provider
node_group_check_constraintsboolWhether the apply process checks constraints when applying data
node_group_num_writersintNumber of writers to use for subscriptions backing this node group
node_group_enable_wal_decoderboolWhether the group has enable_wal_decoder set
node_group_streaming_modecharTransaction streaming setting: 'O' - off, 'F' - file, 'W' - writer, 'A' - auto, 'D' - default

bdr.node_group_replication_sets

A view showing default replication sets create for BDR groups. See also bdr.replication_sets.

bdr.node_group_replication_sets columns

NameTypeDescription
node_group_namenameName of the BDR group
def_repsetnameName of the default repset
def_repset_opstext[]Actions replicated by the default repset
def_repset_extnameName of the default "external" repset (usually same as def_repset)
def_repset_ext_opstext[]Actions replicated by the default "external" repset (usually same as def_repset_ops)

bdr.node_local_info

A catalog table used to store per-node configuration that's specific to the local node (as opposed to global view of per-node configuration).

bdr.node_local_info columns

NameTypeDescription
node_idoidThe OID of the node (including the local node)
applied_stateoidInternal ID of the node state
ddl_epochint8Last epoch number processed by the node
slot_namenameName of the slot used to connect to that node (NULL for the local node)

bdr.node_location

A catalog view that stores user-defined information on node locations.

bdr.node_location Columns

NameTypeDescription
node_group_namenameName of the BDR group
node_idoidID of the node
node_regiontextUser-supplied region name
node_locationtextUser-supplied location name

bdr.node_log_config

A catalog view that stores information on the conflict logging configurations.

bdr.node_log_config columns

NameDescription
log_nameName of the logging configuration
log_to_fileWhether it logs to the server log file
log_to_tableWhether it logs to a table, and which table is the target
log_conflict_typeWhich conflict types it logs, if NULL means all
log_conflict_resWhich conflict resolutions it logs, if NULL means all

bdr.node_peer_progress

Catalog used to keep track of every node's progress in the replication stream. Every node in the cluster regularly broadcasts its progress every bdr.replay_progress_frequency milliseconds to all other nodes (default is 60000 ms, i.e., 1 minute). Expect N * (N-1) rows in this relation.

You might be more interested in the bdr.node_slots view for monitoring purposes. See also Monitoring.

bdr.node_peer_progress columns

NameTypeDescription
node_idoidThe OID of the originating node that reported this position info
peer_node_idoidThe OID of the node's peer (remote node) for which this position info was reported
last_update_sent_timetimestamptzThe time at which the report was sent by the originating node
last_update_recv_timetimestamptzThe time at which the report was received by the local server
last_update_node_lsnpg_lsnLSN on the originating node at the time of the report
peer_positionpg_lsnLatest LSN of the node's peer seen by the originating node
peer_replay_timetimestamptzLatest replay time of peer seen by the reporting node
last_update_horizon_xidoidInternal resolution horizon: all lower xids are known resolved on the reporting node
last_update_horizon_lsnpg_lsnInternal resolution horizon: same in terms of an LSN of the reporting node

bdr.node_pre_commit

Used internally on a node configured as a Commit At Most Once (CAMO) partner. Shows the decisions a CAMO partner took on transactions in the last 15 minutes.

bdr.node_pre_commit columns

NameTypeDescription
origin_node_idoidOID of the node where the transaction executed
origin_xidoidTransaction ID on the remote origin node
decisionchar'c' for commit, 'a' for abort
local_xidxidTransaction ID on the local node
commit_tstimestamptzCommit timestamp of the transaction
decision_tstimestamptzDecision time

bdr.node_replication_rates

This view contains information about outgoing replication activity from a given node.

bdr.node_replication_rates columns

ColumnTypeDescription
peer_node_idoidThe OID of node's peer (remote node) for which this info was reported
target_namenameName of the target peer node
sent_lsnpg_lsnLatest sent position
replay_lsnpg_lsnLatest position reported as replayed (visible)
replay_lagintervalApproximate lag time for reported replay
replay_lag_bytesint8Bytes difference between replay_lsn and current WAL write position on origin
replay_lag_sizetextHuman-readable bytes difference between replay_lsn and current WAL write position
apply_ratebigintLSNs being applied per second at the peer node
catchup_intervalintervalApproximate time required for the peer node to catch up to all the changes that are yet to be applied
Note

The replay_lag is set immediately to zero after reconnect. As a workaround, use replay_lag_bytes, replay_lag_size, or catchup_interval.

bdr.node_slots

This view contains information about replication slots used in the current database by BDR.

See Monitoring outgoing replication for guidance on the use and interpretation of this view's fields.

bdr.node_slots columns

NameTypeDescription
target_dbnamenameDatabase name on the target node
node_group_namenameName of the BDR group
node_group_idoidThe OID of the BDR group
origin_namenameName of the origin node
target_namenameName of the target node
origin_idoidThe OID of the origin node
target_idoidThe OID of the target node
local_slot_namenameName of the replication slot according to BDR
slot_namenameName of the slot according to Postgres (same as above)
is_group_slotbooleanTrue if the slot is the node-group crash recovery slot for this node (see ["Group Replication Slot"](nodes#Group Replication Slot))
is_decoder_slotbooleanIs this slot used by Decoding Worker
pluginnameLogical decoding plugin using this slot (should be pglogical_output or bdr)
slot_typetextType of the slot (should be logical)
datoidoidThe OID of the current database
databasenameName of the current database
temporaryboolIs the slot temporary
activeboolIs the slot active (does it have a connection attached to it)
active_pidint4The PID of the process attached to the slot
xminxidThe XID needed by the slot
catalog_xminxidThe catalog XID needed by the slot
restart_lsnpg_lsnLSN at which the slot can restart decoding
confirmed_flush_lsnpg_lsnLatest confirmed replicated position
usesysidoidsysid of the user the replication session is running as
usenamenameusername of the user the replication session is running as
application_nametextApplication name of the client connection (used by synchronous_standby_names)
client_addrinetIP address of the client connection
client_hostnametextHostname of the client connection
client_portint4Port of the client connection
backend_starttimestamptzWhen the connection started
statetextState of the replication (catchup, streaming, ...) or 'disconnected' if offline
sent_lsnpg_lsnLatest sent position
write_lsnpg_lsnLatest position reported as written
flush_lsnpg_lsnLatest position reported as flushed to disk
replay_lsnpg_lsnLatest position reported as replayed (visible)
write_lagintervalApproximate lag time for reported write
flush_lagintervalApproximate lag time for reported flush
replay_lagintervalApproximate lag time for reported replay
sent_lag_bytesint8Bytes difference between sent_lsn and current WAL write position
write_lag_bytesint8Bytes difference between write_lsn and current WAL write position
flush_lag_bytesint8Bytes difference between flush_lsn and current WAL write position
replay_lag_bytesint8Bytes difference between replay_lsn and current WAL write position
sent_lag_sizetextHuman-readable bytes difference between sent_lsn and current WAL write position
write_lag_sizetextHuman-readable bytes difference between write_lsn and current WAL write position
flush_lag_sizetextHuman-readable bytes difference between flush_lsn and current WAL write position
replay_lag_sizetextHuman-readable bytes difference between replay_lsn and current WAL write position
Note

The replay_lag is set immediately to zero after reconnect. As a workaround, use replay_lag_bytes or replay_lag_size.

bdr.node_summary

This view contains summary information about all BDR nodes known to the local node.

bdr.node_summary columns

NameTypeDescription
node_namenameName of the node
node_group_namenameName of the BDR group the node is part of
interface_connstrtextConnection string to the node
peer_state_nametextConsistent state of the node in human readable form
peer_target_state_nametextState that the node is trying to reach (during join or promotion)
node_seq_idint4Sequence identifier of the node used for generating unique sequence numbers
node_local_dbnamenameDatabase name of the node
set_repl_opstextWhich operations does the default replication set replicate
node_idoidThe OID of the node
node_group_idoidThe OID of the BDR node group

bdr.queue

This table stores the historical record of replicated DDL statements.

bdr.queue columns

NameTypeDescription
queued_attimestamptzWhen was the statement queued
rolenameWhich role has executed the statement
replication_setstext[]Which replication sets was the statement published to
message_typecharType of a message
messagejsonPayload of the message needed for replication of the statement

bdr.replication_set

A table that stores replication set configuration. For user queries, we recommend instead checking the bdr.replication_sets view.

bdr.replication_set columns

NameTypeDescription
set_idoidThe OID of the replication set
set_nodeidoidOID of the node (always local node oid currently)
set_namenameName of the replication set
replicate_insertbooleanIndicates if the replication set replicates INSERTs
replicate_updatebooleanIndicates if the replication set replicates UPDATEs
replicate_deletebooleanIndicates if the replication set replicates DELETEs
replicate_truncatebooleanIndicates if the replication set replicates TRUNCATEs
set_isinternalbooleanReserved
set_autoadd_tablesbooleanIndicates if new tables are automatically added to this replication set
set_autoadd_seqsbooleanIndicates if new sequences are automatically added to this replication set

bdr.replication_set_table

A table that stores replication set table membership. For user queries, we recommend instead checking the bdr.tables view.

bdr.replication_set_table columns

NameTypeDescription
set_idoidThe OID of the replication set
set_reloidregclassLocal ID of the table
set_att_listtext[]Reserved
set_row_filterpg_node_treeCompiled row filtering expression

bdr.replication_set_ddl

A table that stores replication set ddl replication filters. For user queries, we recommend instead checking the bdr.ddl_replication view.

bdr.replication_set_ddl Columns

NameTypeDescription
set_idoidThe OID of the replication set
set_ddl_namenameName of the DDL filter
set_ddl_tagtextCommand tag for the DDL filter
set_ddl_roletextRole executing the DDL

bdr.replication_sets

A view showing replication sets defined in the BDR group, even if they aren't currently used by any node.

bdr.replication_sets columns

NameTypeDescription
set_idoidThe OID of the replication set
set_namenameName of the replication set
replicate_insertbooleanIndicates if the replication set replicates INSERTs
replicate_updatebooleanIndicates if the replication set replicates UPDATEs
replicate_deletebooleanIndicates if the replication set replicates DELETEs
replicate_truncatebooleanIndicates if the replication set replicates TRUNCATEs
set_autoadd_tablesbooleanIndicates if new tables are automatically added to this replication set
set_autoadd_seqsbooleanIndicates if new sequences are automatically added to this replication set

bdr.schema_changes

A simple view to show all the changes to schemas win BDR.

bdr.schema_changes columns

NameTypeDescription
schema_changes_tstimestampstzThe ID of the trigger
schema_changes_changecharA flag of change type
schema_changes_classidoidClass ID
schema_changes_objectidoidObject ID
schema_changes_subidsmallintThe subscription
schema_changes_descrtextThe object changed
schema_changes_addrnamestext[]Location of schema change

bdr.sequence_alloc

A view to see the allocation details for galloc sequences.

bdr.sequence_alloc columns

NameTypeDescription
seqidregclassThe ID of the sequence
seq_chunk_sizebigintA sequence number for the chunk within its value
seq_allocated_up_tobigint
seq_nallocsbigint
seq_last_alloctimestamptzLast sequence allocated

bdr.schema_changes

A simple view to show all the changes to schemas in BDR.

bdr.schema_changes columns

NameTypeDescription
schema_changes_tstimestampstzThe ID of the trigger
schema_changes_changecharA flag of change type
schema_changes_classidoidClass ID
schema_changes_objectidoidObject ID
schema_changes_subidsmallintThe subscription
schema_changes_descrtextThe object changed
schema_changes_addrnamestext[]Location of schema change

bdr.sequence_alloc

A view to see the sequences allocated.

bdr.sequence_alloc columns

NameTypeDescription
seqidregclassThe ID of the sequence
seq_chunk_sizebigintA sequence number for the chunk within its value
seq_allocated_up_tobigint
seq_nallocsbigint
seq_last_alloctimestamptzLast sequence allocated

bdr.sequences

This view lists all sequences with their kind, excluding sequences for internal BDR bookkeeping.

bdr.sequences columns

NameTypeDescription
nspnamenameNamespace containing the sequence
relnamenameName of the sequence
seqkindtextType of the sequence ('local', 'timeshard', 'galloc')

bdr.stat_activity

Dynamic activity for each backend or worker process.

This contains the same information as pg_stat_activity, except wait_event is set correctly when the wait relates to BDR.

bdr.stat_relation

Apply statistics for each relation. Contains data only if the tracking is enabled and something was replicated for a given relation.

bdr.stat_relation columns

ColumnTypeDescription
nspnamenameName of the relation's schema
relnamenameName of the relation
relidoidOID of the relation
total_timedouble precisionTotal time spent processing replication for the relation
ninsertbigintNumber of inserts replicated for the relation
nupdatebigintNumber of updates replicated for the relation
ndeletebigintNumber of deletes replicated for the relation
ntruncatebigintNumber of truncates replicated for the relation
shared_blks_hitbigintTotal number of shared block cache hits for the relation
shared_blks_readbigintTotal number of shared blocks read for the relation
shared_blks_dirtiedbigintTotal number of shared blocks dirtied for the relation
shared_blks_writtenbigintTotal number of shared blocks written for the relation
blk_read_timedouble precisionTotal time spent reading blocks for the relation, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timedouble precisionTotal time spent writing blocks for the relation, in milliseconds (if track_io_timing is enabled, otherwise zero)
lock_acquire_timedouble precisionTotal time spent acquiring locks on the relation, in milliseconds (if bdr.track_apply_lock_timing is enabled, otherwise zero)

bdr.stat_subscription

Apply statistics for each subscription. Contains data only if the tracking is enabled.

bdr.stat_subscription columns

ColumnTypeDescription
sub_namenameName of the subscription
subidoidOID of the subscription
nconnectbigintNumber of times this subscription has connected upstream
ncommitbigintNumber of commits this subscription did
nabortbigintNumber of aborts writer did for this subscription
nerrorbigintNumber of errors writer has hit for this subscription
nskippedtxbigintNumber of transactions skipped by writer for this subscription (due to skip_transaction conflict resolver)
ninsertbigintNumber of inserts this subscription did
nupdatebigintNumber of updates this subscription did
ndeletebigintNumber of deletes this subscription did
ntruncatebigintNumber of truncates this subscription did
nddlbigintNumber of DDL operations this subscription has executed
ndeadlocksbigintNumber of errors that were caused by deadlocks
nretriesbigintNumber of retries the writer did (without going for full restart/reconnect)
nstream_writerbigintNumber of transactions streamed to writer
nstream_filebigintNumber of transactions streamed to file
nstream_commitbigintNumber of streaming transactions committed
nstream_abortbigintNumber of streaming transactions aborted
nstream_startbigintNumber of STREAT START messages processed
nstream_stopbigintNumber of STREAM STOP messages processed
shared_blks_hitbigintTotal number of shared block cache hits by the subscription
shared_blks_readbigintTotal number of shared blocks read by the subscription
shared_blks_dirtiedbigintTotal number of shared blocks dirtied by the subscription
shared_blks_writtenbigintTotal number of shared blocks written by the subscription
blk_read_timedouble precisionTotal time the subscription spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timedouble precisionTotal time the subscription spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
connect_timetimestamp with time zoneTime when the current upstream connection was established, NULL if not connected
last_disconnect_timetimestamp with time zoneTime when the last upstream connection was dropped
start_lsnpg_lsnLSN from which this subscription requested to start replication from the upstream
retries_at_same_lsnbigintNumber of attempts the subscription was restarted from the same LSN value
curr_ncommitbigintNumber of commits this subscription did after the current connection was established

bdr.subscription

This catalog table lists all the subscriptions owned by the local BDR node and their modes.

bdr.subscription columns

NameTypeDescription
sub_idoidID of the subscription
sub_namenameName of the subscription
nodegroup_idoidID of nodegroup
origin_node_idoidID of origin node
source_node_idoidID of source node
target_node_idoidID of target node
subscription_modecharMode of subscription
sub_enabledboolWhether the subscription is enabled (should be replication)
apply_delayintervalHow much behind should the apply of changes on this subscription be (normally 0)
slot_namenameSlot on upstream used by this subscription
origin_namenameLocal origin used by this subscription
num_writersintNumber of writer processes this subscription uses
streaming_modecharStreaming configuration for the subscription
replication_setstext[]Replication sets replicated by this subscription (NULL = all)
forward_origintext[]Origins forwarded by this subscription (NULL = all)

bdr.subscription_summary

This view contains summary information about all BDR subscriptions that the local node has to other nodes.

bdr.subscription_summary columns

NameTypeDescription
node_group_namenameName of the BDR group the node is part of
sub_namenameName of the subscription
origin_namenameName of the origin node
target_namenameName of the target node (normally local node)
sub_enabledboolIs the subscription enabled
sub_slot_namenameSlot name on the origin node used by this subscription
sub_replication_setstext[]Replication sets subscribed
sub_forward_originstext[]Does the subscription accept changes forwarded from other nodes besides the origin
sub_apply_delayintervalDelay transactions by this much compared to the origin
sub_origin_namenameReplication origin name used by this subscription
bdr_subscription_modecharSubscription mode
subscription_statustextStatus of the subscription worker
node_group_idoidThe OID of the BDR group the node is part of
sub_idoidThe OID of the subscription
origin_idoidThe OID of the origin node
target_idoidThe OID of the target node
receive_lsnpg_lsnLatest LSN of any change or message received (this can go backwards in case of restarts)
receive_commit_lsnpg_lsnLatest LSN of last COMMIT received (this can go backwards in case of restarts)
last_xact_replay_lsnpg_lsnLSN of last transaction replayed on this subscription
last_xact_flush_lsntimestamptzLSN of last transaction replayed on this subscription that's flushed durably to disk
last_xact_replay_timestamptimestamptzTimestamp of last transaction replayed on this subscription

bdr.replication_status

This view shows incoming replication status between the local node and all other nodes in the BDR cluster. We consider replication to be blocked when the subscription restarted from the same LSN at least twice and not a single transaction is yet applied after the current upstream connection was established. If the first transaction after restart is very big and still being applied, the replication_blocked result might be wrong.

If this is a logical standby node, then only the status for its upstream node is shown. Similarly, replication status isn't shown for subscriber-only nodes since they never send replication changes to other nodes.

bdr.replication_status columns

ColumnTypeDescription
node_idoidOID of the local node
node_namenameName of the local node
origin_node_idoidOID of the origin node
origin_node_namenameName of the origin node
sub_idoidOID of the subscription for this origin node
sub_namenameName of the subscription for this origin node
connectedbooleanIs this node connected to the origin node?
replication_blockedbooleanIs the replication currently blocked for this origin?
connect_timetimestamp with time zoneTime when the current connection was established
disconnect_timetimestamp with time zoneTime when the last connection was dropped
uptimeintervalDuration since the current connection is active for this origin

bdr.tables

This view lists information about table membership in replication sets. If a table exists in multiple replication sets, it appears multiple times in this table.

bdr.tables columns

NameTypeDescription
relidoidThe OID of the relation
nspnamenameName of the schema relation is in
relnamenameName of the relation
set_namenameName of the replication set
set_opstext[]List of replicated operations
rel_columnstext[]List of replicated columns (NULL = all columns) (*)
row_filtertextRow filtering expression
conflict_detectiontextConflict detection method used: row_origin (default), row_version or column_level

(*) These columns are reserved for future use and should currently be NULL

bdr.trigger

In this view, you can see all the stream triggers created. Often triggers here are created from bdr.create_conflict_trigger.

bdr.trigger columns

NameTypeDescription
trigger_idoidThe ID of the trigger
trigger_reloidregclassName of the relating function
trigger_pgtgidoidPostgres trigger ID
trigger_typecharType of trigger call
trigger_namenameName of the trigger

bdr.triggers

An expanded view of bdr.trigger with columns that are easier to read.

NameTypeDescription
trigger_namenameThe name of the trigger
event_manipulationtextThe operation(s)
trigger_typebdr.trigger_typeType of trigger
trigger_tablebdr.trigger_reloidThe table that calls it
trigger_functionnameThe function used

bdr.workers

Information about running BDR worker processes.

This can be joined with bdr.stat_activity using pid to get even more insight into the state of BDR workers.

bdr.workers Columns

NameTypeDescription
worker_pidintProcess ID of the worker process
worker_roleintNumeric representation of worker role
worker_role_nametextName of the worker role
worker_subidoidSubscription ID if the worker is associated with one
worker_commit_timestamptimestamptzLast commit timestamp processed by this worker if any
worker_local_timestamptimestamptzLocal time at which the above commit was processed if any

bdr.worker_errors

A persistent log of errors from BDR background worker processes.

bdr.worker_errors Columns

NameTypeDescription
node_group_namenameName of the BDR group
origin_namenameName of the origin node
source_namename
target_namenameName of the target node (normally local node)
sub_namenameName of the subscription
worker_roleint4Internal identifier of the role of this worker (1: manager, 2: receive, 3: writer, 4: output, 5: extension)
worker_role_nametextRole name
worker_pidint4Process ID of the worker causing the error
error_timetimestamptzDate and time of the error
error_ageintervalDuration since error
error_messagetextDescription of the error
error_context_messagetextContext in which the error happened
remoterelidoidOID of remote relation on that node

bdr.writers

Specific information about BDR writer processes.

bdr.writers columns

NameTypeDescription
sub_namenameName of the subscription
pidintProcess ID of the worker process
syncing_relintOID of the relation being synchronized (if any)
streaming_allowedtextCan this writer be target of direct to writer streaming
is_streamingboolIs there transaction being streamed to this writer
remote_xidxidRemote transaction id of the transaction being processed (if any)
remote_commit_lsnpg_lsnLSN of last commit processed
commit_queue_positionintPosition in the internal commit queue
commit_timestamptimestamptzTimestamp of last commit processed
nxactsbigintNumber of transactions processed by this writer
ncommitsbigintNumber of transactions committed by this writer
nabortsbigintNumber of transactions aborted by this writer
nstream_filebigintNumber of streamed-to-file transactions processed by this writer
nstream_writerbigintNumber of streamed-to-writer transactions processed by this writer

bdr.worker_tasks

The bdr.worker_tasks view shows BDR's current worker launch rate limiting state as well as some basic statistics on background worker launch and registration activity.

Unlike the other views listed here, it isn't specific to the current database and BDR node. State for all BDR nodes on the current PostgreSQL instance is shown. Join on the current database to filter it.

bdr.worker_tasks doesn't track walsenders and output plugins.

bdr.worker_tasks columns

ColumnTypeDescription
task_key_worker_roleintegerWorker role identifier
task_key_worker_role_nametextWorker role name
task_key_dboidoidDatabase identifier, if available
datnamenameName of the database, if available
task_key_subidoidSubscription identifier, if available
sub_namenameName of the subscription, if available
task_key_ext_libnamenameName of the library (most likely bdr)
task_key_ext_funcnamenameName of the function entry point
task_key_ext_workernamenameName assigned to the worker
task_key_remoterelidoidIdentifier of the remote syncing relation, if available
task_pidintegerProcess ID of the worker
task_registeredtimestamp with time zoneWorker registration timestamp
since_registeredintervalInterval since the worker registered
task_attachedtimestamp with time zoneWorker attach timestamp
since_attachedintervalInterval since the worker attached
task_exitedtimestamp with time zoneWorker exit timestamp
since_exitedintervalInterval since the worker exited
task_successbooleanIs worker still running?
task_next_launch_not_beforetimestamp with time zoneTimestamp when the worker will be restarted again
until_launch_allowedintervalTime remaining for next launch
task_last_launch_requestor_pidintegerProcess ID that requested launch
task_last_launch_request_timetimestamp with time zoneTimestamp when the request was made
since_last_requestintervalInterval since the last request
task_last_launch_request_approvedbooleanDid the last request succeed?
task_nrequestsintegerNumber of requests
task_nregistrationsintegerNumber of registrations
task_prev_pidintegerProcess ID of the previous generation
task_prev_registeredtimestamp with time zoneTimestamp of the previous registered task
since_prev_registeredintervalInterval since the previous registration
task_prev_launchedtimestamp with time zoneTimestamp of the previous launch
since_prev_launchedintervalInterval since the previous launch
task_prev_exitedtimestamp with time zoneTimestamp when the previous task exited
since_prev_exitedintervalInterval since the previous task exited
task_first_registeredtimestamp with time zoneTimestamp when the first registration happened
since_first_registeredintervalInterval since the first registration

bdr.autopartition_work_queue

Contains work items created and processed by autopartition worker. The work items are created on only one node and processed on different nodes.

bdr.autopartition_work_queue columns

ColumnTypeDescription
ap_wq_workidbigintThe unique ID of the work item
ap_wq_ruleidintID of the rule listed in autopartition_rules. Rules are specified using bdr.autopartition command
ap_wq_relnamenameName of the relation being autopartitioned
ap_wq_relnamespacenameName of the tablespace specified in rule for this work item
ap_wq_partnamenameName of the partition created by the workitem
ap_wq_work_kindcharThe work kind can be either 'c' (Create Partition), 'm' (Migrate Partition), 'd' (Drop Partition), 'a' (Alter Partition)
ap_wq_work_sqltextSQL query for the work item
ap_wq_work_dependsOid[]OIDs of the nodes on which the work item depends

bdr.autopartition_workitem_status

The status of the work items that is updated locally on each node.

bdr.autopartition_workitem_status columns

ColumnTypeDescription
ap_wi_workidbigintThe ID of the work item
ap_wi_nodeidOidOID of the node on which the work item is being processed
ap_wi_statuscharThe status can be either 'q' (Queued), 'c' (Complete), 'f' (Failed), 'u' (Unknown)
ap_wi_started_attimestamptzThe start timestamptz of work item
ap_wi_finished_attimestamptzThe end timestamptz of work item

bdr.autopartition_local_work_queue

Contains work items created and processed by autopartition worker. This is similar to bdr.autopartition_work_queue, except that these work items are for locally managed tables. Each node creates and processes its own local work items, independent of other nodes in the cluster.

bdr.autopartition_local_work_queue columns

ColumnTypeDescription
ap_wq_workidbigintThe unique ID of the work item
ap_wq_ruleidintID of the rule listed in autopartition_rules. Rules are specified using bdr.autopartition command
ap_wq_relnamenameName of the relation being autopartitioned
ap_wq_relnamespacenameName of the tablespace specified in rule for this work item.
ap_wq_partnamenameName of the partition created by the workitem
ap_wq_work_kindcharThe work kind can be either 'c' (Create Partition), 'm' (Migrate Partition), 'd' (Drop Partition), 'a' (Alter Partition)
ap_wq_work_sqltextSQL query for the work item
ap_wq_work_dependsOid[]Always NULL

bdr.autopartition_local_workitem_status

The status of the work items for locally managed tables.

bdr.autopartition_local_workitem_status columns

ColumnTypeDescription
ap_wi_workidbigintThe ID of the work item
ap_wi_nodeidOidOID of the node on which the work item is being processed
ap_wi_statuscharThe status can be either 'q' (Queued), 'c' (Complete), 'f' (Failed), 'u' (Unknown)
ap_wi_started_attimestamptzThe start timestamptz of work item
ap_wi_finished_attimestamptzThe end timestamptz of work item

bdr.group_camo_details

Uses bdr.run_on_all_nodes to gather CAMO-related information from all nodes.

bdr.group_camo_details columns

NameTypeDescription
node_idtextInternal node ID
node_nametextName of the node
camo_partnertextNode name of the camo partner
is_camo_partner_connectedtextConnection status
is_camo_partner_readytextReadiness status
camo_transactions_resolvedtextAre there any pending and unresolved CAMO transactions
apply_lsntextLatest position reported as replayed (visible)
receive_lsntextLatest LSN of any change or message received (can go backwards in case of restarts)
apply_queue_sizetextBytes difference between apply_lsn and receive_lsn

bdr.camo_pairs

Information regarding all the CAMO pairs configured in all the cluster.

bdr.camo_pairs columns

NameTypeDescription
node_group_idoidNode group ID
left_node_idoidNode ID
right_node_idoidNode ID
require_raftboolWhether switching to local mode requires majority
Note

The names left and right have no special meaning. BDR4 can configure only symmetric CAMO configuration, i.e., both nodes in the pair are CAMO partners for each other.

bdr.commit_scopes

Catalog storing all possible commit scopes that you can use for bdr.commit_scope to enable group commit.

bdr.commit_scopes columns

NameTypeDescription
commit_scope_idoidID of the scope to be referenced
commit_scope_namenameName of the scope to be referenced
commit_scope_origin_node_groupoidNode group for which the rule applies, referenced by ID
sync_scope_ruletextDefinition of the scope

bdr.group_raft_details

Uses bdr.run_on_all_nodes to gather Raft Consensus status from all nodes.

bdr.group_raft_details columns

NameTypeDescription
node_idoidInternal node ID
node_namenameName of the node
statetextRaft worker state on the node
leader_idoidNode id of the RAFT_LEADER
current_termintRaft election internal ID
commit_indexintRaft snapshot internal ID
nodesintNumber of nodes accessible
voting_nodesintNumber of nodes voting
protocol_versionintProtocol version for this node

bdr.group_replslots_details

Uses bdr.run_on_all_nodes to gather BDR slot information from all nodes.

bdr.group_replslots_details columns

NameTypeDescription
node_group_nametextName of the BDR group
origin_nametextName of the origin node
target_nametextName of the target node
slot_nametextSlot name on the origin node used by this subscription
activetextIs the slot active (does it have a connection attached to it)
statetextState of the replication (catchup, streaming, ...) or 'disconnected' if offline
write_lagintervalApproximate lag time for reported write
flush_lagintervalApproximate lag time for reported flush
replay_lagintervalApproximate lag time for reported replay
sent_lag_bytesint8Bytes difference between sent_lsn and current WAL write position
write_lag_bytesint8Bytes difference between write_lsn and current WAL write position
flush_lag_bytesint8Bytes difference between flush_lsn and current WAL write position
replay_lag_byteint8Bytes difference between replay_lsn and current WAL write position

bdr.group_subscription_summary

Uses bdr.run_on_all_nodes to gather subscription status from all nodes.

bdr.group_subscription_summary columns

NameTypeDescription
origin_node_nametextName of the origin of the subscription
target_node_nametextName of the target of the subscription
last_xact_replay_timestamptextTimestamp of the last replayed transaction
sub_lag_secondstextLag between now and last_xact_replay_timestamp

bdr.group_versions_details

Uses bdr.run_on_all_nodes to gather BDR information from all nodes.

bdr.group_versions_details columns

NameTypeDescription
node_idoidInternal node ID
node_namenameName of the node
postgres_versiontextPostgreSQL version on the node
bdr_versiontextBDR version on the node

Internal catalogs and views

bdr.ddl_epoch

An internal catalog table holding state per DDL epoch.

bdr.ddl_epoch columns

NameTypeDescription
ddl_epochint8Monotonically increasing epoch number
origin_node_idoidInternal node ID of the node that requested creation of this epoch
epoch_consume_timeouttimestamptzTimeout of this epoch
epoch_consumedbooleanSwitches to true as soon as the local node has fully processed the epoch

bdr.internal_node_pre_commit

Internal catalog table. Use the bdr.node_pre_commit view.

bdr.sequence_kind

An internal state table storing the type of each nonlocal sequence. We recommend the view bdr.sequences for diagnostic purposes.

bdr.sequence_kind columns

NameTypeDescription
seqidoidInternal OID of the sequence
seqkindcharInternal sequence kind ('l'=local,'t'=timeshard,'s'=snowflakeid,'g'=galloc)

bdr.state_journal

An internal node state journal. Use bdr.state_journal_details for diagnostic purposes instead.

bdr.state_journal_details

Every change of node state of each node is logged permanently in bdr.state_journal for diagnostic purposes. This view provides node names and human-readable state names and carries all of the information in that journal. Once a node has successfully joined, the last state entry is BDR_PEER_STATE_ACTIVE. This differs from the state of each replication connection listed in bdr.node_slots.state.

bdr.state_journal_details columns

NameTypeDescription
state_counteroidMonotonically increasing event counter, per node
node_idoidInternal node ID
node_namenameName of the node
stateoidInternal state ID
state_nametextHuman-readable state name
entered_timetimestamptzPoint in time the current node observed the state change