PostgreSQL Replication Monitoring: My Learnings

Introduction:

Ensuring the health and performance of your PostgreSQL database involves keeping a close eye on replication. This process, where data is copied from one database server to another, is vital for data redundancy and HA. In this blog, we’ll delve into three effective methods for monitoring PostgreSQL replication: using native Select statements, the `check_postgres` perl script, and understanding WAL internals.

Understanding PostgreSQL Replication Before diving into the monitoring techniques, let’s briefly touch upon what replication in PostgreSQL entails. Replication involves synchronizing data from a primary server (master) to one or more secondary servers (slaves or replicas). This setup ensures data redundancy, high availability, and load balancing.

Using Native Select Statements for Replication Monitoring

PostgreSQL provides several system views and functions that can be queried using Select statements to monitor replication. Here are some essential queries:

1. Checking Replication Slots

Replication slots ensure that the primary server retains WAL files until they are no longer needed by the standby servers. You can monitor these slots using:

mydb=# select * from pg_replication_slots ;
-[ RECORD 1 ]-------+----------
slot_name           | book1
plugin              | 
slot_type           | physical
datoid              | 
database            | 
temporary           | f
active              | t
active_pid          | 5688
xmin                | 
catalog_xmin        | 
restart_lsn         | 0/3018430
confirmed_flush_lsn | 
wal_status          | reserved
safe_wal_size       | 
two_phase           | f

This query returns information about the replication slots, such as the slot name, database, and the WAL positions.

2. Monitoring Replication Lag

Replication lag indicates the delay between the primary and the standby servers. To monitor this, use:

mydb=# SELECT                            
    client_addr,
    application_name,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replication_lag
FROM
    pg_stat_replication;
   client_addr   | application_name |   state   | sync_state | replication_lag 
-----------------+------------------+-----------+------------+-----------------
 192.168.232.151 | walreceiver      | streaming | async      |               0
(1 row)

This query provides details about connected standby servers and the lag in terms of WAL log sequence numbers (LSNs).

3. Checking WAL Receiver Status

The wal _receiver process on the standby server fetches WAL data from the primary server. Monitor its status with:

mydb=# select * from pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 3997
status                | streaming
receive_start_lsn     | 0/4000000
receive_start_tli     | 1
written_lsn           | 0/48E96C0
flushed_lsn           | 0/4000000
received_tli          | 1
last_msg_send_time    | 2024-06-12 14:34:42.137068+05:30
last_msg_receipt_time | 2024-06-12 14:34:42.13582+05:30
latest_end_lsn        | 0/48E96C0
latest_end_time       | 2024-06-12 14:33:11.812817+05:30
slot_name             | book1
sender_host           | 192.168.232.150
sender_port           | 5434
conninfo              | user=replica password=******** channel_binding=prefer dbname=replication host=192.168.232.150 port=5434 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

This query gives insights into the connection status and the latest received WAL position.

Replication Monitoring with `check_postgres` perl Script

Monitoring your PostgreSQL replication is crucial for maintaining database performance and reliability. The `check_postgres` perl script is an excellent tool for this task, offering a robust solution for keeping tabs on your replication status. Let’s dive into how you can use this script to ensure your PostgreSQL databases are running smoothly.

What is `check_postgres`?

`check_postgres` is a perl script that checks various attributes and metrics of your PostgreSQL database. It integrates seamlessly with monitoring systems like Nagios, Icinga, Zabbix, and others. With its comprehensive set of features, `check_postgres` helps you monitor replication lag, database size, connection statuses, and much more.

For setting up `check_postgres` and advanced configurations, be sure to check out the official check_postgres documentation at: https://bucardo.org/check_postgres/

Here’s a simple command to check replication lag:

`check_postgres` --action=replication_state --host=<your_host> --dbuser=<your_user>

Replace <your_host> and <your_user> with your actual database host and user. This command will return the replication state of your PostgreSQL server.

To ensure continuous monitoring, you can set up a cron job that runs the `check_postgres` script at regular intervals. Edit your crontab file with the following command:

For example:

*/5 * * * * /usr/local/bin/`check_postgres` --action=replication_state --host=<your_host> --dbuser=<your_user> >> /var/log/`check_postgres`.log 2>&1
 

This setup ensures that any replication issues are promptly detected, allowing you to take swift corrective action.

Exploring WAL Internals

Understanding the internals of Write-Ahead Logging (WAL) is crucial for effective replication monitoring. WAL is a method used by PostgreSQL to ensure data integrity. Here’s a closer look at its key components:

1. WAL Segments

WAL files are divided into segments, each with a default size of 16MB. These segments store changes made to the database. Monitoring the number of wal_segments can give you insights into the database activity and replication.

mydb=# show wal_segment_size ;
-[ RECORD 1 ]----+-----
wal_segment_size | 16MB

2. WAL Archiving

To ensure data can be recovered in the event of a failure, WAL segments can be archived. Configure your PostgreSQL server to archive WAL segments by setting the archive_mode and archive_command parameters in the postgresql.conf file:

```archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'
```

3. WAL Buffers

WAL buffers are memory areas used to store WAL data before it’s written to disk. Monitoring the buffer usage can help you tune the performance. Use the following query to check the WAL buffer status:

mydb=#   
SELECT * FROM pg_stat_bgwriter;
-[ RECORD 1 ]---------+---------------------------------
checkpoints_timed     | 37
checkpoints_req       | 0
checkpoint_write_time | 213379
checkpoint_sync_time  | 3
buffers_checkpoint    | 2117
buffers_clean         | 0
maxwritten_clean      | 0
buffers_backend       | 5794
buffers_backend_fsync | 0
buffers_alloc         | 3693
stats_reset           | 2024-06-12 10:33:48.003491+05:30

This query returns statistics about the background writer process, including the WAL buffer usage.

Conclusion

Monitoring PostgreSQL replication is essential for maintaining a robust and efficient database environment. By utilizing native Select statements, the check_postgres perl script, and understanding WAL internals, you can gain deep insights into your replication status and performance. Regular monitoring and timely alerts can help you prevent issues before they impact your applications.

To ensure your PostgreSQL replication setup remains healthy, consider implementing the methods discussed in this guide. Regularly review your replication status, adjust configurations as needed, and stay informed about the latest PostgreSQL features and best practices.