PHP小码农

Appearance

ALTER SYSTEM 动态配置 ​

ALTER SYSTEM 是 PostgreSQL 提供的用 SQL 修改集群级配置参数的命令。它绕开了「登录服务器 → 找到 postgresql.conf → 用 vim 改一行 → 保存 → reload」这套手工流程，把变更写进另一个文件 postgresql.auto.conf。对自动化运维、容器化部署、云数据库（很多云厂商根本不给你文件系统权限）来说，这是唯一可行的配置修改方式。

理解 ALTER SYSTEM 的关键在三点：它写到哪个文件、它的优先级排第几、谁有权执行。这三点搞错，就会遇到「我明明改了参数为什么不生效」「为什么重启后又变回去了」「为什么报权限错误」这类问题。

前置知识

阅读本节前，建议先了解：配置参数生效方式

基础概念 ​

ALTER SYSTEM 解决什么问题 ​

传统配置修改流程需要文件系统权限和 shell 登录：

bash
# 1. 登录数据库服务器
ssh dba@db01

# 2. 用编辑器修改（需要提权）
sudo -u postgres vim /var/lib/postgresql/18/main/postgresql.conf

# 3. 通知重载
sudo systemctl reload postgresql-18

这条链路的痛点：

痛点说明
需要文件系统权限云数据库（RDS、PolarDB 等）通常不暴露数据目录
需要 shell 登录变更审计只能靠 shell 日志，与数据库审计割裂
手工编辑易出错改错行、重复配置、语法错误
难以自动化容器里改文件 + reload 的组合很别扭
与「文件即真相」冲突有人在文件里改、有人用 SQL 改，两处不一致

ALTER SYSTEM 把这件事变成一条 SQL 语句：

sql
ALTER SYSTEM SET work_mem = '16MB';

它写到哪个文件 ​

ALTER SYSTEM 不修改 postgresql.conf，而是写入数据目录下的 postgresql.auto.conf：

bash
sudo cat /var/lib/postgresql/18/main/postgresql.auto.conf
ini
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '16MB'
max_wal_size = '4GB'

文件头部注释是 PostgreSQL 自己写的，意思很明确：这是机器管理的文件，人手别碰。

两个文件的定位对比：

维度postgresql.confpostgresql.auto.conf
谁写人（DBA / 配置管理工具）PostgreSQL 引擎（ALTER SYSTEM）
覆盖关系被 auto.conf 覆盖覆盖 postgresql.conf
手动编辑推荐强烈不建议
进版本库✅ 应该❌ 不应该（它是运行时状态）
恢复默认改回原值即可ALTER SYSTEM RESET

PostgreSQL 在读取 postgresql.conf 之后，总是会读 postgresql.auto.conf（如果存在），不需要显式 include。

完整优先级链 ​

这是本节最重要的一张图，从低到高：

text
┌──────────────────────────────────────────────────────────────┐
│ 优先级从低到高（后者覆盖前者）                                 │
├──────────────────────────────────────────────────────────────┤
│ 1. 编译内置默认值（boot_val） 例：work_mem = 4MB              │
│                          ↓ 覆盖                              │
│ 2. postgresql.conf（含 include / include_dir 分片）           │
│     例：work_mem = 8MB                                       │
│                          ↓ 覆盖                              │
│ 3. postgresql.auto.conf（ALTER SYSTEM 写入）                  │
│     例：work_mem = '16MB'                                    │
│                          ↓ 覆盖                              │
│ 4. 服务启动命令行 -c / --name=value（重启才能改）              │
│     例：postgres -c work_mem=32MB                            │
│                          ↓ 覆盖                              │
│ 5. ALTER DATABASE ... SET / ALTER ROLE ... SET（会话启动时）  │
│     例：ALTER DATABASE myapp SET work_mem = '24MB'           │
│                          ↓ 覆盖                              │
│ 6. 会话级 SET / SET LOCAL                                    │
│     例：SET work_mem = '64MB'                                │
└──────────────────────────────────────────────────────────────┘

注意第 4 层：命令行参数优先级高于 postgresql.auto.conf。如果 systemd 单元文件里写死了 -c work_mem=32MB，用 ALTER SYSTEM 改成 16MB 不会生效（pg_settings.source 会显示 command line）。

权限模型：谁能执行 ​

这是最容易踩坑的地方。PostgreSQL 14 及之前，ALTER SYSTEM 只有超级用户能执行；从 PostgreSQL 15 开始引入了按参数授权模型。

官方文档（sql-altersystem.html）原文：

Only superusers and users granted ALTER SYSTEM privilege on a parameter can change it using ALTER SYSTEM.

也就是说，能执行 ALTER SYSTEM SET <参数> 的有两类角色：

  1. 超级用户（rolsuper = true）
  2. 被显式授予该参数 ALTER SYSTEM 权限的角色

第二类通过 GRANT 实现（语法来自 sql-grant.html）：

sql
GRANT { SET | ALTER SYSTEM } [, ...]
    ON PARAMETER configuration_parameter [, ...]
    TO role_specification [, ...]
    [ WITH GRANT OPTION ]

具体例子：

sql
-- 允许运维角色修改 work_mem 这个参数
GRANT ALTER SYSTEM ON PARAMETER work_mem TO app_dba;

-- 允许修改多个参数
GRANT ALTER SYSTEM ON PARAMETER work_mem, maintenance_work_mem TO app_dba;

-- 一次性授予该参数的全部可用权限（SET 和 ALTER SYSTEM）
GRANT ALL ON PARAMETER work_mem TO app_dba;

授权记录保存在系统目录 pg_parameter_acl 中：

sql
SELECT parname, paracl FROM pg_parameter_acl ORDER BY parname;

根据 catalog-pg-parameter-acl.html：pg_parameter_acl 记录「被授予权限的配置参数」，没有被单独授予权限的参数不会出现在这个表里。三个字段如下：

字段类型含义
oidoid行标识
parnametext被授予权限的配置参数名
paraclaclitem[]访问权限列表

还有一点很重要：pg_parameter_acl 是全集群共享的目录（不像大多数系统目录那样每库一份）。

SET 权限与 ALTER SYSTEM 权限的区别：

权限控制什么影响范围
SET能否在自己的会话里 SET 该参数单个会话
ALTER SYSTEM能否用 ALTER SYSTEM 改该参数的集群级默认值整个集群（reload 后）

一个角色可能有 SET 但没有 ALTER SYSTEM（能改自己的会话，不能改别人），也可能反过来。两者默认分开授予。

不能用 ALTER SYSTEM 设置的参数 ​

官方文档明确列出三类例外：

text
1. data_directory      —— 数据目录位置，改了引擎会找不到自己
2. allow_alter_system  —— 它本身就是控制 ALTER SYSTEM 的开关
3. preset options      —— 只能在编译/启动时确定的参数

尝试设置会直接报错：

sql
ALTER SYSTEM SET data_directory = '/data/pg18';
-- ERROR:  parameter "data_directory" cannot be changed

allow_alter_system 可以禁用整个 ALTER SYSTEM 机制：

ini
# postgresql.conf
allow_alter_system = off

但要特别注意官方警告：这不是安全机制。它只防误操作，不防有权改文件的人。

事务限制 ​

ALTER SYSTEM 不能在事务块里执行，也不能在函数里执行——它直接操作文件系统，无法回滚。

sql
BEGIN;
ALTER SYSTEM SET work_mem = '32MB';
-- ERROR:  ALTER SYSTEM cannot run inside a transaction block
ROLLBACK;

很多 ORM 或迁移框架默认把所有语句包在事务里，这时 ALTER SYSTEM 会失败，需要单独处理。

值类型与引号规则 ​

值格式与 postgresql.conf 一致：

sql
-- 数值型：带单位时必须加引号（推荐带单位）
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET max_connections = 200;          -- 纯整数不需要引号

-- 布尔型：多种写法
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_checkpoints = 'true';
ALTER SYSTEM SET log_checkpoints = 1;

-- 枚举型：需要引号
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET synchronous_commit = 'local';

-- 字符串型：单引号
ALTER SYSTEM SET log_directory = 'pg_log';

-- 列表型：逗号分隔
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements, auto_explain';

一个常见的坑：带单位的数值必须加引号，因为 16MB 不是合法的 SQL 记号。

sql
ALTER SYSTEM SET work_mem = 16MB;
-- ERROR:  syntax error at or near "16MB"

ALTER SYSTEM SET work_mem = '16MB';
-- ALTER SYSTEM

语法与用法 ​

三条语法 ​

来自 sql-altersystem.html 的标准语法：

sql
-- 语法一：设置参数（可一次设置多个，逗号分隔）
ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }

-- 语法二：重置单个参数（从 auto.conf 中删除该条目）
ALTER SYSTEM RESET configuration_parameter

-- 语法三：重置所有由 ALTER SYSTEM 设置的条目
ALTER SYSTEM RESET ALL

TO 和 = 完全等价，随个人习惯。

SET / DEFAULT / RESET 的语义差异 ​

这三种写法行为不同，必须分清：

写法对 postgresql.auto.conf 的影响效果
ALTER SYSTEM SET x = 'v'写入或覆盖 x = 'v'集群默认值变为 v
ALTER SYSTEM SET x = DEFAULT删除 x 条目回落到 postgresql.conf 的值
ALTER SYSTEM RESET x删除 x 条目与上一行完全等价
ALTER SYSTEM RESET ALL清空整个文件的配置条目所有 ALTER SYSTEM 变更撤销

关键认知：RESET 不是「把参数设成某个默认值」，而是「把这条 ALTER SYSTEM 记录删掉，让下面一层的值重新露出来」。

sql
-- 假如 postgresql.conf 里写了 work_mem = 8MB
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

SELECT name, setting, unit, source, sourcefile
FROM pg_settings WHERE name = 'work_mem';
text
   name   | setting | unit |       source       |            sourcefile
----------+---------+------+--------------------+----------------------------------
 work_mem | 65536   | kB   | configuration file | .../main/postgresql.auto.conf
sql
-- 现在撤销
ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();

SELECT name, setting, unit, source, sourcefile
FROM pg_settings WHERE name = 'work_mem';
text
   name   | setting | unit |       source       |          sourcefile
----------+---------+------+--------------------+------------------------------
 work_mem | 8192    | kB   | configuration file | .../main/postgresql.conf

如果 postgresql.conf 里也没写，就回落到编译默认值，source 变成 default。

从修改到生效的完整时序 ​

text
T0  执行 ALTER SYSTEM SET work_mem = '64MB'
    └─ 写入 postgresql.auto.conf（磁盘同步）
    └─ 此时 pg_settings 里仍是旧值！因为还没 reload

T1  执行 SELECT pg_reload_conf()
    └─ postmaster 收到 SIGHUP
    └─ 重新读取 postgresql.conf + postgresql.auto.conf
    └─ 新值写入共享内存，SIGHUP 转发给所有后端进程

T2  已存在的会话
    └─ 下次空闲时采用新值（若该参数 context 允许）
    └─ 若会话里已 SET 过该参数，则保持会话值不变

T3  新建立的连接
    └─ 直接使用新值

这就是为什么「ALTER SYSTEM 后查询 pg_settings 还是旧值」——它需要一次 reload。

sql
-- 一条语句完成「修改」，另一条完成「生效」
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

SELECT pg_sleep(0.5);
SELECT name, setting, unit, source, pending_restart FROM pg_settings WHERE name = 'work_mem';

查看 auto.conf 的当前内容 ​

三种方式：

sql
-- 方式一：查 pg_settings，按 sourcefile 过滤
SELECT name, setting, unit, source, sourceline
FROM pg_settings
WHERE sourcefile LIKE '%postgresql.auto.conf'
ORDER BY name;

-- 方式二：如果角色有 pg_read_server_files，直接读文件
SELECT pg_read_file('postgresql.auto.conf');

方式二需要超级用户或 pg_read_server_files 角色成员身份：

sql
GRANT pg_read_server_files TO app_dba;

对于非超级用户，pg_settings.sourcefile 和 sourceline 列是 NULL（除非拥有 pg_read_all_settings）：

sql
GRANT pg_read_all_settings TO app_dba;

查看权限现状 ​

sql
-- 谁有超级用户权限
SELECT rolname FROM pg_roles WHERE rolsuper ORDER BY rolname;

-- 哪些参数被单独授权
SELECT parname, paracl FROM pg_parameter_acl ORDER BY parname;

-- 展开成可读形式（用官方的 aclexplode 集合返回函数）
SELECT p.parname,
       grantee.rolname AS grantee,
       acl.privilege_type
FROM pg_parameter_acl p,
     LATERAL aclexplode(p.paracl) AS acl
     LEFT JOIN pg_roles grantee ON grantee.oid = acl.grantee
ORDER BY p.parname, grantee.rolname;
text
 parname  |  grantee  | privilege_type
----------+-----------+----------------
 work_mem | app_dba   | ALTER SYSTEM

详细说明 ​

一、为什么会有 postgresql.auto.conf ​

PostgreSQL 9.4 引入 ALTER SYSTEM。在它之前，想在 SQL 里改配置只有两条路：ALTER DATABASE / ALTER ROLE SET（只影响单库或单角色），或者直接编辑 postgresql.conf（需要文件系统权限）。后者在托管数据库中完全不可行。

所以 9.4 引入了「引擎自己维护一个配置文件」的思路，好处是把两类职责分开：

text
┌──────────────────────────┬───────────────────────────────┐
│ postgresql.conf          │ postgresql.auto.conf          │
│ （人管理的部分）          │ （SQL 管理的部分）             │
├──────────────────────────┼───────────────────────────────┤
│ 进版本库，走代码评审      │ 运行时状态，由语句产生         │
│ 含 include 分片           │ 单文件，无 include             │
│ 手工注释丰富              │ 全部由引擎重写                 │
└──────────────────────────┴───────────────────────────────┘

设计哲学：把「声明式配置（人写）」与「命令式调整（SQL 写）」分开。你可以把 postgresql.conf 完全交给配置管理工具，把临时/自动化调整交给 ALTER SYSTEM，两者互不覆盖。

二、文件格式与重写行为 ​

postgresql.auto.conf 与 postgresql.conf 格式完全相同，但有两个特殊点。

（1）不支持 include。ALTER SYSTEM 写入的是一个扁平文件，不提供 include 机制。

（2）每次 ALTER SYSTEM SET 都会重写整个文件：

text
1. 读取现有 auto.conf
2. 移除该参数的既有条目（含可能的重复项和被注释掉的条目）
3. 在文件末尾追加新条目
4. 原子替换文件

这就是为什么手动编辑会被覆盖：你手写的一行，下次 ALTER SYSTEM 执行时会被「规范化」掉。

三、与 ALTER DATABASE / ALTER ROLE SET 的关系 ​

三者都是「设置会话默认值」，但作用域不同：

命令作用域生效时机写入位置
ALTER SYSTEM SET整个集群reload 后postgresql.auto.conf
ALTER DATABASE db SET单个数据库新会话登录该库时pg_db_role_setting
ALTER ROLE r SET单个角色该角色新会话登录时pg_db_role_setting
ALTER ROLE r IN DATABASE db SET角色 + 库该角色登录该库时pg_db_role_setting
SET / SET LOCAL当前会话/事务立即内存

ALTER DATABASE / ALTER ROLE 的设置不写在配置文件里，而存在系统表 pg_db_role_setting：

sql
SELECT
    COALESCE(d.datname, '-') AS database,
    COALESCE(r.rolname, '-') AS role,
    s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole;
text
 database |   role   |          setconfig
----------+----------+-----------------------------
 myapp    | -        | {work_mem=24MB}
 myapp    | app_dba  | {maintenance_work_mem=512MB}
 -        | etl_job  | {work_mem=256MB}

冲突时的优先级（来自 sql-alterrole.html）：

text
database-role-specific  >  role-specific  >  database-specific

即「角色+库」最具体、优先级最高；其次是「角色级」；再次是「库级」。

psql 元命令 \drds 也能看：

text
 Role name | Database | Settings
-----------+----------+-----------------------------
 app_dba   | myapp    | maintenance_work_mem=512MB
 etl_job   |          | work_mem=256MB

四、ALTER SYSTEM RESET 的细节 ​

RESET 只删除 auto.conf 里的条目，不会影响 postgresql.conf 或 pg_db_role_setting。

sql
-- 场景：postgresql.conf 里写 max_wal_size = 2GB，有人改成 8GB，现在想改回来
ALTER SYSTEM RESET max_wal_size;
SELECT pg_reload_conf();   -- 生效值回到 postgresql.conf 的 2GB

RESET ALL 是「核弹级」操作，执行前必须备份：

bash
sudo cp $PGDATA/postgresql.auto.conf /tmp/auto.conf.$(date +%F-%H%M%S)

也可以用 SQL 生成「重建语句」，便于回滚：

sql
SELECT format('ALTER SYSTEM SET %I = %L;', name, setting || COALESCE(unit, ''))
FROM pg_settings
WHERE sourcefile LIKE '%postgresql.auto.conf'
ORDER BY name;
text
                   format
---------------------------------------------
 ALTER SYSTEM SET max_wal_size = '8GB';
 ALTER SYSTEM SET work_mem = '65536kB';

setting 列返回的是未带单位的裸值（单位在 unit 列），所以重建语句要拼上 unit。

五、context = postmaster 的参数需要重启 ​

contextALTER SYSTEM 后如何生效
internal不能设置（会报错）
postmaster需要重启，reload 无效
sighupreload 生效
superuser-backendreload 生效，但只影响新连接
backendreload 生效，但只影响新连接
superuserreload 生效（未 SET 过的会话）
userreload 生效（未 SET 过的会话）
sql
ALTER SYSTEM SET shared_buffers = '4GB';   -- context = postmaster
SELECT pg_reload_conf();
SELECT name, setting, pending_restart FROM pg_settings WHERE name = 'shared_buffers';
text
      name       | setting | pending_restart
-----------------+---------+-----------------
 shared_buffers  | 524288  | t

pending_restart = t 是「改了但还没重启」的信号。它意味着「磁盘上的配置」与「内存里的配置」已经不一致，运维监控应当对此告警。

六、用 ALTER SYSTEM 管理 shared_preload_libraries ​

shared_preload_libraries 是 postmaster context，且语义特殊（决定哪些扩展被预加载），适合演示完整流程：

sql
-- 第 1 步：修改
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements, auto_explain';

-- 第 2 步：确认已写入 auto.conf
SELECT name, setting, sourcefile FROM pg_settings WHERE name = 'shared_preload_libraries';

-- 第 3 步：因为 context 是 postmaster，需要重启（reload 只会标记 pending_restart）
SELECT name, pending_restart FROM pg_settings WHERE name = 'shared_preload_libraries';

-- 第 4 步：重启后确认扩展已加载
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

常见误区：以为 CREATE EXTENSION pg_stat_statements; 就够了。实际上 shared_preload_libraries 没配置的话，扩展会创建失败或无法收集统计。

七、ALTER SYSTEM 与配置文件编辑的协同 ​

两者不是二选一，而是划分职责：

text
┌───────────────────────────────────────────────────────────────┐
│ 放 postgresql.conf（人管理）                                   │
│  • 稳定的基线参数（shared_buffers、max_connections、端口）      │
│  • 与硬件/环境绑定的参数（在版本库中按环境分片）                 │
│  • 需要代码评审、需要历史记录的变更                             │
├───────────────────────────────────────────────────────────────┤
│ 放 postgresql.auto.conf（SQL 管理）                            │
│  • 自动化脚本/运维平台发起的调整                                │
│  • 临时调优（需要全局生效的）                                   │
│  • 云数据库等无文件权限场景                                     │
│  • 灰度/回滚式变更（RESET 一条语句即可回退）                    │
└───────────────────────────────────────────────────────────────┘

反模式：把基线参数也用 ALTER SYSTEM 设，导致 postgresql.conf 里的值被静默覆盖，新人看文件完全不知道实际运行值。

排查冲突最有效的视图是 pg_file_settings，它能精确到「文件 + 行号」并标出哪条被覆盖：

sql
SELECT sourcefile, sourceline, name, setting, applied, error
FROM pg_file_settings
WHERE name IN ('work_mem', 'max_wal_size')
ORDER BY name, sourcefile, sourceline;
text
            sourcefile                    | sourceline |    name     | setting | applied | error
------------------------------------------+------------+-------------+---------+---------+-------
 .../main/postgresql.conf                  |    128     | work_mem    | 8MB     | f       |
 .../main/postgresql.auto.conf             |      4     | work_mem    | 65536   | t       |

applied = f 表示「这条被后面的条目覆盖了，没有生效」。

八、并发与「最后写入者胜」 ​

ALTER SYSTEM 内部对 auto.conf 有加锁保护，但仍有并发风险：两个进程同时 SET 同一参数都会成功，最终值取决于执行顺序。对参数化配置管理平台来说这是隐患——如果平台先 RESET ALL 再逐个 SET，期间别人插一条就会被清掉。

推荐做法是变更前后都做快照对比：

sql
CREATE TEMP TABLE cfg_before AS
SELECT name, setting, unit FROM pg_settings WHERE sourcefile LIKE '%postgresql.auto.conf';

-- 执行变更 ...

SELECT COALESCE(b.name, a.name) AS name,
       b.setting AS before_value,
       a.setting AS after_value
FROM cfg_before b
FULL JOIN (SELECT name, setting FROM pg_settings WHERE sourcefile LIKE '%postgresql.auto.conf') a
  ON a.name = b.name
WHERE b.setting IS DISTINCT FROM a.setting;

九、两种典型组合模式 ​

模式 A：单参数调整

sql
-- 分两条独立语句执行（各自自动提交），不能在 BEGIN 里
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

模式 B：批量调整（先全部写入，再统一 reload）

sql
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET random_page_cost = 1.1;
-- 全部写完再统一 reload，避免多次信号广播
SELECT pg_reload_conf();

pg_reload_conf() 会让所有后端重新读取配置，频繁调用是无意义的开销。

十、与监控告警、审计的对接 ​

把「配置漂移」做成可监控的指标：

sql
-- 需要重启才能生效的参数清单（应为空）
SELECT name, setting, unit, pending_restart FROM pg_settings WHERE pending_restart ORDER BY name;

-- auto.conf 条目数（突增/突减都可能意味着误操作）
SELECT count(*) AS auto_conf_entries FROM pg_settings WHERE sourcefile LIKE '%postgresql.auto.conf';

-- 所有非默认来源的参数（配置基线快照）
SELECT name, setting, unit, source, sourcefile, sourceline
FROM pg_settings WHERE source <> 'default' ORDER BY name;

ALTER SYSTEM 本身不写审计日志，需要 log_statement=ddl 或 pgaudit：

sql
ALTER SYSTEM SET log_statement = 'ddl';
SELECT pg_reload_conf();
-- 之后日志里会出现：statement: ALTER SYSTEM SET work_mem = '64MB';
sql
-- pgaudit 方案（需 shared_preload_libraries，故要重启）
ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';
-- 重启后
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'ddl, role';
SELECT pg_reload_conf();

十一、ALTER SYSTEM 在托管环境中的特殊约束 ​

场景典型行为
完全托管（如 RDS）只开放部分参数，用控制台/API 修改；直接 ALTER SYSTEM 可能被拒绝
半托管（如 K8s Operator）允许 ALTER SYSTEM，但 Operator 会覆盖它管理的参数
自建无限制

在 Operator 管理的集群里，常见现象是你 ALTER SYSTEM 改了参数，过一会儿被 Operator 从 CR（Custom Resource）重新同步回原值。这时唯一正确的做法是改 CR，而不是改数据库。

判断方法：看 postgresql.conf 里是否有自动生成的头部注释：

ini
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
# This file was generated by the postgres-operator
# Do not edit this file manually

看到这种注释就说明文件由外部系统管理，ALTER SYSTEM 的改动大概率会被覆盖。

实战示例 ​

场景：用 ALTER SYSTEM 完成一次安全的参数调优 ​

背景：一台 32GB 内存的 OLTP 数据库，当前为「开箱即用」配置（全部默认）。需要在不重启的前提下完成一轮调优，并把「需要重启」的参数单独留到维护窗口。

第 1 步：采集当前基线 ​

sql
SELECT name, setting, unit, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'work_mem', 'maintenance_work_mem',
    'effective_cache_size', 'random_page_cost', 'max_wal_size',
    'log_min_duration_statement', 'wal_buffers'
)
ORDER BY name;
text
            name            |  setting  | unit |   context   | source  | pending_restart
----------------------------+-----------+------+-------------+---------+-----------------
 effective_cache_size       | 524288    | 8kB  | user        | default | f
 log_min_duration_statement | -1        | ms   | superuser   | default | f
 maintenance_work_mem       | 65536     | kB   | user        | default | f
 max_wal_size               | 1024      | MB   | sighup      | default | f
 random_page_cost           | 4         |      | user        | default | f
 shared_buffers             | 16384     | 8kB  | postmaster  | default | f
 wal_buffers                | -1        | 8kB  | postmaster  | default | f
 work_mem                   | 4096      | kB   | user        | default | f

第 2 步：按 context 分两批 ​

text
【reload 批】context ∈ {sighup, superuser, user, backend, superuser-backend}
  → work_mem / maintenance_work_mem / effective_cache_size
  → random_page_cost / max_wal_size / log_min_duration_statement

【重启批】context = postmaster
  → shared_buffers / wal_buffers

第 3 步：执行 reload 批与重启批 ​

sql
-- reload 批（数值单位必须加引号）
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET max_wal_size = '8GB';
ALTER SYSTEM SET log_min_duration_statement = '500ms';

-- 重启批（写入但暂不生效）
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET wal_buffers = '64MB';

第 4 步：预检 — 用 pg_file_settings 检查语法 ​

这是关键一步，能在 reload 之前发现语法错误，避免「reload 后参数静默不生效」。

sql
SELECT sourcefile, sourceline, name, setting, applied, error
FROM pg_file_settings
WHERE error IS NOT NULL
   OR name IN ('work_mem', 'maintenance_work_mem', 'effective_cache_size',
               'random_page_cost', 'max_wal_size', 'log_min_duration_statement',
               'shared_buffers', 'wal_buffers')
ORDER BY sourcefile, sourceline;
text
                 sourcefile                  | sourceline |          name          | setting | applied | error
---------------------------------------------+------------+------------------------+---------+---------+-------
 .../main/postgresql.auto.conf                |      1     | work_mem               | 16MB    | t       |
 .../main/postgresql.auto.conf                |      2     | maintenance_work_mem   | 1GB     | t       |
 .../main/postgresql.auto.conf                |      3     | effective_cache_size   | 24GB    | t       |
 .../main/postgresql.auto.conf                |      4     | random_page_cost       | 1.1     | t       |
 .../main/postgresql.auto.conf                |      5     | max_wal_size           | 8GB     | t       |
 .../main/postgresql.auto.conf                |      6     | log_min_duration_statement | 500ms | t     |
 .../main/postgresql.auto.conf                |      7     | shared_buffers         | 8GB     | t       |
 .../main/postgresql.auto.conf                |      8     | wal_buffers            | 64MB    | t       |

error 全为空、applied 全为 t，说明语法都正确、无冲突。

第 5 步：reload 并验证生效情况 ​

sql
SELECT pg_reload_conf();
SELECT pg_sleep(0.5);

SELECT name, setting, unit, source, pending_restart
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'work_mem', 'maintenance_work_mem',
    'effective_cache_size', 'random_page_cost', 'max_wal_size',
    'log_min_duration_statement', 'wal_buffers'
)
ORDER BY name;
text
            name            |  setting  | unit |       source       | pending_restart
----------------------------+-----------+------+--------------------+-----------------
 effective_cache_size       | 3145728   | 8kB  | configuration file | f
 log_min_duration_statement | 500       | ms   | configuration file | f
 maintenance_work_mem       | 1048576   | kB   | configuration file | f
 max_wal_size               | 8192      | MB   | configuration file | f
 random_page_cost           | 1.1       |      | configuration file | f
 shared_buffers             | 16384     | 8kB  | configuration file | t  ← 需要重启
 wal_buffers                | -1        | 8kB  | configuration file | t  ← 需要重启
 work_mem                   | 16384     | kB   | configuration file | f

读法：source 变成 configuration file 说明 reload 已生效；pending_restart = t 说明该值只写在磁盘上、还没进内存（shared_buffers 仍是 16384 个 8kB 页 = 128MB，即默认值）。

第 6 步：确认「会话快照」行为 ​

sql
-- reload 之前建立的会话，若此前未 SET 过，会采用新值
SHOW work_mem;
text
 work_mem
----------
 16MB

但反过来，如果当前会话里已经执行过 SET work_mem，reload 不会覆盖会话值：

sql
SET work_mem = '64MB';
SELECT pg_reload_conf();
SELECT pg_sleep(0.5);
SHOW work_mem;
text
 work_mem
----------
 64MB

想回到全局值，必须显式重置会话：

sql
RESET work_mem;
SHOW work_mem;
text
 work_mem
----------
 16MB

第 7 步：把结果固化为基线 ​

bash
psql -At -c "
  SELECT format('%-30s = %-12s  # %s', name, setting || COALESCE(unit, ''), source)
  FROM pg_settings WHERE source <> 'default' ORDER BY name
" > /etc/pgsql/config-baseline-$(date +%F).txt

第 8 步：维护窗口重启后验证 ​

bash
# 重启前先做一次检查点，缩短恢复时间
psql -c "CHECKPOINT;"
sudo systemctl restart postgresql-18

# 重启后确认 pending_restart 已清空
psql -c "SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart;"
text
 name | setting | pending_restart
------+---------+-----------------
(0 rows)
sql
-- 确认 shared_buffers 真正生效（单位是 8kB 块）
SELECT name, setting, unit,
       setting::bigint * CASE unit WHEN '8kB' THEN 8192 ELSE 1 END AS bytes
FROM pg_settings WHERE name = 'shared_buffers';
text
      name       | setting | unit |   bytes
-----------------+---------+------+------------
 shared_buffers  | 1048576 | 8kB  | 8589934592

8589934592 字节 = 8GB，与设置一致。

第 9 步：回滚预案 ​

sql
-- 逐条回退
ALTER SYSTEM RESET work_mem;
ALTER SYSTEM RESET effective_cache_size;
ALTER SYSTEM RESET random_page_cost;
SELECT pg_reload_conf();

-- 或整体回退（谨慎！先备份 auto.conf）
ALTER SYSTEM RESET ALL;
SELECT pg_reload_conf();

注意事项 ​

不要在事务块或函数中执行 ALTER SYSTEM。 它直接操作文件系统且无法回滚，官方明确禁止在事务中运行。ORM 迁移框架通常会自动包事务，需要显式跳出。

sql
-- 错误
BEGIN;
ALTER SYSTEM SET work_mem = '32MB';
-- ERROR:  ALTER SYSTEM cannot run inside a transaction block

带单位的数值必须加引号。 16MB 不是合法 SQL 记号，必须写成 '16MB'。

sql
-- 错误
ALTER SYSTEM SET work_mem = 16MB;
-- 正确
ALTER SYSTEM SET work_mem = '16MB';

不要手动编辑 postgresql.auto.conf。 文件头已经写明会被 ALTER SYSTEM 覆盖，手工内容随时可能丢失。

ini
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

ALTER SYSTEM 后不 reload 等于没改。 写入文件不等于生效，必须 SELECT pg_reload_conf() 或等价手段。

sql
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();   -- 漏了这一步，参数永远不会生效

reload 不会改变已经 SET 过的会话参数值。 排查「为什么改了参数某个连接没变」时，必须先确认该会话有没有本地 SET。

sql
SELECT name, setting, source FROM pg_settings WHERE name = 'work_mem';
-- source = 'session' 说明会话本地覆盖了，reload 无效

postmaster 类参数用 ALTER SYSTEM 后仍需重启。 reload 只会把它们标记为 pending_restart = t，不会真正生效。

sql
ALTER SYSTEM SET shared_buffers = '8GB';
SELECT pg_reload_conf();
SELECT name, setting, pending_restart FROM pg_settings WHERE name = 'shared_buffers';
-- pending_restart = t，实际值仍是旧的

ALTER SYSTEM RESET 的语义是「删条目」，不是「设为默认值」。 删除后生效值由下层（postgresql.conf）决定，可能不是编译默认值。

sql
ALTER SYSTEM RESET work_mem;
-- 如果 postgresql.conf 里有 work_mem = 8MB，生效值就是 8MB，而不是 4MB

执行 RESET ALL 前必须先备份。 它会清空所有 ALTER SYSTEM 条目，且没有 undo。

bash
sudo cp $PGDATA/postgresql.auto.conf /tmp/auto.conf.bak.$(date +%F-%H%M%S)

data_directory、allow_alter_system、preset 参数无法用 ALTER SYSTEM 设置。 会直接报错。

sql
ALTER SYSTEM SET data_directory = '/data/pg18';
-- ERROR:  parameter "data_directory" cannot be changed

allow_alter_system = off 不是安全机制。 它只能防误操作，不能防有权改文件的人。真正的权限控制要靠角色系统和文件权限。

ini
# postgresql.conf
allow_alter_system = off

小心 Operator 管理的集群会覆盖你的改动。 如果 postgresql.conf 头部有「generated by ...」注释，说明文件由外部系统托管。

ini
# This file was generated by the postgres-operator
# Do not edit this file manually

pg_settings.sourcefile 对非超级用户为 NULL。 排查配置来源需要超级用户或 pg_read_all_settings / pg_read_server_files 角色成员身份。

sql
GRANT pg_read_all_settings TO app_dba;

最佳实践 ​

把基线配置放 postgresql.conf，把动态调整放 ALTER SYSTEM。 前者进版本库、走评审；后者作为运行时状态由 SQL 产生。两者的职责边界要清晰。

ini
# postgresql.conf（人管理，进 Git）
shared_buffers = 8GB
max_connections = 300
listen_addresses = '*'

每次 ALTER SYSTEM 前先查 context，按生效方式分批。 reload 能完成的绝不排到重启窗口。

sql
SELECT name, context FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_wal_size');

变更前用 pg_file_settings 预检语法。 这是唯一能在 reload 之前发现语法错误的手段。

sql
SELECT sourcefile, sourceline, name, setting, applied, error
FROM pg_file_settings WHERE error IS NOT NULL;
-- 期望 0 行

批量变更时「先全部写入，再统一 reload」。 一次 pg_reload_conf() 顶多次调用，减少无谓的后端配置重读。

sql
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
SELECT pg_reload_conf();

用 pending_restart 做监控指标。 这个值为 t 意味着磁盘与内存的配置已经不一致，属于必须告警的状态。

sql
SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart ORDER BY name;

变更前后都做配置快照并 diff。 三次 ALTER SYSTEM 里混入一次误操作，只有 diff 能发现。

sql
CREATE TEMP TABLE cfg_before AS
SELECT name, setting, unit FROM pg_settings WHERE sourcefile LIKE '%postgresql.auto.conf';
-- 变更...
SELECT b.name, b.setting AS before, a.setting AS after
FROM cfg_before b JOIN pg_settings a USING (name)
WHERE a.setting IS DISTINCT FROM b.setting;

给需要自助调参的角色按参数授权，而不是给超级用户。 PostgreSQL 15+ 的按参数授权模型正是为此设计。

sql
GRANT ALTER SYSTEM ON PARAMETER work_mem TO app_dba;
GRANT ALTER SYSTEM ON PARAMETER maintenance_work_mem TO app_dba;

用 pg_parameter_acl 做授权审计。 定期导出「哪些参数被单独授权给了谁」，防止权限扩散。

sql
SELECT p.parname, g.rolname, a.privilege_type
FROM pg_parameter_acl p, LATERAL aclexplode(p.paracl) a
LEFT JOIN pg_roles g ON g.oid = a.grantee
ORDER BY p.parname, g.rolname;

把 ALTER SYSTEM 写进变更单模板。 语句全文、影响参数、生效方式、回滚语句，四要素齐全才允许执行。

text
变更单片段：
- 语句：ALTER SYSTEM SET work_mem = '16MB';
- 影响：全局默认 work_mem，reload 生效，无需重启
- 回滚：ALTER SYSTEM RESET work_mem; SELECT pg_reload_conf();
- 预检：SELECT * FROM pg_file_settings WHERE error IS NOT NULL;（0 行）

给 ALTER SYSTEM 配一个幂等化封装脚本。 参数值已经正确时重复执行不应产生副作用。

bash
#!/usr/bin/env bash
# 幂等地设置一个参数：值相同则跳过
set -euo pipefail
PARAM="$1"; WANT="$2"
CUR=$(psql -At -c "SELECT setting || COALESCE(unit,'') FROM pg_settings WHERE name='${PARAM}'")
if [ "$CUR" = "$WANT" ]; then
    echo "[skip] ${PARAM} 已是 ${WANT}"
    exit 0
fi
psql -v ON_ERROR_STOP=1 -c "ALTER SYSTEM SET ${PARAM} = '${WANT}';"
psql -At -c "SELECT pg_reload_conf();" > /dev/null
echo "[done] ${PARAM}: ${CUR} -> ${WANT}"

变更后立即用 pg_settings 验证，而不是只看命令返回成功。 ALTER SYSTEM 成功 ≠ 生效。

sql
SELECT name, setting, unit, source, pending_restart
FROM pg_settings WHERE name = 'work_mem';

把 ALTER SYSTEM 的语句纳入自动化流水线时，加一层范围校验。 用 pg_settings.min_val / max_val 校验，防止脚本把参数误设成灾难性值。

sql
SELECT name, min_val, max_val, vartype FROM pg_settings WHERE name = 'work_mem';

文档化「哪些参数被 ALTER SYSTEM 管理」。 在 postgresql.conf 头部加一段注释，避免后来者困惑。

ini
# ============================================================
# 注意：以下参数由 ALTER SYSTEM 管理，不在本文件配置
#   work_mem, maintenance_work_mem, effective_cache_size
# 查看实际值：SELECT name, setting FROM pg_settings WHERE sourcefile LIKE '%auto.conf';
# ============================================================

下一节

继续学习：shared_buffers 共享缓冲区

参考链接 ​