Re: Lag clarification with Sync Replication

[Keith <keith@xxxxxxxxxxx>]

On Fri, May 22, 2020 at 12:36 AM Raj kumar <rajkumar820999@xxxxxxxxx> wrote:

I have made a synchronous replication setup with PG12.

During data loading of 200 gig in master node, I found that there is a lag that it shows on the slave. But, ideally for sync replication, lag should be always 0 right.

Because commit never happens in master before updating the slave.
 log_delay
------------
 871.549671
(1 row) log_delay
------------
 871.557246
(1 row) log_delay
-----------
         0
(1 row) log_delay
-----------
         0
(1 row)

Query Used for checking Standby Replication Lag:

psql -d perfbench -p 5432 -c "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;"

Setting Used in PG12

 cat postgresql.conf |grep synchronous_

synchronous_commit = remote_write                # synchronization level;

synchronous_standby_names = 'ANY 1 (standby1,standby2)'        # standby servers that provide sync rep

cat postgresql.conf |grep primary_conn

primary_conninfo = 'user=edbrepl host= 10.12.11.101 port=5432 application_name=standby1 '

Thanks,

Raj Kumar

For synchronous replication, and any replication really, I would recommend monitoring byte lag from the primary. This is much more accurate and, if the count of replicas goes down from an expected number, you'll know it's been disconnected as well. Monitoring replication from the replica can be done, but it has caveats and shouldn't be the only way it's monitored

Wrote a short post on this a while ago. See relavant updates further down - https://www.keithf4.com/monitoring_streaming_slave_lag/