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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
