[ADMIN] Re: pg_current_xlog*_location and pg_stat_replication.replay_location> 0 for synced replication connection - Mailing list pgsql-admin

I'll try to condense my question:

Shouldn't there be an LSN value on the master as well as on the
synchronously replicated streaming standby that always match that I
can query from the master instance? In contrast to an asynchronously
replicated standby where there's some lag during normal write
activity.

Regards,

Aleksander Kamenik

On Wed, Aug 23, 2017 at 12:38 PM, Aleksander Kamenik
<aleksander.kamenik@gmail.com> wrote:
> Hi!
>
> Setting up replication where one of the streaming standbys is using
> synchronous replication.
>
> I would have expected to find a
> pg_xlog_location_diff(pg_current_xlog_*location(),
> pg_stat_replication.replay_location) that would always calculate 0 for
> the synced replication connection. However that is not the case, I
> easily get positive values after an INSERT for about a second.
>
> I understand that
> pg_xlog_location_diff(pg_stat_replication.sent_location,
> sr.replay_location) can be greater than zero, as it takes time for the
> reply to arrive.
>
> However when doing a pg_xlog_location_diff(pg_current_xlog_location(),
> pg_stat_replication.replay_location) (or
> pg_current_xlog_insert_location() or pg_current_xlog_flush_location())
> I still get a greater than zero value for a moment. I would have
> expected to find a current LSN that is always synced with the standby.
> That is, the calculation would always return 0.
>
> Can someone explain the following LSN values in more detail for why
> that's not the case or is it simply be cause it's not an atomic query
> I'm doing?
>
> pg_current_xlog_flush_location() pg_lsn Get current transaction log
> flush location
> pg_current_xlog_insert_location() pg_lsn Get current transaction log
> insert location
> pg_current_xlog_location() pg_lsn Get current transaction log write location
>
> pg_current_xlog_location displays the current transaction log write
> location in the same format used by the above functions. Similarly,
> pg_current_xlog_insert_location displays the current transaction log
> insertion point and pg_current_xlog_flush_location displays the
> current transaction log flush point. The insertion point is the
> "logical" end of the transaction log at any instant, while the write
> location is the end of what has actually been written out from the
> server's internal buffers and flush location is the location
> guaranteed to be written to durable storage. The write location is the
> end of what can be examined from outside the server, and is usually
> what you want if you are interested in archiving partially-complete
> transaction log files.
>
> Could it be, that by durable storage the xlog storage and not main
> data storage is referred to. So main data storage LSN on master and
> pg_stat_replication.replay_location diff are always 0?
>
> synchronous_commit is not set, defaults 'on'.
> PostgreSQL 9.6.3
>
> Regards,
>
> --
> Aleksander Kamenik



--
Aleksander Kamenik