Обсуждение: [ADMIN] pg_current_xlog*_location and pg_stat_replication.replay_location > 0for synced replication connection

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

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

You could query pg_stat_replication on the master, compare sent_location
and replay_location, and if they are the same, the replication is in sync.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html