Monitoring and Managing Streaming Replication in PostgreSQL

Streaming replication in PostgreSQL is a vital feature for ensuring data availability and redundancy. Once your primary and standby servers are set up, it’s essential to monitor the replication status and manage the replication process effectively. This guide will walk you through various queries and commands to monitor, pause, and resume replication. 

Monitoring Replication Status 

1. Check Replication Status on the Primary Server:

To monitor the status of replication on the primary server, use the following query: 

SELECT * FROM pg_stat_replication; 

This query provides detailed information about the replication status, including the state of replication connections, the current WAL (Write-Ahead Log) positions, and more. 

2. Check the Current WAL Position on the Primary Server:

The current WAL position on the primary server can be checked with: 

SELECT pg_current_wal_lsn(); 

This command returns the current WAL LSN (Log Sequence Number), which is the position up to which the WAL has been written on the primary server. 

3. Check WAL Positions on the Standby Server:

On the standby server, you can monitor two key positions: 

• Last Received WAL Position:

SELECT pg_last_wal_receive_lsn(); 

This query shows the last WAL LSN received from the primary server. 

• Last Replayed WAL Position:

SELECT pg_last_wal_replay_lsn(); 

This query displays the last WAL LSN that has been replayed on the standby server. 

4. Check Replication Lag (Log Delay):

Replication lag is a critical metric that indicates how much the standby server is behind the primary server in terms of replaying WAL records. You can check the replication lag using the following query: 

SELECT CASE
            WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
            THEN 0
            ELSE EXTRACT (EPOCH FROM now() – pg_last_xact_replay_timestamp())
          END AS log_delay; 

This query returns the replication lag in seconds. If the `pg_last_wal_receive_lsn()` matches `pg_last_wal_replay_lsn()`, the log delay is zero, indicating that the standby server has caught up with the primary server. 

— 

Managing Replication: Pausing and Resuming 

In certain scenarios, you may need to pause replication, for example, to perform maintenance tasks on the standby server. PostgreSQL allows you to pause and resume WAL replay on the standby server with the following commands: 

1. Pause WAL Replay:

To pause the WAL replay process on the standby server, run: 

SELECT pg_wal_replay_pause(); 

This command stops the application of WAL records, effectively pausing the replication process. 

2. Resume WAL Replay:

To resume WAL replay, use: 

SELECT pg_wal_replay_resume(); 

This command resumes the application of WAL records, continuing the replication process from where it was paused. 

— 

Setting a Minimum Apply Delay 

If you want to introduce a delay in applying WAL records on the standby server (for example, for disaster recovery scenarios), you can configure a minimum apply delay by setting the `recovery_min_apply_delay` parameter in your PostgreSQL configuration: 

recovery_min_apply_delay = ’12h’  # Or ‘1min’ or ‘1d 

This setting ensures that WAL records are only applied after the specified delay, giving you a buffer period to respond to potential issues on the primary server.