如何检查PostgreSQL中的复制延迟？ [英] How to check the replication delay in PostgreSQL?

问题描述

我想使用PostgreSQL 9.3中的流复制来测量将数据插入主表和从表之间的时间。为此，我创建了具有两个字段id（serial），t（text）的表 test_time 。之后添加了一个触发器：

I would like to measure time between insert data into master-table and slave-table using streaming replication in PostgreSQL 9.3. For this I create table test_time with 2 fields id(serial), t(text). After that added a trigger:

cur_time：= to_char（current_timestamp，‘HH12：MI：SS：MS：US’）;
更新test_time设置为t = cur_time，其中id = new.id;

但是两个表中的时间相同。
如何测量延迟时间

But the time is the same in both tables. How can I measure delay time

推荐答案

您可以从中获取延迟以字节为单位主机方很容易使用 pg_xlog_location_diff 比较主机的 pg_current_xlog_insert_location 与 replay_location pg_stat_replication 条目的c $ c>。

You can get the delay in bytes from the master side quite easily using pg_xlog_location_diff to compare the master's pg_current_xlog_insert_location with the replay_location for that backend's pg_stat_replication entry.

这仅在主服务器上运行时有效。您无法从副本服务器上执行此操作，因为副本服务器不知道主服务器有多远。

This only works when run on the master. You can't do it from the replica because the replica has no idea how far ahead the master is.

此外，这不会告诉您的延迟秒。在当前的PostgreSQL版本（至少为9.4）中，没有与提交或WAL记录相关的时间戳。因此，无法得知给定的LSN（xlog位置）是多久之前的。

Additionally this won't tell you the lag in seconds. In current (as of 9.4 at least) PostgreSQL versions there's no timestamp associated with a commit or a WAL record. So there's no way to tell how long ago a given LSN (xlog position) was.

在当前PostgreSQL版本中，获得复制延迟的秒数的唯一方法是：让外部进程定期将更新提交到专用时间戳表。因此，您可以将副本上的 current_timestamp 与副本上该表中可见表中最新条目的时间戳进行比较，以了解副本落后多少。这样会创建额外的WAL流量，然后必须将其保留在PITR（PgBarman或其他工具）的存档WAL中，因此您应该在增加的数据使用量与所需的延迟检测粒度之间取得平衡。

The only way to get the replica lag in seconds on a current PostgreSQL version is to have an external process commit an update to a dedicated timestamp table periodically. So you can compare current_timestamp on the replica to the timestamp of the most recent entry in that table visible on the replica to see how far the replica is behind. This creates additional WAL traffic that will then have to be kept in your archived WAL for PITR (PgBarman or whatever), so you should balance the increased data use with the granularity of lag detection you require.

PostgreSQL 9.5可能会添加提交时间戳记，希望可以让您查明一次给定提交发生的时间，以及因此而得出的副本在墙上时钟秒之后的时间。

PostgreSQL 9.5 may add commit timestamps that will hopefully let you find out how long ago a given commit happened and therefore how far a replica is behind in wall-clock seconds.

这篇关于如何检查PostgreSQL中的复制延迟？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！