怎么在Postgresql数据库中检查主从复制的进度 - 行业资讯 - 肥雀云

　　介绍

怎么在Postgresql数据库中检查主从复制的进度?很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。

查看流复制的信息可以使用主库上的视图

select pid,状态,client_addr, sync_priority, sync_state 得到pg_stat_replication;

pg_stat_replication中几个字断记录了发送的wal的位置及备库接收到的细胞膜的位置,

sent_location——发送的wal的位置

write_location——备库接收到的细胞膜的位置

flush_location -备库写的wal日志到磁盘的位置

replay_location -备库应用日志的位置

查看备库落后主库多少字节

select pg_xlog_location_diff (pg_current_xlog_location (), replay_location)/1024/1024 as MB 得到pg_stat_replication; 　　select pg_xlog_location_diff (pg_current_xlog_location (), replay_location)/1024/1024/1024 as GB 得到pg_stat_replication;

级联复制

select pg_xlog_location_diff (pg_last_xlog_replay_location (), replay_location)/1024/1024/1024 as GB 得到pg_stat_replication;

<强>补充:pgsql之查看主备复制延迟

查看复制延迟:

<强> 10.0及以上:

select 　　pg_wal_lsn_diff才能(A .c1, replay_lsn),/(1024, *, 1024), AS slave_latency_MB, 　　pg_wal_lsn_diff才能(A .c1, sent_lsn),/(1024, *, 1024), AS send_latency_MB, 　　pg_wal_lsn_diff才能(A .c1, flush_lsn),/(1024, *, 1024), AS flush_latency_MB, 　　状态,才能　　backend_start才能, 　　现在,,()::timestamp with time 区　　,得到pg_stat_replication, pg_current_wal_lsn (), AS (c1) 　　,WHERE client_addr=& # 39; 192.168.46.173& # 39;,以及application_name =, & # 39; standby1& # 39; 　　,ORDER BY slave_latency_MB send_latency_MB DESC 　　,LIMIT 1;

注:

192.168.46.173表示从库ip地址。

pg_wal_lsn_diff (lsn pg_lsn lsn pg_lsn):计算两个预写式日志位置间的差别。

pg_current_wal_lsn():获得当前预写式日志写入位置

<强>小于10.0版本:

SELECT 　　pg_xlog_location_diff才能(A .c1, replay_lsn),/(1024, *, 1024), AS slave_latency_MB, 　　pg_xlog_location_diff才能(A .c1, sent_lsn),/(1024, *, 1024), AS send_latency_MB, 　　pg_xlog_location_diff才能(A .c1, flush_lsn),/(1024, *, 1024), AS flush_latency_MB, 　　状态,才能　　backend_start才能, 　　现在,,()::timestamp with time 区　　,得到pg_stat_replication, pg_current_xlog_location AS (c1) 　　,WHERE client_addr=& # 39; 192.168.46.173& # 39;,以及application_name =, & # 39; standby1& # 39; 　　,ORDER BY slave_latency_MB send_latency_MB DESC 　　,LIMIT 1;

注:

192.168.46.173表示从库ip地址。

pg_xlog_location_diff (lsn pg_lsn lsn pg_lsn):计算两个预写式日志位置间的差别。

pg_current_xlog_location():获得当前预写式日志写入位置

看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注行业资讯频道,感谢您对的支持。