为什么备库某些参数必须比主库大？
xiongcc约 11 分钟前言
最近实际生产中碰到这么一个场景：基于成本的考虑，BU要使用低配同城，即备库的硬件配置要比主库低一下，比如主库8C8G，那么备库可能只有4C4G，因为大多数没有读写分离的场景，备库仅仅是做一个灾备，那么数据库参数也需要等比例进行调整。但是在调整参数的时候碰到了一点问题，报错如下
FATAL: recovery aborted because of insufficient parameter settings
DETAIL: max_connections = 99 is a lower setting than on the primary server, where its value was 100.
为什么备库上的max_connections必须要比主库大？之前只是知道有这个限制，但并没有分析为什么必须这样。
分析
官网上也确实写明了备库的设置必须大于主库
max_connections(integer)Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.
When running a standby server, you must set this parameter to the same or higher value than on the primary server. Otherwise, queries will not be allowed in the standby server.
类似的，还有一些参数也有此要求。见 https://www.postgresql.org/docs/14/hot-standby.html
max_prepared_transactions max_locks_per_transaction max_wal_senders max_worker_processes
不妨思考一下，为什么有这个限制？以上五个参数可以看到都是postmaster级别，调整后均需要重启才能生效
postgres=# select name,context from pg_settings where name in ('max_connections','max_prepared_transactions','max_locks_per_transaction','max_wal_senders','max_worker_processes');
name | context
---------------------------+------------
max_connections | postmaster
max_locks_per_transaction | postmaster
max_prepared_transactions | postmaster
max_wal_senders | postmaster
max_worker_processes | postmaster
(5 rows)
首先看看报错的位置，报错的代码位于RecoveryRequiresIntParameter处，比较传入的两个参数大小，注意这里有一个SetRecoveryPause()函数，等会后文会提及。
/*
* Note that text field supplied is a parameter name and does not require
* translation
*/
static void
RecoveryRequiresIntParameter(const char *param_name, int currValue, int minValue)
{
if (currValue < minValue)
{
if (LocalHotStandbyActive)
{
bool warned_for_promote = false; ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("hot standby is not possible because of insufficient parameter settings"),
errdetail("%s = %d is a lower setting than on the primary server, where its value was %d.",
param_name,
currValue,
minValue)));
SetRecoveryPause(true);
正如前方所说，代码里写的很清晰，总共5个参数
/*
* Check to see if required parameters are set high enough on this server
* for various aspects of recovery operation.
*
* Note that all the parameters which this function tests need to be
* listed in Administrator's Overview section in high-availability.sgml.
* If you change them, don't forget to update the list.
*/
static void
CheckRequiredParameterValues(void)
{
/*
* For archive recovery, the WAL must be generated with at least 'replica'
* wal_level.
*/
if (ArchiveRecoveryRequested && ControlFile->wal_level == WAL_LEVEL_MINIMAL) ---流复制要去wal_level必须大于等于replica
{
ereport(FATAL,
(errmsg("WAL was generated with wal_level=minimal, cannot continue recovering"),
errdetail("This happens if you temporarily set wal_level=minimal on the server."),
errhint("Use a backup taken after setting wal_level to higher than minimal.")));
} /*
* For Hot Standby, the WAL must be generated with 'replica' mode, and we
* must have at least as many backend slots as the primary.
*/
if (ArchiveRecoveryRequested && EnableHotStandby) ---确保这五个参数必须大于等于主库
{
/* We ignore autovacuum_max_workers when we make this test. */
RecoveryRequiresIntParameter("max_connections",
MaxConnections,
ControlFile->MaxConnections);
RecoveryRequiresIntParameter("max_worker_processes",
max_worker_processes,
ControlFile->max_worker_processes);
RecoveryRequiresIntParameter("max_wal_senders",
max_wal_senders,
ControlFile->max_wal_senders);
RecoveryRequiresIntParameter("max_prepared_transactions",
max_prepared_xacts,
ControlFile->max_prepared_xacts);
RecoveryRequiresIntParameter("max_locks_per_transaction",
max_locks_per_xact,
ControlFile->max_locks_per_xact);
}
}
官网上虽然也有解释，但是比较泛，十分不好理解
The settings of some parameters determine the size of shared memory for tracking transaction IDs, locks, and prepared transactions. These shared memory structures must be no smaller on a standby than on the primary in order to ensure that the standby does not run out of shared memory during recovery.
一些参数的设置决定了用于跟踪事务ID、锁和预备事务的共享内存的大小。备库上的这些共享内存结构必须不小于主库上的结构，以确保备库在恢复期间不会耗尽共享内存。
不过此处提到了一个tracking transaction ID，这让我联想到了之前在总结子事务的时候涉及到的KnownAssignedTransactionIds，因为在流复制模式下，假如是同步模式的话，子事务提交并不需要等待备库的答复
Read-only transactions and transaction rollbacks need not wait for replies from standby servers. Subtransaction commits do not wait for responses from standby servers, only top-level commits. Long running actions such as data loading or index building do not wait until the very final commit message. All two-phase commit actions require commit waits, including both prepare and commit.
同样的，备库如何确定子事务的可见性呢？见如下例子
BEGIN;
INSERT INTO test VALUES(111,111111); => a heap_redo WAL record having t_xmin = 500 is sent to standby
INSERT INTO test VALUES(111,222222); => a heap_redo WAL record having t_xmin = 500 is sent to standby
INSERT INTO test VALUES(111,333333); => a heap_redo WAL record having t_xmin = 500 is sent to standby
SAVEPOINT A;
INSERT INTO test VALUES(111,444444); => a heap_redo WAL record having t_xmin = 501 is sent to standby
INSERT INTO test VALUES(111,555555); => a heap_redo WAL record having t_xmin = 501 is sent to standby
INSERT INTO test VALUES(111,666666); => a heap_redo WAL record having t_xmin = 501 is sent to standby
SAVEPOINT B;
INSERT INTO test VALUES(111,777777); => a heap_redo WAL record having t_xmin = 502 is sent to standby
INSERT INTO test VALUES(111,888888); => a heap_redo WAL record having t_xmin = 502 is sent to standby
INSERT INTO test VALUES(111,999999); => a heap_redo WAL record having t_xmin = 502 is sent to standby
COMMIT; => a XLOG_XACT_COMMIT WAL record having t_xmin = 500 is sent to standby
可以看到，发送给备库的XLOG_XACT_COMMIT并没有包含501和502子事务，那么备库如何知道501和502事务也提交了呢？这个就是KnownAssignedTransactionIds的作用，在主库提交commit之前，备库的KnownAssignedTransactionIds中就已经记录了 txid 500、501 和 502。当备库收到 XLOG_XACT_COMMIT 的WAL记录时，它实际上会将来自 WAL 的 txid 500 加上来自KnownAssignedTransactionIds的 501 和 502 提交给CLOG，这样后续的查询就知道了子事务501和502同样也提交了。如果主库发送 XLOG_XACT_ABORT回滚到之前的 SAVEPOINT ，那么无效的 txid 就将从KnownAssignedTransactionIds中剔除，因此当事务提交时，无效的 txid 就记录到CLOG。
因此备库不同于主库，还会额外多一些数据结构，用于保存必要的信息。
感兴趣的可以去看一下procarray.c，里面还提交到了子事务溢出的问题，即lastoverflowwedxid和suboverflow。
* During hot standby we do not fret too much about the distinction between
* top-level XIDs and subtransaction XIDs. We store both together in the
* KnownAssignedXids list. In backends, this is copied into snapshots in
* GetSnapshotData(), taking advantage of the fact that XidInMVCCSnapshot()
* doesn't care about the distinction either. Subtransaction XIDs are
* effectively treated as top-level XIDs and in the typical case pg_subtrans
* links are *not* maintained (which does not affect visibility).
/*
* If we are attempting to enter Hot Standby mode, process
* XIDs we see
*/
if (standbyState >= STANDBY_INITIALIZED &&
TransactionIdIsValid(record->xl_xid))
RecordKnownAssignedTransactionIds(record->xl_xid);
再考虑一下max_locks_per_transaction，我们知道在shared buffers中用于存放锁的buffer是固定大小的，总共可以存放max_locks_per_transaction * max_connections个，超过了就会提示out of shared memory。现在让我们不妨考虑一个极端的例子，假设主库的max_connections是100，max_locks_per_transaction是1的话，那么总共锁的数量就是100。这个时候主库上假如有100个会话都在主库上申请了AccessExclusiveLock，比如做个DDL添加了一列，那么备库也需要同步这个操作，假如备库的max_connections值比主库小，那么备库上锁的区域就不够了，更不用说这个时候备库万一还存在查询等活动，那么很容易就会out of shared memory，复制就中断了。
优化
在14的版本里，针对这种情况还做了一个优化，当主库上修改了上面五个参数，并且比备库上的值更小时，不会再导致备库宕库了，而是改成暂停恢复，当备库参数修改为大于等于主库后便会继续自动恢复。
看下面这个例子，原来是正常同步的主备，我将主库的max_connections改成了102，然后重启主库，注意备库并没有重启，不然备库会起不来的，提示FATAL: recovery aborted because of insufficient parameter settings。
postgres=# show max_connections ;
max_connections
-----------------
102
(1 row)postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
备库的则是100
[postgres@xiongcc ~]$ psql -p 5433
psql (14.2)
Type "help" for help.postgres=# show max_connections ;
max_connections
-----------------
100
(1 row)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
这样就违背了备库参数必须大于主库的原则，但是PostgreSQL会表现的很费解，pg_stat_replication依然表明复制"正常"。
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 3786
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr |
client_hostname |
client_port | -1
backend_start | 2022-05-15 21:37:31.46073+08
backend_xmin | 4691
state | streaming
sent_lsn | A/21000420
write_lsn | A/21000420
flush_lsn | A/21000420
replay_lsn | A/210000A0
write_lag | 00:00:00.000093
flush_lag | 00:00:00.001138
replay_lag | 00:08:15.790567
sync_priority | 0
sync_state | async
reply_time | 2022-05-15 21:45:52.682167+08
我们通常遇到的情况都是pg_stat_replication里面没有内容了，表明流复制断了。但是我这个例子是暂停回放了！主库继续插入一些数据
postgres=# insert into test values(generate_series(1,10000));
INSERT 0 10000
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 3786
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr |
client_hostname |
client_port | -1
backend_start | 2022-05-15 21:37:31.46073+08
backend_xmin | 4691
state | streaming
sent_lsn | A/2109D180 ---其他几个lsn都推进了
write_lsn | A/2109D180
flush_lsn | A/2109D180
replay_lsn | A/210000A0 ---replay_lsn并未发生改变
write_lag | 00:00:00.000085
flush_lag | 00:00:00.001181
replay_lag | 00:10:29.221024
sync_priority | 0
sync_state | async
reply_time | 2022-05-15 21:48:00.683047+08
可以很明显地看到，replay_lsn是保持不变，sent_lsn、write_lsn、flush_lsn都是最新的，说明主库已经发送给了备库，但是备库暂停了回放！也就是我在前文提到的SetRecoveryPause()函数。
不过可惜的是，在系统端查看进程的状态，和常见的replication conflict表现并不一致，并没有提示start 000000010000000A00000021 waiting，给人一种依然在正常回放的错觉。
[postgres@xiongcc ~]$ ps -ef | egrep 'sender|stream|startup' | grep -v grep
postgres 3142 3140 0 21:34 ? 00:00:00 postgres: startup recovering 000000010000000A00000021
postgres 3785 3140 0 21:37 ? 00:00:00 postgres: walreceiver streaming A/2109D180
postgres 3786 3769 0 21:37 ? 00:00:00 postgres: walsender postgres [local] streaming A/2109D180
因此危害不难想到，假如暂停了过久，主库会被备库还需要的WAL回收了，那么流复制就断了。因此，监控流复制的延迟和状态十分重要，不能通过简单的查看一下pg_stat_replication视图就完了。
而假如是13的版本，同样的操作会导致备库宕机，如下
2022-05-15 21:59:30.526 CST [5304] LOG: started streaming WAL from primary at 0/3000000 on timeline 1
2022-05-15 21:59:30.530 CST [5200] FATAL: hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 102)
2022-05-15 21:59:30.530 CST [5200] CONTEXT: WAL redo at 0/30000D8 for XLOG/PARAMETER_CHANGE: max_connections=102 max_worker_processes=8 max_wal_senders=10 max_prepared_xacts=0 max_locks_per_xact=64 wal_level=replica wal_log_hints=off track_commit_timestamp=off
2022-05-15 21:59:30.530 CST [5199] LOG: startup process (PID 5200) exited with exit code 1
2022-05-15 21:59:30.530 CST [5199] LOG: terminating any other active server processes
2022-05-15 21:59:30.532 CST [5199] LOG: database system is shut down
2022-05-15 21:59:30.533 CST [5305] LOG: unexpected EOF on standby connection
小结
通过思考，终于搞明白了为什么PostgreSQL会限制备库的部分参数必须大于等于主库，因为流复制模式下，不管是procarray还是快照都不能直接与备库共享，备库是从WAL日志中接收并回放所需的所有数据，因此势必会多一些额外的数据结构来做这些工作，为了不让共享内存溢出，便有了这个限制。
另外在14以后的版本，流复制假如遇到延迟了还要考虑到参数的影响，虽然这个场景不容易遇到。
参考
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=15251c0a60be76eedee74ac0e94b433f9acca5af
https://www.postgresql.org/docs/14/hot-standby.html