[[toc]]

问题概述

备库 hot_standby 为 on 模式下，备库会因为查询冲突产生备库查询超时；
解决思路： 在备库调整max_standby_archive_delay 和 max_standby_streaming_delay参数，延长备库日志接受超时时间使查询不会因为复制冲突被终止。

参考文档 – 翻译（pg 16）

Hot Standby

Hot Standby 这个术语用于描述当服务器处于归档恢复或备用模式时连接到服务器并运行只读查询的能力。这对于复制目标端和准确的将备份恢复指定状态都很有用。术语“hot standby”还指服务器在用户继续运行查询或者保持连接打开的情况下从恢复状态转移到正常操作状态的一种能力。

1、使用概述

当备用服务器上的 hot_standby 参数设置为 true 时，一旦系统恢复到一致状态，它将开始接受连接。所有此类连接都是严格只读的，临时表也不能做写入操作。

备用服务器上需要一段时间才能从主服务器接受到传输的数据，因此主服务器和备用服务器之间会存在可测量的延迟。因此，在主数据库和备用数据库上几乎同时运行相同的查询可能会返回不同的结果。我们说备用数据库上的数据最终与主数据库保持一致。一旦事务的提交记录在备用数据库上重放，该事务所做的更改将对备用数据库上拍摄的任何新快照可见。可以在每个查询开始时或每个事务开始时拍摄快照，具体取决于当前事务隔离级别。有关更多详细信息，请参阅第 13.2 节。

热备期间启动的事务可以接受以下命令：

• 查询命令: SELECT, COPY TO
• 声明命令：DECLARE, FETCH, CLOSE
• 参数类：SHOW, SET, RESET
• 事务管理命令：
  • BEGIN, END, ABORT, START TRANSACTION
  • SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT
  • EXCEPTION 块和其他内部子事务
• LOCK TABLE，仅明确处于一下模式之一：ACCESS SHARE, ROW SHARE or ROW EXCLUSIVE.
• 计划和资源：PREPARE, EXECUTE, DEALLOCATE, DISCARD
• 插件和扩展：LOAD
• UNLISTEN

在hot standby期间开始的事务将永远不会分配一个事务ID，也无法写入系统写入日志。因此，以下操作将产生错误消息：

• 数据操作语言 (DML)：INSERT, UPDATE, DELETE, MERGE, COPY FROM, TRUNCATE。请注意，不允许执行任何在恢复期间执行会产生结果的触发器的操作。此限制甚至适用于临时表，因为表内行数据在没有分配事务 ID的清况下无法进行读写，目前在机热备环境下还无法实现。
• 数据定义语言（DDL）：CREATE, DROP, ALTER, COMMENT.此限制甚至适用于临时表，因为执行这些操作需要更新系统目录表
• SELECT … FOR SHARE | UPDATE，因为如果不更新底层数据文件就无法获取行锁
• 生成 DML 命令的 SELECT 语句的规则。
• 显式请求高于 ROW EXCLUSIVE MODE 的模式的 LOCK
• LOCK 采用简短的默认形式，因为它请求访问独占模式。
• 显式设置非只读状态的事务管理命令：
  • BEGIN READ WRITE, START TRANSACTION READ WRITE
  • SET TRANSACTION READ WRITE, SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE
  • SET transaction_read_only = off
• 两阶段提交命令：PREPARE TRANSACTION、COMMIT PREPARED、ROLLBACK PREPARED 因为即使是只读事务也需要在prepare阶段（两阶段提交的第一阶段）写入WAL。
• 序列更新：nextval()、setval()
• LISTEN, NOTIFY

在正常操作中，“只读”事务允许使用 LISTEN 和 NOTIFY，因此热备用会话在比普通只读会话稍微严格的限制下运行。其中一些限制可能会在未来版本中放宽。（当前版本 16）

在热备时，参数transaction_read_only始终为true，不可更改。但只要不尝试修改数据库，热备用期间的连接就会像任何其他数据库连接一样正常运行。如果发生故障转移或切换，数据库将切换到正常处理模式。当服务器更改模式时，会话将保持连接。一旦热备用结束，就可以启动读写事务（甚至从热备用期间就开始的会话）。

用户可以通过发出 SHOW in_hot_standby 来确定其会话的热备用当前是否处于活动状态。 （在 14 之前的服务器版本中，in_hot_standby 参数不存在；旧服务器的可行替代方法是 SHOW transaction_read_only。）此外，一组函数（表 9.92）允许用户访问有关备用服务器的信息。这些允许您编写了解数据库当前状态的程序。这些可用于监视恢复进度，或允许您编写将数据库恢复到特定状态的复杂程序。

2、处理查询冲突

主服务器和备用服务器在很多方面都是松散连接的。主数据库上的操作将对备用数据库产生影响。因此，它们之间可能会产生负面互动或冲突。最容易理解的冲突是性能：如果主数据库上发生巨大的数据负载，那么这将在备用数据库上生成类似的 WAL 记录流，因此备用查询可能会争用系统资源，例如 I/O。

热备用还可能发生其他类型的冲突。这些冲突是硬冲突，因为可能需要取消查询，并且在某些情况下，需要断开会话才能解决它们。为用户提供了多种方法来处理这些冲突。冲突案例包括：

• 在主服务器上采取的访问独占锁（Access Exclusive lock，包括显式 LOCK 命令和各种 DDL 操作）与备用查询中的表访问发生冲突。
• 在主数据库上删除表空间与使用该表空间作为临时工作文件的备用查询会发生冲突。
• 删除主数据库上的数据库与连接到备用数据库上该数据库的会话发生冲突。
• 从 WAL 使用 vacuum清理记录行的应用与备库仍旧需要“看到“已经移除行的事务发生冲突。
• 从 WAL 使用 vacuum清理记录行的应用与查询访问备用数据库上的目标页面之间的冲突，无论要删除的数据是否可见。

在主服务器上，这些情况只会导致等待；并且用户可以选择取消任一冲突的操作。然而，在备用数据库上没有选择：主数据库上已经发生了 WAL 记录的操作，因此备用数据库一定不能无法应用它。此外，允许 WAL 应用程序无限期地等待可能是非常不可取的，因为备用数据库的状态将越来越落后于主数据库的状态。因此，提供了一种机制来强制取消与待应用的WAL记录冲突的备用查询。

问题情况的一个示例是主服务器上的管理员对当前正在备用服务器上查询的表运行 DROP TABLE。显然，如果在备用数据库上应用 DROP TABLE，则备用查询无法继续。如果这种情况发生在主数据库上，则 DROP TABLE 将等待其他查询完成。但是，当 DROP TABLE 在主数据库上运行时，主数据库没有有关备用数据库上正在运行哪些查询的信息，因此它不会等待任何此类备用数据库查询。当备用数据库查询仍在运行时，WAL 更改记录会到达备用数据库，从而导致冲突。备用服务器必须要么延迟 WAL 记录的应用（以及它们之后的所有内容），要么取消冲突的查询，以便可以应用 DROP TABLE。

当冲突查询很短时，通常希望通过稍微延迟 WAL 应用程序来让它完成；但 WAL 应用中的长时间延迟通常是不可取的。因此取消机制有参数 max_standby_archive_delay 和 max_standby_streaming_delay，它们定义了 WAL 应用程序中允许的最大延迟。一旦应用任何新接收到的 WAL 数据的时间超过相关延迟设置，冲突查询将被取消。有两个参数，以便可以为从存档读取 WAL 数据与通过流复制读取 WAL 数据的情况指定不同的延迟值（即，从基本备份进行初始恢复或“追赶”远远落后的备用服务器）。

配置延迟参数可以参照以下标准：

• 在主要为了高可用性而存在的备用服务器中，最好将延迟参数设置得相对较短，这样服务器就不会因为备用查询造成的延迟而远远落后于主服务器。
• 如果备用服务器用于执行长时间运行的查询，则较高甚至无限的延迟值可能会更好。

需要注意，如果长时间运行的查询延迟了 WAL 记录的应用，则可能会导致备用服务器上的其他会话看不到主服务器上最近的更改。

一旦超过 max_standby_archive_delay 或 max_standby_streaming_delay 指定的延迟，冲突的查询将被取消。这通常只会导致取消错误，尽管在重放 DROP DATABASE 的情况下，整个冲突会话将被终止。此外，如果冲突是由空闲事务持有的锁引起的，则冲突会话将终止（此行为将来可能会改变）。

取消的查询可以立即重试（当然需要在开始新事务之后）。由于查询取消取决于重放的 WAL 记录的性质，因此被取消的查询如果再次执行，很可能会成功。

请记住，延迟参数是与备用服务器收到 WAL 数据以来经过的时间进行比较的。因此，备用数据库上的任何一个查询所允许的延迟时间不能超过延迟参数配置时间，并且如果备用数据库由于等待先前查询完成而已经落后，则延迟时间可能会短得多，或者由于无法追平繁重的更新负载导致延迟。

备用查询和 WAL 重放之间发生冲突的最常见原因是“early cleanup”。通常，当没有事务需要查看旧行版本时，PostgreSQL 允许清理旧行版本，以根据 MVCC 规则确保数据的正确可见性。但是，此规则只能应用于在主节点上执行的事务。因此，主数据库上的清理可能会删除对备用数据库上的事务仍然可见的行版本。

行版本清理并不是与备用查询发生冲突的唯一潜在原因。所有index-only scan（包括在备用数据库上运行的扫描）必须使用与可见性视图“一致”的 MVCC 快照。因此，每当 VACUUM 将页面设置为在可见性映射中全部可见时，该页面包含对所有备用查询不可见的一行或多行，就会产生冲突。因此，即使对没有需要清理的更新或删除行的表运行 VACUUM 也可能会导致冲突。

用户应该清楚，在主服务器上定期且大量更新的表将很快导致备用服务器上运行时间较长的查询被取消。在这种情况下，可以将 max_standby_archive_delay 或 max_standby_streaming_delay 值设置合适的值类似设置 statements_timeout。

如果发现备用查询取消的数量不可接受，则存在补救可能性。第一个选项是设置参数 hot_standby_feedback，它可以防止 VACUUM 删除最近死亡的行，因此不会发生清理冲突。如果这样做，您应该注意，这将延迟主数据库上死元组的清理，这可能会导致表膨胀问题。然而，清理情况不会比直接在主服务器上运行备用查询的情况更糟，并且您仍然可以获得将执行卸载到备用服务器上的好处。如果备用服务器频繁连接和断开连接，您可能需要进行调整以处理未提供 hot_standby_feedback 反馈的时期。例如，考虑增加 max_standby_archive_delay ，以便在断开连接期间查询不会因 WAL 归档文件中的冲突而快速取消。您还应该考虑增加 max_standby_streaming_delay 以避免重新连接后新到达的流 WAL 条目快速取消。

查询取消的次数及其原因可以使用备用服务器上的 pg_stat_database_conflicts 系统视图来查看。 pg_stat_database 系统视图还包含摘要信息

用户可以控制当 WAL 重放因冲突而等待时间超过 deadlock_timeout 时是否生成日志消息。这是由 log_recovery_conflict_waits 参数控制的。

3、管理员概述

如果 postgresql.conf 中的 hot_standby 打开（默认值）并且存在standby.signal 文件，则服务器将以热备用模式运行。但是，允许热备用连接可能需要一些时间，因为服务器在完成足够的恢复以提供可以运行查询的一致状态之前不会接受连接。在此期间，尝试连接的客户端将被拒绝并显示错误消息。要确认服务器已启动，请循环尝试从应用程序进行连接，或在服务器日志中查找以下消息：

LOG:  entering standby mode

... then some time later ...

LOG:  consistent recovery state reached
LOG:  database system is ready to accept read-only connections
复制

主数据库上的每个检查点都会记录一次一致性信息。当主服务器上的 wal_level 未设置为replica或logical期间写入的 WAL 时，无法启用热备。在存在以下两种情况的情况下，达到一致状态也可能会延迟：

• 一个写事务有超过64个子事务
• 非常长寿命的写事务

如果您正在运行file-based的日志传送（“热备用”），您可能需要等待下一个 WAL 文件到达，该时间可能与主服务器上的 archive_timeout 设置一样长。

一些参数的设置决定了用于跟踪事务ID、锁和prepared 事务的共享内存的大小。备用数据库上的这些共享内存结构不得小于主数据库上的共享内存结构，以确保备用数据库在恢复期间不会耗尽共享内存。例如，如果主数据库使用了prepared 事务，但备用数据库没有分配任何共享内存来跟踪prepared 事务，则在更改备用数据库的配置之前，恢复无法继续。受影响的参数有：

• max_connections
• max_prepared_transactions
• max_locks_per_transaction
• max_wal_senders
• max_worker_processes

确保这不会成为问题的最简单方法是将备用数据库上的这些参数设置为等于或大于主数据库上的值。因此，如果您想要增加这些值，您应该首先在所有备用服务器上执行此操作，然后再将更改应用到主服务器。相反，如果您想减小这些值，则应首先在主服务器上执行此操作，然后再将更改应用到所有备用服务器。请记住，当升级备用数据库时，它将成为其后的备用数据库所需参数设置的新参考。因此，为了避免这在切换或故障转移期间成为问题，建议在所有备用服务器上保持相同的设置。

WAL 跟踪主节点上这些参数的更改。如果热备机处理的 WAL 表明主备机上的当前值高于其自身值，它将记录警告并暂停恢复，例如：

WARNING:  hot standby is not possible because of insufficient parameter settings
DETAIL:  max_connections = 80 is a lower setting than on the primary server, where its value was 100.
LOG:  recovery has paused
DETAIL:  If recovery is unpaused, the server will shut down.
HINT:  You can then restart the server after making the necessary configuration changes.
复制

此时，需要更新备用数据库上的设置并重新启动实例，然后才能继续恢复。如果备用机不是热备机，那么当它遇到不兼容的参数更改时，它会立即关闭而不会暂停，因为没有把备库维持下去的价值。

管理员必须为max_standby_archive_delay和max_standby_streaming_delay选择适当的设置。最佳选择因业务优先级而异。例如：

• 如果服务器主要作为高可用性服务器（OLTP），那么需要设置较低的值，甚至可能为零，尽管这是一个非常激进的设置。
• 如果备用服务器的任务是作为决策支持查询的附加服务器（OLAP），则可以接受将最大延迟值设置为数小时，甚至 -1，这意味着永远等待查询完成。

主数据库上写入的事务状态“hint bits”不会记录 WAL，因此备用数据库上的数据可能会在备用数据库上再次重写hint。因此，即使所有用户都是只读的，备用服务器仍将执行磁盘写入;数据值本身不会发生任何更改。用户仍将写入大型排序临时文件并重新生成 relcache 信息文件，因此在热备用模式下，数据库的任何部分都不是真正只读的。另请注意，即使事务在本地是只读的，使用 dblink 模块写入远程数据库以及使用 PL 函数在数据库外部执行的其他操作也是可能的。

在恢复模式下不接受以下类型的管理命令：

• 数据定义语言 （DDL）：例如 CREATE INDEX
• 权限和归属：GRANT, REVOKE, REASSIGN
• 维护命令：ANALYZE, VACUUM, CLUSTER, REINDEX

同样，请注意，在主数据库上的“只读”模式事务期间，实际上允许使用其中一些命令。

因此，您不能创建仅存在于备用数据库上的其他索引，也无法创建仅存在于备用数据库上的统计信息。如果需要这些管理命令，则应在主服务器上执行这些命令，最终这些更改将传播到备用服务器。

pg_cancel_backend（） 和 pg_terminate_backend（） 将在用户后端工作，但不适用于执行恢复的启动过程。pg_stat_activity 不会将正在恢复的事务显示为活动。因此，在恢复过程中pg_prepared_xacts始终是空的。如果您希望解决有问题的prepared事务，请查看主事务上的pg_prepared_xacts，并发出命令以解决那里的事务或在恢复结束后解决它们。

pg_locks 将像平常一样显示后端持有的锁。 pg_locks 还显示了一个由启动进程管理的虚拟事务，该进程拥有由恢复重播的事务所持有的所有 AccessExclusiveLocks。请注意，启动进程不会获取锁来进行数据库更改，因此除 AccessExclusiveLocks 之外的锁不会显示在启动进程的 pg_locks 中；他们只是被假定存在

Nagios 插件 check_pgsql 可以工作，因为它检查的简单信息是存在的。 check_postgres 监控脚本也将起作用，尽管某些报告的值可能会给出不同或令人困惑的结果。例如，由于备用数据库上没有发生vacuum，因此不会保留上次vacuum时间。在主服务器上运行的 Vacuum 仍然会将其更改发送到备用服务器。

WAL 文件控制命令在恢复期间将不起作用，例如 pg_backup_start、pg_switch_wal 等。

动态可加载模块可以工作，包括 pg_stat_statements。

咨询锁在恢复时正常工作，包括死锁检测。请注意，咨询锁永远不会记录 WAL，因此主数据库或备用数据库上的咨询锁不可能与 WAL 重播发生冲突。也不可能在主数据库上获取咨询锁并让它在备用数据库上启动类似的咨询锁。咨询锁仅与获取它们的服务相关。

基于触发器的复制系统（例如 Slony、Londiste 和 Bucardo）根本不会在备用服务器上运行，但只要更改不发送到备用服务器进行应用，它们就会在主服务器上正常运行。 WAL 重播不是基于触发器的，因此您无法从备用系统中继到任何需要额外数据库写入或依赖于触发器使用的系统。

无法分配新的 OID，但某些 UUID 生成器只要不依赖于将新状态写入数据库就仍然可以工作。

目前，在只读事务期间不允许创建临时表，因此在某些情况下现有脚本将无法正确运行。此限制可能会在以后的版本中放宽。这既是一个 SQL 标准合规性问题，也是一个技术问题。

仅当表空间为空时，DROP TABLESPACE 才能成功。一些备用用户可能通过其 temp_tablespaces 参数主动使用表空间。如果表空间中有临时文件，则取消所有活动查询以确保临时文件被删除，因此表空间可以被删除并且WAL重放可以继续。

在主数据库上运行 DROP DATABASE 或 ALTER DATABASE … SET TABLESPACE 将生成一个 WAL 条目，该条目将导致连接到备用数据库上该数据库的所有用户被强制断开连接。无论 max_standby_streaming_delay 的设置如何，此操作都会立即发生。请注意，ALTER DATABASE … RENAME 不会断开用户连接，在大多数情况下不会被关注到，但在某些情况下，可能会导致以某种方式依赖于数据库名称的程序发生异常。

在正常（非恢复）模式下，如果您对具有登录功能的角色发出 DROP USER 或 DROP ROLE，而该用户仍处于连接状态，则连接的用户不会发生任何事情 — 他们仍保持连接状态。但是用户无法重新连接。此行为也适用于恢复，因此主数据库上的 DROP USER 不会断开备用数据库上的该用户的连接。

累积统计系统在恢复期间处于活动状态。所有扫描、读取、块、索引使用等（scans, reads, blocks, index usage,），都将正常记录在备用数据库上。但是，WAL 重播不会增加对象和数据库特定的计数器。 例如。 replay 不会增加 pg_stat_all_tables 列（如 n_tup_ins），也不会在 pg_statio 视图中跟踪启动进程执行的读取或写入，也不会增加关联的 pg_stat_database 列。

恢复期间Autovacuum处于非活动状态。它将在恢复结束时正常启动。

检查点进程和后台写入进程在恢复期间处于活动状态。检查点进程将执行重启检查点（类似于主节点上的检查点），后台写入进程将执行正常的块清理活动。这可以包括存储在备用服务器上的hint位信息的更新。 CHECKPOINT 命令在恢复期间被接受，尽管它执行重启检查点而不是一个新的检查点。

4、热备参数参考

• 上述章节提及的参数
• 在主库上，可以使用 wal_level 参数。max_standby_archive_delay 和 max_standby_streaming_delay 如果在主服务器上设置则无效。
• 在备用服务器上，可以使用参数 hot_standby、max_standby_archive_delay 和 max_standby_streaming_delay。

5、需要注意的事项

热备有几个限制。这些可以而且很可能会在未来的版本中得到修复：

• 在拍摄快照之前，需要充分了解正在运行的事务。使用大量子事务（当前大于 64）的事务将延迟只读连接的启动，直到运行时间最长的写入事务完成。如果发生这种情况，解释消息将发送到服务器日志。
• 备用查询的有效起始点是在主数据库上的每个检查点生成的。如果在主数据库处于关闭状态时关闭备用数据库，则在主数据库启动之前可能无法重新进入热备用状态，从而在 WAL 日志中生成进一步的起始点。在可能发生的最常见情况下，这种情况并不是问题。通常，如果主数据库关闭且不再可用，则可能是由于严重故障导致，无论如何都需要将备用数据库转换为新的主数据库。在有意关闭主数据库的情况下，进行协调以确保备用数据库顺利成为新的主数据库也是标准程序。
• 在恢复结束时，prepared事务持有的 AccessExclusiveLock 将需要两倍于正常数量的lock table entries。如果您计划运行通常需要 AccessExclusiveLocks 的大量并发prepare事务，或者您计划有一个需要许多 AccessExclusiveLocks 的大型事务，建议您选择较大的 max_locks_per_transaction 值，可能是主服务器上max_locks_per_transaction参数值的两倍。如果您的 max_prepared_transactions 设置为 0，则根本不需要考虑这一点。
• Serialized 事务隔离级别在热备中尚不可用。尝试在热备用模式下将事务设置为Serializable隔离级别将生成错误。