In this article, we look at the functions related to PostgreSQL replication.
Function List
WAL functions | ㅤ | ㅤ |
pg_current_wal_lsn | pg_current_wal_flush_lsn | pg_current_wal_insert_lsn |
pg_walfile_name | pg_walfile_name_offset | pg_switch_wal |
pg_wal_lsn_diff | ||
Standby server functions | ㅤ | ㅤ |
pg_is_in_recovery | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn |
pg_last_xact_replay_timestamp | pg_get_wal_replay_pause_state | pg_is_wal_replay_paused |
pg_wal_replay_pause | pg_wal_replay_resume | |
Replication functions | ㅤ | ㅤ |
pg_create_physical_replication_slot | pg_create_logical_replication_slot | pg_drop_replication_slot |
pg_copy_physical_replication_slot | pg_copy_logical_replication_slot | pg_logical_slot_get_changes |
pg_logical_slot_peek_changes | pg_logical_slot_get_binary_changes | pg_logical_slot_peek_binary_changes |
pg_stat_reset_replication_slot | ㅤ |
WAL Functions
Function Name | Description |
pg_current_wal_lsn | Returns the write LSN of the current WAL file |
pg_current_wal_flush_lsn | Returns the current WAL record flush LSN (the WAL LSN to be written to disk) |
pg_current_wal_insert_lsn | Returns the LSN at which the current WAL record will be inserted |
pg_walfile_name | Returns the name of the WAL file for an LSN |
pg_walfile_name_offset | Returns the name and byte offset of the WAL file for an LSN |
pg_switch_wal | Switches the WAL file |
pg_wal_lsn_diff | Difference between two WAL LSNs (unit: Byte) |
pg_current_wal_lsn, pg_currnt_wal_flush_lsn, pg_current_wal_insert_lsn
Each function returns an LSN (Log Sequence Number) for the WAL file.
Changes
The function names changed as of PostgreSQL 10.
PostgreSQL 9.6 and earlier | PostgreSQL 10 and later |
pg_current_xlog_location | pg_current_wal_lsn |
pg_current_xlog_flush_lsn | pg_current_wal_flush_lsn |
pg_current_xlog_insert_lsn | pg_current_wal_insert_lsn |
Run on the main server, they return the current WAL LSN.
📢 If no transactions occur, the results of the functions can be the same.
Run on a server configured as a standby, they return an error saying the function cannot be executed.
pg_walfile_name, pg_walfile_name_offset
You can check the WAL file that corresponds to a WAL LSN.
Changes
The function names changed as of PostgreSQL 10.
PostgreSQL 9.6 and earlier | PostgreSQL 10 and later |
pg_xlogfile_name | pg_walfile_name |
pg_xlogfile_name_offset | pg_walfile_name_offset |
In the example above, for LSN
0/7B3D8038, the WAL file name is 00000001000000000000007B, and the byte offset is 4030520.Run on a server configured as a standby, they return an error saying the function cannot be executed.
pg_wal_lsn_diff
Calculates the difference between the two WAL LSNs passed as arguments. The difference in position is returned as a number representing the size difference between the two WAL LSNs, in bytes.
Changes
The function names changed as of PostgreSQL 10.
PostgreSQL 9.6 and earlier | PostgreSQL 10 and later |
pg_xlog_location_diff | pg_wal_lsn_diff |
In the example above, the difference between WAL LSN
1/301351D0 and WAL LSN 1/301329C8 is 10248Byte(10MB). Without the pg_wal_lsn_diff function, you can calculate it as follows.pg_switch_wal
Switches the WAL file. In the example below, it switches from WAL file 2F to the next one, 30.
Run on a server configured as a standby, it returns an error saying the function cannot be executed.
Standby Server Functions
Function Name | Description |
pg_is_in_recovery | Checks whether the PostgreSQL server is in recovery |
pg_last_wal_receive_lsn | Returns the LSN of the WAL last received by the standby server |
pg_last_wal_replay_lsn | Returns the LSN of the WAL last replayed by the standby server |
pg_last_xact_replay_timestamp | Returns the timestamp of the transaction last replayed by the standby server |
pg_get_wal_replay_pause_state | Checks the recovery pause state (not paused / pause requested / paused) |
pg_is_wal_replay_paused | Checks whether recovery is paused |
pg_wal_replay_pause | Requests that recovery pause |
pg_wal_replay_resume | Requests that recovery resume |
pg_is_in_recovery, pg_last_wal_receive_lsn, pg_last_wal_replay_lsn
The
pg_is_in_recovery function shows whether the PostgreSQL server is in recovery. It returns t on a standby server and f on the main server. The pg_last_wal_receive_lsn function returns the position of the WAL last received by the standby server. The pg_last_wal_replay_lsn function returns the WAL LSN last replayed by the standby server.Changes
The function names changed as of PostgreSQL 10.
PostgreSQL 9.6 and earlier | PostgreSQL 10 and later |
pg_last_xlog_receive_location | pg_last_wal_receive_lsn |
pg_last_xlog_replay_location | pg_last_wal_replay_lsn |
The server that ran the SQL above shows
pg_is_in_recovery as t, so it is running as a standby server, and up to WAL LSN 1/B7FDCDA8 it has received and replayed the data.pg_last_xact_replay_timestamp
Returns the timestamp of the transaction last replayed by the standby server.
In the example above, the timestamp of the last transaction replayed on the standby server is
2023-05-22 11:56:47.375693+09. You can check replication lag from its difference with the now() function, which returns the current time.📢 On a database with many transactions, this value can be a good way to judge replication lag. On a database with few transactions, however, the transaction timestamp does not advance, so the value keeps growing, which makes it unsuitable for judging replication lag.
Functions for Controlling WAL Replay
pg_is_wal_replay_paused: checks the WAL replay state.
pg_wal_replay_pause: pauses WAL replay.
pg_wal_replay_resume: resumes WAL replay.
pg_get_wal_replay_pause_state: checks the WAL replay state.
Changes
The function names changed as of PostgreSQL 10.
PostgreSQL 9.6 and earlier | PostgreSQL 10 and later |
pg_is_xlog_replay_paused | pg_is_wal_replay_paused |
pg_xlog_replay_pause | pg_wal_replay_pause |
pg_xlog_replay_resume | pg_wal_replay_resume |
The
pg_get_wal_replay_pause_state function was added in PostgreSQL 14.Checking the WAL replay pause state
The WAL replay pause state is currently FALSE (not paused).
Pausing WAL replay
After pausing WAL replay, the state shows TRUE (paused).
Resuming WAL replay
After resuming WAL replay, the state goes back to FALSE (not paused).
Replication Slot Functions
Function Name | Description |
pg_create_physical_replication_slot | Creates a physical replication slot (used for streaming replication) |
pg_create_logical_replication_slot | Creates a logical replication slot (used for logical replication) |
pg_drop_replication_slot | Drops a replication slot |
pg_copy_physical_replication_slot | [Since. v12] Copies a physical replication slot |
pg_copy_logical_replication_slot | [Since. v12] Copies a logical replication slot |
pg_logical_slot_get_changes | Returns changes from a logical replication slot (cannot be read again) |
pg_logical_slot_peek_changes | Returns changes from a logical replication slot (can be read again) |
pg_logical_slot_get_binary_changes | Returns changes from a logical replication slot in binary (cannot be read again) |
pg_logical_slot_peek_binary_changes | Returns changes from a logical replication slot in binary (can be read again) |
pg_stat_reset_replication_slot | [Since. v14] Resets replication slot statistics |
Functions for Creating/Copying Physical Replication Slots
pg_create_physical_replication_slot: creates a physical replication slot.
pg_copy_physical_replication_slot: copies a physical replication slot.
Creating a physical replication slot (pg_create_physical_replication_slot)
Checking the physical replication slot (pg_replication_slots catalog)
The slot_type column shows whether a slot is physical or logical, and since no standby server is currently connected for replication, the active column is FALSE.
Copying a physical replication slot (pg_copy_physical_replication_slot)
pg_copy_physical_replication_slot, added in PostgreSQL 12, copies a replication slot created as the physical type. Copying is possible only when the source slot is in the reserved state.
(wal_status = reserved in pg_catalog.pg_replication_slots)If the source slot is not in the reserved state or does not exist, copying the slot fails.
Functions for Creating/Copying Logical Replication Slots
pg_create_logical_replication_slot: creates a logical replication slot.
pg_copy_logical_replication_slot: copies a logical replication slot.
Creating a logical replication slot (pg_create_logical_replication_slot)
Checking the logical replication slot (pg_replication_slots catalog)
The slot_type column shows whether a slot is physical or logical, and since no standby server is currently connected for replication, the active column is FALSE.
Copying a logical replication slot (pg_copy_logical_replication_slot)
pg_copy_logical_replication_slot, added in PostgreSQL 12, copies a replication slot created as the logical type.
Function for Dropping Logical Replication Slots
pg_drop_replication_slot: drops a replication slot.
Dropping a replication slot
Dropping a replication slot
Functions for Viewing Logical Decoding Data
pg_logical_slot_get_changes: returns changes from logical replication.
pg_logical_slot_peek_changes: returns changes from logical replication. (peek)
pg_logical_slot_get_binary_changes: returns changes from logical replication in binary form.
pg_logical_slot_peek_binary_changes: returns changes from logical replication in binary form. (peek)
pg_logical_slot_get_changes
A function that returns changes from a logical replication slot.
Logical replication does not carry DDL, so the pg_logical_slot_get_changes result does not show the DDL itself, only the transaction start (BEGIN) and end (COMMIT). (xid=775) For DML, you can see the changes. (xid=776) In the example above, the changes have already been consumed (read), so if you run the function again while no new transactions occur, no rows are returned.
pg_logical_slot_peek_changes
A function that returns changes from a logical replication slot. It does the same thing as pg_logical_slot_get_changes, with one difference: pg_logical_slot_get_changes cannot be read again, while pg_logical_slot_peek_changes can.
📢 The English words make it easy to remember. get means fetching, so the changes are consumed once read. peek means taking a look, so the changes remain after reading.
pg_logical_slot_get_binary_changes, pg_logical_slot_peek_binary_changes
It does the same thing as the pg_logical_slot_get_changes and pg_logical_slot_peek_changes functions, but returns the output in binary form.
pg_stat_reset_replication_slot
Resets replication slot statistics. This function was added in PostgreSQL 14.
If you run the function with a replication slot name, only that slot is reset. If you do not specify one, all replication slots are reset.
All replication slot statistics are reset to 0, and the time of the reset is recorded in the stats_reset column.
Related articles
