{"action":"created","issue":{"url":"https://api.github.com/repos/bucardo/check_postgres/issues/119","repository_url":"https://api.github.com/repos/bucardo/check_postgres","labels_url":"https://api.github.com/repos/bucardo/check_postgres/issues/119/labels{/name}","comments_url":"https://api.github.com/repos/bucardo/check_postgres/issues/119/comments","events_url":"https://api.github.com/repos/bucardo/check_postgres/issues/119/events","html_url":"https://github.com/bucardo/check_postgres/issues/119","id":251189716,"number":119,"title":"Explanation of time_delay= in hot_standby_delay","user":{"login":"majales","id":11310917,"avatar_url":"https://avatars1.githubusercontent.com/u/11310917?v=4","gravatar_id":"","url":"https://api.github.com/users/majales","html_url":"https://github.com/majales","followers_url":"https://api.github.com/users/majales/followers","following_url":"https://api.github.com/users/majales/following{/other_user}","gists_url":"https://api.github.com/users/majales/gists{/gist_id}","starred_url":"https://api.github.com/users/majales/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/majales/subscriptions","organizations_url":"https://api.github.com/users/majales/orgs","repos_url":"https://api.github.com/users/majales/repos","events_url":"https://api.github.com/users/majales/events{/privacy}","received_events_url":"https://api.github.com/users/majales/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-08-18T09:27:52Z","updated_at":"2017-08-19T11:17:14Z","closed_at":null,"body":"Hi,\r\nI have just setup check_postgres plugin in icinga and I came to output which I don't understand\r\n\r\n```\r\ncheck_postgres.pl --action hot_standby_delay  --dbuser=icinga,icinga  --dbhost=master,slave -w 5 -c 10\r\nPOSTGRES_HOT_STANDBY_DELAY OK: DB \"postgres\" (host:10.33.0.23) 0 and 200 seconds | time=0.06s replay_delay=0;1;1 receive-delay=0;1;1  time_delay=200;\r\n```\r\nWhat actually means that \"time_delay=200\" and  \"0 and 200 seconds\" ? It changes (grows) with time.\r\n\r\nI have checked on master that replication should be running in sync mode\r\n```\r\npostgres=# select usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;\r\n usesysid |  usename  | application_name | client_addr |   state   | sent_location | write_location | sync_state\r\n----------+-----------+------------------+-------------+-----------+---------------+----------------+------------\r\n    16384 | repl_user | replica1         | 10.22.0.33  | streaming | 0/23F94C20    | 0/23F94C20     | sync\r\n```\r\n\r\nthis is how my recovery.conf on  slave looks like:\r\n```\r\nstandby_mode = 'on'\r\nprimary_conninfo = 'user=repl_user password=verysecretpass host=10.22.0.13 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres application_name=replica1'\r\nprimary_slot_name = 'standby1'\r\n```\r\n\r\nPlease could someone explain me what it means?"},"comment":{"url":"https://api.github.com/repos/bucardo/check_postgres/issues/comments/323517081","html_url":"https://github.com/bucardo/check_postgres/issues/119#issuecomment-323517081","issue_url":"https://api.github.com/repos/bucardo/check_postgres/issues/119","id":323517081,"user":{"login":"Napsty","id":3702203,"avatar_url":"https://avatars3.githubusercontent.com/u/3702203?v=4","gravatar_id":"","url":"https://api.github.com/users/Napsty","html_url":"https://github.com/Napsty","followers_url":"https://api.github.com/users/Napsty/followers","following_url":"https://api.github.com/users/Napsty/following{/other_user}","gists_url":"https://api.github.com/users/Napsty/gists{/gist_id}","starred_url":"https://api.github.com/users/Napsty/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Napsty/subscriptions","organizations_url":"https://api.github.com/users/Napsty/orgs","repos_url":"https://api.github.com/users/Napsty/repos","events_url":"https://api.github.com/users/Napsty/events{/privacy}","received_events_url":"https://api.github.com/users/Napsty/received_events","type":"User","site_admin":false},"created_at":"2017-08-19T11:17:14Z","updated_at":"2017-08-19T11:17:14Z","body":"I saw you've asked the same question on https://www.claudiokuenzler.com/blog/723/how-to-monitor-postgresql-replication :)\r\n\r\nI went through the source code and time_delay is $time_delta:\r\n\r\n     if ($version >= 9.1) {\r\n            $db->{perf} .= sprintf ' %s=%s;%s;%s',\r\n                perfname(msg('hs-time-delay')), $time_delta, $wtime, $ctime;\r\n     }\r\n\r\ntime_delta is read from xlog positions (line 5110 and below):\r\n\r\n    ## Get xlog positions\r\n    my ($moffset, $s_rec_offset, $s_rep_offset, $time_delta);\r\n\r\n    ## On slave\r\n    $SQL = q{SELECT pg_last_xlog_receive_location() AS receive, pg_last_xlog_replay_location() AS replay}\r\n    if ($version >= 9.1) {\r\n        $SQL .= q{, COALESCE(ROUND(EXTRACT(epoch FROM now() - pg_last_xact_replay_timestamp())),0) AS seconds};\r\n    }\r\n    my $info = run_command($SQL, { dbnumber => $slave, regex => qr/\\// });\r\n    my $saved_db;\r\n    for $db (@{$info->{db}}) {\r\n        my $receive = $db->{slurp}[0]{receive};\r\n        my $replay = $db->{slurp}[0]{replay};\r\n    $time_delta = $db->{slurp}[0]{seconds};\r\n\r\nSo if you run the SQL command you see these \"seconds\":\r\n\r\n    postgres=# SELECT pg_last_xlog_receive_location() AS receive, pg_last_xlog_replay_location() AS replay , COALESCE(ROUND(EXTRACT(epoch FROM now() - pg_last_xact_replay_timestamp())),0) AS seconds;\r\n      receive   |   replay   | seconds \r\n    ------------+------------+---------\r\n     2/266055D8 | 2/266055D8 |  151763\r\n    (1 row)\r\n\r\nSo seconds is defined by the function pg_last_xact_replay_timestamp(), which is, according to the documentation (https://www.postgresql.org/docs/9.4/static/functions-admin.html:\r\n\r\n> Get time stamp of last transaction replayed during recovery. This is the time at which the commit or abort WAL record for that transaction was generated on the primary. If no transactions have been replayed during recovery, this function returns NULL. Otherwise, if recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last transaction applied during that recovery. When the server has been started normally without recovery the function returns NULL.\r\n"}}
{"actor":{"display_login":"Napsty"}}