Replication Lag in PostgreSQL

PostgreSQL is a popular open source relational database management system that is widely used for storing and managing data. One of the common issues that can be encountered in PostgreSQL is replication lag.

In this blog, we will discuss what replication lag is, why it occurs, and how to mitigate it in PostgreSQL.

What is replication lag?

Replication lag is the delay between the time when data is written to the primary database and the time when it is replicated to the standby databases. In PostgreSQL, replication lag can occur due to various reasons such as network latency, slow disk I/O, long-running transactions, etc.

Replication lag can have serious consequences in high-availability systems where standby databases are used for failover. If the replication lag is too high, it can result in data loss when failover occurs.

The most common approach is to run a query referencing this view in the primary node.

PgSQL

1 2 3 4 5 6 7

postgres=# SELECT pid,application_name,client_addr,client_hostname,state,sync_state,replay_lag postgres-# FROM pg_stat_replication postgres-# ; pid | application_name | client_addr | client_hostname | state | sync_state | replay_lag -----+------------------+-------------+-----------------+-------+------------+------------ (0 rows) postgres=#

Queries to check in the Standby node:

PgSQL

1 2 3 4 5 6

postgres=# select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp(); pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp -------------------+-------------------------+-------------------------+------------------------+------------------------------- t | f | D1/8000000 | D1/8000000 | 2023-03-16 11:37:57.861711+00 (1 row) postgres=#

Why does replication lag occur?

Replication lag can occur due to various reasons, such as:

Network latency: Network latency is the delay caused by the time it takes for data to travel between the primary and standby databases.

Various factors, such as the distance between the databases, network congestion, etc., can cause this delay:

Slow disk I/O: Slow disk I/O can be caused by various factors such as disk fragmentation, insufficient disk space, etc. Slow disk I/O can delay writing data to the standby databases.

Long-running transactions: Long-running transactions can cause replication lag because the changes made by these transactions are not replicated until the transaction is committed.

A poor configuration, like setting low numbers of max_wal_senders while processing huge numbers of transaction requests.

Sometimes the server recycles old WAL segments before the backup can finish and cannot find the WAL segment from the primary.

Usually, this is also due to the checkpointing behavior where WAL segments are rotated or recycled.

Mitigating replication lag in PostgreSQL

There are several ways to mitigate replication lag in PostgreSQL, such as:

Increasing the network bandwidth: Increasing the network bandwidth between the primary and standby databases can help reduce replication lag caused by network latency.

Using asynchronous replication: Asynchronous replication can help reduce replication lag by allowing the standby databases to lag behind the primary database.

This means that the standby databases do not have to wait for the primary database to commit transactions before replicating the data.

Tuning PostgreSQL configuration parameters: Tuning the PostgreSQL configuration parameters such as wal_buffers, max_wal_senders, etc.
can help improve replication performance and reduce replication lag.

Monitoring replication lag: Monitoring replication lag can help identify the cause of the lag and take appropriate actions to mitigate it.

PostgreSQL provides several tools, such as pg_stat_replication, pg_wal_receiver_stats, etc., for monitoring replication lag.

Conclusion

Replication lag is a common issue in PostgreSQL that can seriously affect high-availability systems.

Understanding the causes of replication lag and taking appropriate measures to mitigate it can help ensure the availability and reliability of the database system.

By increasing network bandwidth, using asynchronous replication, tuning PostgreSQL configuration parameters, and monitoring replication lag, administrators can mitigate replication lag and ensure a more stable and reliable database environment.

Postgresql Check Standby Replication

Monitoring postgresql replication is in progress but in not adequate I think. here is some scripts for checking a standby replication. In some cases replication has terminated because of some problems. this scripts give you a glance and you will see the problem about replication. But do not forget to check the log file, you can miss some problems if you do mot check the log file. As I said before postresql views is not adequate.

check overall information about replication on primary

1	select * from pg_stat_replication;

If you are using replication slots then replication slots information view is

1	select * from pg_replication_slots;

check overall information about replication on standby

1	select * from pg_stat_wal_receiver;

check status of replication on standby (version > 10)

1 2 3 4 5

select  pg_is_in_recovery(),         pg_is_wal_replay_paused(),         pg_last_wal_receive_lsn(),         pg_last_wal_replay_lsn(),         pg_last_xact_replay_timestamp();

check status of replication on standby (version < 10)

1 2 3 4

select  pg_is_in_recovery(),         pg_last_xlog_receive_location(),         pg_last_xlog_replay_location(),         pg_last_xact_replay_timestamp();

run this query for a few times and check if lsn/xlocation number ad replay timestamp is changing.
if it has not change then check the primary log file there could be an error about replication.

–check lag on standby (version >10)

1 2 3 4

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;

check lag on standby (version <10)

1 2 3 4

SELECT  CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()         THEN 0         ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())         END AS log_delay;

check the primary lsn and compare with standby’s last applied lsn

get current lsn from primary

1 2 3 4

SELECT pg_current_wal_lsn();       -[ RECORD 1 ]------+-----------     pg_current_wal_lsn | 0/1ADE2F80

get last applied lsn from standby

1 2 3 4 5 6 7 8 9 10 11 12

select  pg_is_in_recovery(),         pg_is_wal_replay_paused(),         pg_last_wal_receive_lsn(),         pg_last_wal_replay_lsn(),         pg_last_xact_replay_timestamp();               -[ RECORD 1 ]-----------------+------------------------------         pg_is_in_recovery             | t         pg_is_wal_replay_paused       | f         pg_last_wal_receive_lsn       | 0/1005EEEF         pg_last_wal_replay_lsn        | 0/1005EEEF         pg_last_xact_replay_timestamp | 2021-05-05 13:01:14.328936+00

calculate difference between two lsn from above queries

1 2 3 4 5 6

pg_wal_lsn_diff(lsn pg_lsn, lsn pg_lsn) (version > 10 ) pg_xlog_location_diff (location pg_lsn, location pg_lsn) (version < 10)   select pg_wal_lsn_diff('0/1ADE2F80','0/1005EEEF');     -[ RECORD 1 ]---+---------     pg_wal_lsn_diff | 181944465

previous query return result in bytes, we can get gap size in human readable like this

1 2 3 4 5 6 7

select round(181944465/pow(1024,3.0),2) missing_lsn_GiB;     -[ RECORD 1 ]---+-----     missing_lsn_gib | 0.17   select round(181944465/pow(1024,2.0),2) missing_lsn_GiB;     -[ RECORD 1 ]---+-------     missing_lsn_gib | 173.52