pg_last_xact_replay_timestamp lies
| От | Anton Bushmelev |
|---|---|
| Тема | pg_last_xact_replay_timestamp lies |
| Дата | |
| Msg-id | 557E0EAF.8060000@gmail.com обсуждение исходный текст |
| Ответы |
Re: pg_last_xact_replay_timestamp lies
|
| Список | pgsql-general |
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)
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)
В списке pgsql-general по дате отправления: