PostgreSQL流复制停止更新

Question

我有四个PostgreSQL 9.5实例运行在EC2上，使用的是m4.8x大型实例，在RAID0设置中有五个PIOPS，还有一个单独的XLOG驱动器。直到今天早上，我从来没有超过一到两分钟的复制滞后，但现在复制完全失败，在所有的情况下，大约30分钟。

重新启动Postgres解决了另一个半小时的问题。

没有CPU争用，iowait通常小于1%。阻止对服务器的读取，认为它可能会不堪重负，什么也不做。我不知道这里的问题是什么，除了亚马逊的一些东西。

有人能告诉我如何解决这个问题吗？日志中没有任何内容，replay_location (来自pg_stat_replication)只是停止更新，直到我重新启动奴隶。

Answer 1

DR:不要在配置中禁用max_standby_streaming_delay。

为了其他人寻找这个问题的答案，我想我应该回答我自己的问题，因为通过Google提供的关于这个话题的信息非常少。

深入研究后，我发现了一本名为“疑难解答PostgreSQL：https://www.packtpub.com/big-data-and-business-intelligence/troubleshooting-postgresql”的书。

它以电子书的形式出现，对帮助我追踪我所经历的问题很有用。它描述了使用一个名为pg_view的工具，该工具对PostgreSQL服务器中发生的事情有很多非常有用的实时度量。

在停止复制的服务器上运行它表明，在长时间运行查询时出现了问题：

db.server.com up 21:34:25 36 cores Linux 4.4.5-15.26.amzn1.x86_64 load average 1.79 0.94 0.47                                                                                                                                               08:58:29
 sys: utime 5.7   stime 0.1   idle 94.3  iowait 0.0   ctxt 580 run 4   block 0
 mem: total 59.0GB free 2.3GB  buffers 41.0MB cached 55.0GB dirty 692KB  limit 29.5GB as 14.7GB left 14.8GB
 /var/lib/pgsql/9.5/data 9.5.2 standby connections: 5 of 500 allocated, 2 active
 type dev     fill   total    left   read  write    await path_size path
 data md127    0.0 499.7GB 382.8GB    0.0    0.1      0.0   116.6GB /var/lib/pgsql/9.5/data
 xlog xvdg     0.0 100.0GB  84.0GB    0.0    0.6     15.7    15.9GB /var/lib/pgsql/9.5/data/pg_xlog
   pid type    s utime stime guest read write   age  uss db   user     query
 26644 backend R 100.2   0.0   0.0  0.0   0.0 03:21 93.4 data postgres WITH query AS ( WITH original_transaction_id AS( SELECT original_transaction_id FROM table WHERE...
 26645 backend R  99.3   0.0   0.0  0.0   0.0 00:01 93.8 data postgres WITH query AS( SELECT COUNT(id) AS total, original_transaction_id FROM table GROUP BY original_tr...

其中的键是显示长时间运行的查询的age列。然而，秘诀是通过按下“S”热键来显示服务器上的所有进程，这在pg_view GitHub页面中没有描述。这显示了系统进程，包括启动和WAL接收器：

   pid type            s utime stime guest read write   age  uss db   user     query
 12169 startup         S   0.0   0.0   0.0  0.0   0.0        1.2               recovering 000000010000070A0000006D...
 12170 checkpointer    S   0.0   0.0   0.0  0.0   1.9        1.2               ...
 12171 writer          S   0.0   0.0   0.0  0.0   0.0        1.0               ...
 12172 stats collector S   0.0   0.0   0.0  0.0   0.0        1.2               ...
 12178 wal receiver    S   0.0   1.0   0.0  0.0   1.1        1.2               streaming 70A/6D998000...

有用的信息在startup行上，上面写着recovering。这是正常的，因为它是一个只读的从，技术上总是恢复新的数据通过wal接收器。

问题是当它在该行的末尾有waiting时--这告诉您，服务器正等待一个长时间运行的查询来释放它需要更新的表上的锁，在此之前，复制会停止，或者到达max_standby_streaming_delay值。

默认情况下，max_standby_streaming_delay设置为30 is。但是，在我的服务器上禁用了它，可能是为了提高复制的可靠性：

max_standby_streaming_delay = -1

从配置文件中删除该行并重新加载，修复了前面的问题。

另一个结果是，我们将所有长期运行的查询转移到一个单独的副本中，只使用我的这些后端进程来帮助实现长期的可靠性，并避免对面向客户的数据库进行可能的表锁。

大多数故障排除指南都说复制问题与网络有关，但在这种情况下，这是一个配置错误和格式错误的SQL查询。