Re: Lag clarification with Sync Replication

On Fri, May 22, 2020 at 12:36 AM Raj kumar <rajkumar820999(at)gmail(dot)com> 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/