Managing critical databases in enterprise environments demands robust high availability and disaster recovery solutions. Among these, continuous PostgreSQL replication across geographically distant sites is fundamental for operational continuity and minimizing data loss. However, asynchronous replication, while offering flexibility and performance, introduces the risk of lag. If this lag is not monitored and managed, it can severely compromise the Recovery Point Objective (RPO) during a failover. I’ve witnessed scenarios where an RPO of a few seconds ballooned into minutes, or even hours, due to underestimated replication lag, leading to disastrous business consequences. This article explores how to configure and, critically, effectively monitor PostgreSQL replication lag, drawing on real-world implementation and management experience in a critical environment.
Tested on: PostgreSQL 15 · Ubuntu 22.04 LTS · September 2026
Prerequisites / Test Environment
To implement continuous replication and its monitoring, you need two PostgreSQL servers: one configured as a primary (master) and the other as a standby (replica), preferably on distinct networks and data centers. Ensure that the two servers can communicate on the PostgreSQL port (typically 5432) and that traffic is secure (e.g., via VPN or SSH tunnel). For this setup, we will use two PostgreSQL 15 instances on Ubuntu 22.04 LTS. It is crucial to have a dedicated replication user with appropriate privileges and for the server filesystems to have sufficient space for Write-Ahead Logs (WAL).
1. Primary Server Configuration
The first step is to configure the primary server to allow replication. Modify the postgresql.conf file (usually in /etc/postgresql/15/main/postgresql.conf) with the following parameters:
wal_level = replica
archive_mode = on
archive_command = 'cp %p /mnt/pg_wal_archive/%f'
max_wal_senders = 10
listen_addresses = '*'wal_level = replica: Enables writing the necessary information for replication into WALs.archive_mode = on: Enables archiving of completed WAL segments.archive_commandspecifies how to archive the files. In a production environment, you would use a remote storage system (NFS, S3, etc.) or a tool likepg_basebackuporbarmanfor archiving and recovery. For our test, we will use a simple local copy.max_wal_senders = 10: Defines the maximum number ofwalsenderprocesses that can be started. Each standby requires awalsender.listen_addresses = '*': Allows PostgreSQL to accept connections from any IP address. In production, it is advisable to specify specific IP addresses or subnets for security reasons.
Next, modify the pg_hba.conf file to allow the replica to connect. Add a line similar to this:
host replication all <STANDBY_IP>/32 md5Replace
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'your_secure_password';Restart the PostgreSQL service on the primary to apply the changes:
sudo systemctl restart postgresql@15-main2. Standby Server Preparation
Before configuring the standby, you need to create a base backup from the primary. This can be done using pg_basebackup directly from the standby server:
sudo -u postgres pg_basebackup -h <PRIMARY_IP> -D /var/lib/postgresql/15/main -U replicator -P -v -R-h: IP address of the primary server.-D /var/lib/postgresql/15/main: PostgreSQL data directory on the standby server.-U replicator: The replication user created earlier.-P: Shows progress.-v: Verbose output.-R: Automatically creates astandby.signalfile and adds the necessary configurations topostgresql.auto.confto start the standby. This replaces the oldrecovery.confin recent PostgreSQL versions.
If you are using an older PostgreSQL version that requires recovery.conf, the -R command will generate a recovery.conf file with the following (or similar) content:
standby_mode = 'on'
primary_conninfo = 'host=<PRIMARY_IP> port=5432 user=replicator password=your_secure_password application_name=standby1'
primary_slot_name = 'standby_slot'Ensure that primary_conninfo contains the correct IP address of the primary and the password for the replicator user.
Start the PostgreSQL service on the standby:
sudo systemctl start postgresql@15-mainVerify the replication status on the primary with ps aux | grep walsender and on the standby with ps aux | grep walreceiver. You should see active processes. Read also: PostgreSQL: Find Killer Queries with pg_stat_statements
3. Monitoring Replication Lag
Monitoring replication lag is crucial. PostgreSQL provides built-in functions for this purpose. Connect to the database on the standby server and use the following query:
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS byte_lag,
(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())) AS time_lag_seconds;pg_last_wal_receive_lsn(): The LSN (Log Sequence Number) of the last WAL record received by the standby.pg_last_wal_replay_lsn(): The LSN of the last WAL record applied (replayed) by the standby.byte_lag: The difference in bytes between received and applied WALs. A larger value indicates a data-based lag.time_lag_seconds: The lag in seconds based on the timestamp of the last replayed transaction. This is often the most relevant data for RPO.
A byte_lag or time_lag_seconds consistently greater than zero indicates that replication is asynchronous. If these values increase significantly, it means the standby cannot keep up with the primary. Read also: Oracle Database: Managing and Optimizing Performance
You can create a table and a function to record the lag over time, for historical analysis and integration with monitoring systems like Prometheus or Zabbix:
CREATE TABLE replica_lag_history (
timestamp TIMESTAMPTZ DEFAULT now(),
byte_lag BIGINT,
time_lag_seconds INT
);
CREATE OR REPLACE FUNCTION record_replica_lag()
RETURNS VOID AS $$
DECLARE
current_byte_lag BIGINT;
current_time_lag_seconds INT;
BEGIN
SELECT (pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) INTO current_byte_lag;
SELECT (EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())) INTO current_time_lag_seconds;
INSERT INTO replica_lag_history (byte_lag, time_lag_seconds)
VALUES (current_byte_lag, current_time_lag_seconds);
END;
$$ LANGUAGE plpgsql;
-- Run every minute
SELECT cron.schedule('record_replica_lag', '* * * * *', 'SELECT record_replica_lag();');Common Errors and Troubleshooting
- Connection refused: Check
pg_hba.confon the primary and firewall rules. Ensure port 5432 is open and thereplicatoruser has correct permissions. - Standby not starting: Check the PostgreSQL logs on the standby server (
/var/log/postgresql/postgresql-15-main.log). Common errors include incorrectprimary_conninfo, non-empty data directory beforepg_basebackup, or permission issues. - High replication lag: This can be caused by several factors: insufficient I/O on the standby server, slow network between primary and standby, excessive load on the primary generating too many WALs, or insufficient resources on the standby to apply WALs. Check I/O, CPU, and network metrics on both servers. A useful external link for further reading is the official PostgreSQL documentation on Streaming Replication.
FAQ — Frequently Asked Questions
Does asynchronous replication guarantee zero data loss?
No, asynchronous replication does not guarantee a zero RPO. There is always a potential delay between when a transaction is committed on the primary and when it is applied on the standby. In the event of a sudden primary crash, transactions not yet replicated to the standby will be lost. For zero RPO, synchronous replication is required, but this introduces latency to writes on the primary.
How can I reduce replication lag?
To reduce lag, ensure the standby server has adequate hardware resources (CPU, RAM, I/O) to process WALs. Optimize the network between primary and standby to reduce latency and increase bandwidth. On the primary, consider optimizing queries to reduce the amount of WAL generated. If the lag is persistent and inexplicable, horizontal or vertical scaling might be necessary.
Can I use the standby for reads?
Yes, one of the main advantages of replication is the ability to use the standby server as a read-only server to balance query load. This is known as a Read Replica. Be sure to direct read queries to the standby server to lighten the load on the primary and improve overall system performance.
What happens if the primary goes down?
If the primary fails, you will need to manually (or automatically with tools like pg_auto_failover or Patroni) promote one of the standbys to a new primary. The promotion process will apply all received but not yet applied WALs, and then the standby will begin accepting writes. It is critical to have a well-documented and tested failover process.
Conclusions with Operational Takeaways
Continuous PostgreSQL replication is a cornerstone of infrastructural resilience for databases. Implementing asynchronous replication between two data centers, as described, offers a good balance between performance and data protection. However, the true value of this configuration only emerges if replication lag is consistently monitored. Ignoring this aspect means operating with an unknown RPO, a gamble too risky for any production environment. Integrating lag metrics with your central monitoring system and setting up automatic alerts is a non-negotiable action to ensure your disaster recovery plan always aligns with expectations.
