postgresql流复制四（查询冲突）

部署流复制环境后，备库可提供只读操作，通常会将一些执行时间较长的分析任务、统计 SQL 跑在备库上，从而减轻主库压力，在备库上执行一些长时间 SQL 时，可能会出现以下错误并被中止：

FATAL: terminating connection due to conflict with recovery
DETAIL: User was holding a relation lock for too long.

根据报错信息，在主库上执行长时间查询过程中，由于此查询涉及的记录有可能在主库上被更新或删除，根据 PostgreSQL MVCC 机制，更新或删除的数据不是立即从物理块上删除，而是之后 autovacuum 进程对老版本数据进行 VACUUM ，主库上对更新或删除数据的老版本进行 VACUUM 后，从库上也会执行这个操作，从而与从库当前查询产生冲突，导致查询被中断并抛出以上错误。

实际上 PostgreSQL 提供了配置参数来减少或避免这种情况出现的概率，主要包括以下两个参数：

• max_standby_streaming_delay：此参数默认为 30 秒，当备库执行 SQL ，有可能与正在应用的 WAL 发生冲突，此查询如果 30 秒没有执行完成则被中止，注意 30 秒不是备库上单个查询允许的最大执行时间，是指当备库上应用 WAL 时允许的最WAL 延迟应用时间，因此备库上查询的执行时间有可能不到这个参数设置的值就被中止了，此参数可以设置成为 -1，表示当从库上的 WAL 应用进程与从库上执行的查询冲突时， WAL 应用进程一直等待直到从库查询执行完成。
• hot_standby_feedback：默认情况下从库执行查询时并不会通知主库，设置此参数为 on 后从库执行查询时会通知主库，当从库执行查询过程中，主库不会清理从库需要的数据行老版本，因此，从库上的查询不会被中止，然而，这种方法也会带来一定的弊端，主库上的表可能出现膨胀，主库表的膨胀程度与表上的写事务和从库上大查询的执行时间有关，此参数默认为 off。

接下来模拟这一案例，测试环境为一主一备异步流复制环境， host1 为主库， host2为备库，调整备库 postgresql.conf 以下参数：

max_standby_streaming_delay = 10s       # max delay before canceling queries

为了测试方便，将 max_standby_ streaming_ delay 参数降低到 10 秒，调整完成后执行 reload 使配置生效，如下所示：

pg_ctl reload

创建一张表test_delay，并插入1条数据，如下所示：

postgres=# create table test_delay(id int);
CREATE TABLE
postgres=# insert into test_delay select generate_series(1,1000);
INSERT 0 1

在备库上开启一个事务，执行以下查询， 如下所示：

postgres=# begin;
BEGIN
postgres=# select * from test_delay limit 1;
 id
----
  1
(1 row)

在主库truncate这张表，如下所示：

postgres=# truncate table test_delay;
TRUNCATE TABLE

过了大概10秒中，备库报错：

FATAL:  terminating connection due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

有两种方式可以避开这一错误。

方式一：调大 max_standby_streaming_delay 参数值

由于设置 max_standby_streaming_delay 数为 10 秒， 从库上执行查询与从库应用WAL 日志产生冲突时，此 SQL 或事务最多执行到 10 秒左右将被中止，因此可以将此参数值调大或调整成为 -1 绕开这一错误，以下将备库此参数调成60秒：

max_standby_streaming_delay = 60s       # max delay before canceling queries
hot_standby_feedback = off             # send info from standby to prevent

同时将 hot_standby_ feedback 参数设置为 off ，调整完成后执行 reload 使配置生效，如下所示：

pg_ctl reload

方式二：开启 hot_standby_feedback 参数

hot_standby_feedback 参数设置成 on 后，备库执行查询时会通知主库，在备库执行查询过程中 ，主库不会清理从库需要用的数据行老 版本，备库上开启此参数的代码如下：

max_standby_streaming_delay = 10s       # max delay before canceling queries
hot_standby_feedback = on             # send info from standby to prevent

以上设置 hot_standby_feedback 参数为 on ，同时将 max_standby_streaming_deay 参数设置 10 秒，调整完 reload 使配置生效，如下所示：

pg_ctl reload

以上两种方式都可以绕开这一错误，方式一中设置 max_standby_streaming_delay 参数为 -1 有可能造成备库上慢查询由于长时间执行而消耗大量主机资源，建议根据应用情况设成一个较合理的值；方式二开启 hot_standby_feedback 参数可能会使主库某些表产生膨胀，两种方式无论选择哪一种都应该加强对流复制主库、备库慢查询的监控，并分析是否需人工介入维护。