On Mon, Jun 15, 2015 at 8:30 AM, Anton Bushmelev <djeday84@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
