作者
digoal
日期
2021-01-19
标签
PostgreSQL , pause , 参数 , 从库 , 主库
背景
注意 - 当primary修改了某些standby 必须更小的参数时, 不会导致standby shutdown 了, 改成暂停恢复. 从库参数修改为大于等于主库后, 自动继续恢复.
所以一定要监控standby的恢复状态呀, 还有恢复延迟, 否则你都不知道发生了什么. 而且更严重的是长时间不恢复, 主库的WAL日志可能rotate, 从库需要的wal可能被rotate.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=15251c0a60be76eedee74ac0e94b433f9acca5af
```
Pause recovery for insufficient parameter settings
author Peter Eisentraut peter@eisentraut.org
Mon, 18 Jan 2021 07:49:10 +0000 (08:49 +0100)
committer Peter Eisentraut peter@eisentraut.org
Mon, 18 Jan 2021 08:04:04 +0000 (09:04 +0100)
commit 15251c0a60be76eedee74ac0e94b433f9acca5af
tree 4b77b0f77f165ea2d2091b84b5c2b8774bf60ea3 tree | snapshot
parent 708d165ddb92c54077a372acf6417e258dcb5fef commit | diff
Pause recovery for insufficient parameter settings
When certain parameters are changed on a physical replication primary,
this is communicated to standbys using the XLOG_PARAMETER_CHANGE WAL
record. The standby then checks whether its own settings are at least
as big as the ones on the primary. If not, the standby shuts down
with a fatal error.
This patch changes this behavior for hot standbys to pause recovery at
that point instead. That allows read traffic on the standby to
continue while database administrators figure out next steps. When
recovery is unpaused, the server shuts down (as before). The idea is
to fix the parameters while recovery is paused and then restart when
there is a maintenance window.
Reviewed-by: Sergei Kornilov sk@zsrv.org
Discussion: https://www.postgresql.org/message-id/flat/4ad69a4c-cc9b-0dfe-0352-8b1b0cd36c7b@2ndquadrant.com
```
到底哪些参数必须满足从库大于或等于主库呢?
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. For example, if the primary had used a prepared transaction but the standby had not allocated any shared memory for tracking prepared transactions, then recovery could not continue until the standby's configuration is changed. The parameters affected are:
- max_connections
- max_prepared_transactions
- max_locks_per_transaction
- max_wal_senders
- max_worker_processes
The easiest way to ensure this does not become a problem is to have these parameters set on the standbys to values equal to or greater than on the primary. Therefore, if you want to increase these values, you should do so on all standby servers first, before applying the changes to the primary server. Conversely, if you want to decrease these values, you should do so on the primary server first, before applying the changes to all standby servers. Keep in mind that when a standby is promoted, it becomes the new reference for the required parameter settings for the standbys that follow it. Therefore, to avoid this becoming a problem during a switchover or failover, it is recommended to keep these settings the same on all standby servers.
The WAL tracks changes to these parameters on the primary. If a hot standby processes WAL that indicates that the current value on the primary is higher than its own value, it will log a warning and pause recovery, for example:
日志:
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.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
