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