postgresqlモニタstream replicationの遅延状況

10
いくつかの名前が変更され、xlog=>wal、location=>lsn

pg_xlog_location_diff            pg_wal_lsn_diff

pg_current_xlog_insert_location  pg_current_wal_insert_lsn
pg_current_xlog_location         pg_current_wal_lsn
pg_current_xlog_flush_location   pg_current_wal_flush_lsn

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 17836
usesysid         | 16674
usename          | replicator
application_name | walreceiver
client_addr      | 192.168.56.101
client_hostname  | 
client_port      | 12955
backend_start    | 2018-03-01 17:03:29.129844+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/4CCFB4B8
write_lsn        | 0/4CCFB4B8
flush_lsn        | 0/4CCFB4B8
replay_lsn       | 0/4CCFB4B8
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async

プライマリ・ライブラリ・クエリー・ストリームのレプリケーションの遅れたバイト数は、主にreplay_を参照してください.delay数!メインライブラリpostgresスーパーユーザーでpostgresライブラリに接続します.pg_current_wal_insert_lsn()wal bufferの位置pg_を書き込むcurrent_wal_lsn()walファイルを書き込む場所

select client_addr, 
       pg_wal_lsn_diff(pg_current_wal_insert_lsn(), pg_current_wal_lsn() ) as local_noflush_delay,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) as local_sent_delay,
       pg_wal_lsn_diff(sent_lsn, write_lsn) as stream_write_delay,
       pg_wal_lsn_diff(sent_lsn, flush_lsn) as stream_flush_delay,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) as stream_replay_delay 
from pg_stat_replication
;

次のように実行します.

postgres=# select client_addr, 
        pg_wal_lsn_diff(pg_current_wal_insert_lsn(), pg_current_wal_lsn() ) as local_noflush_delay,
        pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) as local_sent_delay,
        pg_wal_lsn_diff(sent_lsn, write_lsn) as stream_write_delay,
        pg_wal_lsn_diff(sent_lsn, flush_lsn) as stream_flush_delay,
        pg_wal_lsn_diff(sent_lsn, replay_lsn) as stream_replay_delay 
 from pg_stat_replication;
 
-[ RECORD 1 ]-------+------------
client_addr         | 192.168.56.101
local_noflush_delay | 0
local_sent_delay    | 0
stream_write_delay  | 0
stream_flush_delay  | 0
stream_replay_delay | 0

9.6

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 8467 # sender   
usesysid         | 44673 #      id
usename          | replica #         
application_name | walreceiver 
client_addr      | 10.12.12.12 #         
client_hostname  |
client_port      | 55804 #         
backend_start    | 2015-05-12 07:31:16.972157+08 #          
backend_xmin     |
state            | streaming #      startup:    、catchup:    、streaming:   
sent_location    | 3/CF123560 # Master  WAL   
write_location   | 3/CF123560 # Slave  WAL   (     )
flush_location   | 3/CF123560 # Slave      WAL  (     )
replay_location  | 3/CF123560 # Slave       WAL  (      )
sync_priority    | 0 #  Replication    ?
                   0:   、1～?:   (         )
sync_state       | async #     ，
                   async:   、
                   sync:   、
                   potential:          ，            
                   

–プライマリ・ライブラリ・クエリー・ストリームのレプリケーションの遅れたバイト数は、主にreplay_を参照してください.delay数!–メインライブラリpostgresスーパーユーザーでpostgresライブラリに接続します.–pg_xlog_location_diffは他のライブラリではなくpostgresライブラリに構築されました!–pg_xlog_location_diffの単位はbyte

select client_addr,
       pg_xlog_location_diff(pg_current_xlog_insert_location(), pg_current_xlog_location() ) as insert_local_delay,
       pg_xlog_location_diff(pg_current_xlog_location(), pg_current_xlog_flush_location() ) as local_flush_delay,
       pg_xlog_location_diff(pg_current_xlog_insert_location(), sent_location) as insert_sent_delay,
       pg_xlog_location_diff(pg_current_xlog_flush_location(), sent_location) as flush_sent_delay,
       pg_xlog_location_diff(sent_location, write_location) as stream_write_delay,
       pg_xlog_location_diff(sent_location, flush_location) as stream_flush_delay,
       pg_xlog_location_diff(sent_location, replay_location) as stream_replay_delay 
from pg_stat_replication;

–stream一時停止、判断、起動

select pg_xlog_replay_pause() as replay_pause; 
select pg_is_xlog_replay_paused() as is_replay_paused;
select pg_xlog_replay_resume() as replay_resume ;

-HotStandbyでは、次の関数を実行して、バックアップが受信したWALログとアプリケーションWALログのステータスを表示することもできます:pg_last_xlog_receive_location() pg_last_xlog_replay_location() pg_last_xact_replay_timestamp()は次のとおりです.

select pg_last_xlog_receive_location(),
       pg_last_xlog_replay_location(),
       pg_last_xact_replay_timestamp(), 
       clock_timestamp() ,
       clock_timestamp() - pg_last_xact_replay_timestamp() AS replication_delay;
 
-[ RECORD 1 ]-----------------+------------------------------
pg_last_xlog_receive_location | BAA/393C71D0
pg_last_xlog_replay_location  | BAA/393C71D0
pg_last_xact_replay_timestamp | 2017-07-24 09:12:43.701454+08
replication_delay             | -00:00:05.58923
(1 row)
 

またはクエリーpg_stat_wal_receiver

select * from pg_stat_wal_receiver;

slave遅延時間の大まかな判断

#!/bin/bash

PGSQLHOST=127.0.0.1
PGSQLPORT=5432
PGSQLDATABASE=postgres
PGSQLUSER=postgres
PGSQLRESULTFILE=/tmp/tempCheckStreamRepliationDelay.txt


sql="select coalesce(extract(epoch FROM (clock_timestamp() - (select last_msg_receipt_time from pg_stat_wal_receiver ))),0::int4)"
result=""

result=`psql -h $PGSQLHOST -U $PGSQLUSER -d $PGSQLDATABASE -Atqc "$sql" `

echo "`date +%Y%m%d%H%M%S` $result" >> $PGSQLRESULTFILE

リファレンスhttps://www.postgresql.org/docs/10/static/functions-admin.html