PostgreSQL 主从差异查看

最新推荐文章于 2024-09-16 00:37:08 发布

weixin_34309543

最新推荐文章于 2024-09-16 00:37:08 发布

阅读量893

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://my.oschina.net/aven92/blog/486496

本文提供了两种方法来检查PostgreSQL主从数据库之间的同步延迟情况。第一种方法通过比较主从库的数据大小差异来评估延迟，第二种方法则直接测量时间上的差异。这些技巧对于维护数据库系统的高效运行至关重要。

为什么80%的码农都做不了架构师？>>>

一、大小差异

在主库上执行

select
        application_name,
        pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), replay_location)) as diff
from
        pg_stat_replication;

或者：

select
	application_name,
	client_addr,
	cur_xlog || '/' || cur_offset as cur_xlog,  
	sent_xlog || '/' || sent_offset as sent_xlog,     	     
	replay_xlog || '/' || replay_offset as replay_xlog,
	pg_size_pretty(( ((cur_xlog * 255 * 16 ^ 6) + cur_offset) - ((sent_xlog * 255 * 16 ^ 6) + sent_offset) )::numeric) as master_lag,         
	pg_size_pretty(( ((sent_xlog * 255 * 16 ^ 6) + sent_offset) - ((replay_xlog * 255 * 16 ^ 6) + replay_offset) )::numeric) as slave_lag,                                                                  
	pg_size_pretty(( ((cur_xlog * 255 * 16 ^ 6) + cur_offset) - ((replay_xlog * 255 * 16 ^ 6) + replay_offset) )::numeric) as total_lag
from (
	select
		application_name,
		client_addr,
		('x' || lpad(split_part(sent_location::text,'/', 1), 8, '0'))::bit(32)::bigint as sent_xlog,
		('x' || lpad(split_part(replay_location::text, '/', 1), 8, '0'))::bit(32)::bigint as replay_xlog,
		('x' || lpad(split_part(sent_location::text, '/', 2), 8, '0'))::bit(32)::bigint as sent_offset,
		('x' || lpad(split_part(replay_location::text, '/', 2), 8, '0'))::bit(32)::bigint as replay_offset,
		('x' || lpad(split_part(pg_current_xlog_location()::text, '/', 1), 8, '0'))::bit(32)::bigint as cur_xlog,
		('x' || lpad(split_part(pg_current_xlog_location()::text, '/', 2), 8, '0'))::bit(32)::bigint as cur_offset
	from
		pg_stat_replication
) as s;

二、时间差异

在从库上执行：

select now() - pg_last_xact_replay_timestamp() as replication_delay;

转载于:https://my.oschina.net/aven92/blog/486496