Re: pg_last_xact_replay_timestamp lies

On Mon, Jun 15, 2015 at 8:30 AM, Anton Bushmelev <djeday84(at)gmail(dot)com> wrote:
> Hello, dear guru =) help me to fix situation when no change made on primary
> and standby show lag more than 15 minutes:
> master :
> postgres=# select * from pg_stat_replication
> ;
> -[ RECORD 1 ]----+------------------------------
> pid | 18553
> usesysid | 117942
> usename | replication
> application_name | walreceiver
> client_addr | 10.62.43.60
> client_hostname |
> client_port | 45281
> backend_start | 2015-06-15 00:02:00.095195+03
> state | streaming
> sent_location | 62/BC000000
> write_location | 62/BC000000
> flush_location | 62/BC000000
> replay_location | 62/BC000000
> sync_priority | 0
> sync_state | async
>
> standby:
> postgres=# SELECT (extract(epoch from now() -
> pg_last_xact_replay_timestamp() ))::int AS slave_lag;
> slave_lag
> -----------
> 1030
>
> on master :
> postgres=# select name,setting from pg_settings where lower (category) like
> '%write%log%';
> name | setting
> ------------------------------+-------------------------------------------------------------------
> archive_command | cp -i %p
> /var/lib/postgresql/9.3/main/pg_archivelog/%f </dev/null
> archive_mode | on
> archive_timeout | 600
> checkpoint_completion_target | 0.7
> checkpoint_segments | 32
> checkpoint_timeout | 300
> checkpoint_warning | 30
> commit_delay | 0
> commit_siblings | 5
> fsync | on
> full_page_writes | on
> synchronous_commit | on
> wal_buffers | 2048
> wal_level | hot_standby
> wal_sync_method | fdatasync
> wal_writer_delay | 200
> (16 rows)

Isn't your mistake the fact that you rely on the assumption that
replication lag measured in terms of timestamp is a good thing while
it should be estimated in terms of byte difference by comparing WAL
positions between the master and its standbys?
--
Michael