印刻数据库

外观

PostgreSQL 在线参数修改方法 ​

在线参数修改方法 ​

1. 全局参数修改（ALTER SYSTEM） ​

修改全局参数，影响所有新连接和现有连接（根据参数类型）：

sql
-- 修改全局参数
ALTER SYSTEM SET parameter_name = 'value';
-- 验证修改
SHOW parameter_name;

示例：

sql
-- 修改最大连接数
ALTER SYSTEM SET max_connections = 200;
-- 修改共享缓冲区大小（需要重启）
ALTER SYSTEM SET shared_buffers = '16GB';
-- 修改自动清理比例因子
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;

2. 会话参数修改（SET） ​

修改当前会话的参数，只影响当前连接：

sql
-- 修改会话参数
SET parameter_name = 'value';
-- 修改当前事务的参数
SET LOCAL parameter_name = 'value';
-- 验证修改
SHOW parameter_name;

示例：

sql
-- 临时增加工作内存
SET work_mem = '32MB';
-- 临时禁用顺序扫描
SET enable_seqscan = off;
-- 临时修改事务隔离级别
SET transaction isolation level serializable;

3. 数据库级参数修改 ​

修改特定数据库的参数，影响该数据库的所有新连接：

sql
-- 修改数据库级参数
ALTER DATABASE dbname SET parameter_name = 'value';
-- 验证修改
ALTER DATABASE dbname SHOW parameter_name;

示例：

sql
-- 设置数据库默认事务隔离级别
ALTER DATABASE mydb SET default_transaction_isolation = 'read committed';
-- 设置数据库默认时区
ALTER DATABASE mydb SET timezone = 'Asia/Shanghai';

4. 用户级参数修改 ​

修改特定用户的参数，影响该用户的所有新连接：

sql
-- 修改用户级参数
ALTER USER username SET parameter_name = 'value';
-- 验证修改
ALTER USER username SHOW parameter_name;

示例：

sql
-- 设置用户默认搜索路径
ALTER USER myuser SET search_path = 'public, myschema';
-- 设置用户默认工作内存
ALTER USER myuser SET work_mem = '16MB';

参数生效方式 ​

1. 立即生效 ​

某些参数修改后立即生效，无需任何操作：

  • 会话级参数修改
  • 部分超级用户级参数

2. 重载配置生效 ​

需要重载配置才能生效：

sql
-- 重载配置
SELECT pg_reload_conf();

需要重载配置的参数类型：

  • sighup级参数
  • 部分全局参数

3. 新连接生效 ​

对现有连接无效，只对新连接生效：

  • backend级参数
  • 数据库级参数
  • 用户级参数

4. 重启生效 ​

某些参数修改后需要重启数据库才能生效：

  • postmaster级参数
  • 核心参数（如shared_buffers、max_connections）

在线参数修改最佳实践 ​

1. 了解参数的生效方式 ​

在修改参数前，先了解参数的生效方式：

sql
-- 查看参数的生效方式
SELECT name, context, short_desc FROM pg_settings WHERE name = 'parameter_name';

2. 从低影响到高影响 ​

按以下顺序修改参数：

  1. 会话级修改（仅影响当前连接）
  2. 用户级修改（仅影响特定用户）
  3. 数据库级修改（仅影响特定数据库）
  4. 全局级修改（影响所有连接）

3. 验证修改效果 ​

修改参数后，验证修改是否生效：

sql
-- 查看参数当前值
SHOW parameter_name;
-- 查看参数的来源
SELECT name, setting, source FROM pg_settings WHERE name = 'parameter_name';

4. 记录所有修改 ​

记录所有参数修改，包括：

  • 修改时间
  • 修改人
  • 修改原因
  • 修改前后的值
  • 验证结果

5. 备份当前配置 ​

修改重要参数前，备份当前配置：

sql
-- 导出当前参数配置
COPY (SELECT name, setting FROM pg_settings) TO '/path/to/params_backup.csv' WITH CSV;

6. 逐步调整 ​

对于性能相关参数，逐步调整，观察效果：

sql
-- 先小幅度调整
ALTER SYSTEM SET work_mem = '8MB';
-- 观察一段时间后再调整
ALTER SYSTEM SET work_mem = '16MB';

常见在线修改的参数 ​

1. 性能相关参数 ​

sql
-- 工作内存
ALTER SYSTEM SET work_mem = '16MB';
-- 维护工作内存
ALTER SYSTEM SET maintenance_work_mem = '1GB';
-- 自动清理比例因子
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
-- 检查点完成目标
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

2. 日志相关参数 ​

sql
-- 记录慢查询的阈值
ALTER SYSTEM SET log_min_duration_statement = '1s';
-- 日志级别
ALTER SYSTEM SET log_min_messages = 'warning';
-- 日志输出格式
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';

3. 连接相关参数 ​

sql
-- 最大连接数（需要重启）
ALTER SYSTEM SET max_connections = 200;
-- 连接超时时间
ALTER SYSTEM SET tcp_keepalives_idle = '60s';
-- 密码验证超时
ALTER SYSTEM SET authentication_timeout = '1min';

4. 复制相关参数 ​

sql
-- 最大复制槽数量
ALTER SYSTEM SET max_replication_slots = 10;
-- 同步提交级别
ALTER SYSTEM SET synchronous_commit = 'remote_write';
-- 复制延迟阈值
ALTER SYSTEM SET hot_standby_feedback = on;

在线参数修改注意事项 ​

1. 避免频繁修改 ​

频繁修改参数可能导致：

  • 性能波动
  • 配置混乱
  • 难以追踪问题

2. 注意参数依赖关系 ​

某些参数之间存在依赖关系，修改一个参数可能影响其他参数的效果：

  • work_mem和max_connections
  • checkpoint_timeout和max_wal_size
  • shared_buffers和effective_cache_size

3. 考虑长期影响 ​

某些参数修改可能有长期影响：

  • 自动清理参数可能影响表的健康状态
  • 日志参数可能影响磁盘空间使用
  • 复制参数可能影响数据一致性

4. 准备回滚方案 ​

修改重要参数前，准备回滚方案：

sql
-- 记录当前值
SELECT name, setting INTO my_param_backup FROM pg_settings WHERE name = 'parameter_name';
-- 回滚修改
ALTER SYSTEM SET parameter_name = (SELECT setting FROM my_param_backup WHERE name = 'parameter_name');

在线参数修改案例 ​

案例1：解决高并发连接问题 ​

问题：数据库连接数达到上限，应用无法连接

解决方案：

sql
-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;
-- 查看当前最大连接数
SHOW max_connections;
-- 在线修改最大连接数（需要重启）
ALTER SYSTEM SET max_connections = 300;
-- 临时增加连接数（会话级）
SET max_connections = 300;

案例2：优化慢查询性能 ​

问题：大量慢查询导致系统负载过高

解决方案：

sql
-- 查看慢查询
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
-- 临时增加工作内存
SET work_mem = '32MB';
-- 全局调整工作内存
ALTER SYSTEM SET work_mem = '16MB';
-- 调整随机页面成本
ALTER SYSTEM SET random_page_cost = 1.1;

案例3：调整自动清理配置 ​

问题：表膨胀严重，自动清理不及时

解决方案：

sql
-- 查看表膨胀情况
SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE n_dead_tup > n_live_tup;
-- 调整自动清理比例因子
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.025;
-- 增加自动清理工作进程数
ALTER SYSTEM SET autovacuum_max_workers = 5;

常见问题（FAQ） ​

Q1：如何查看参数的修改历史？ ​

A1：查看参数修改历史的方法：

sql
-- 查看配置文件的修改
SELECT * FROM pg_file_settings;
-- 查看参数的来源
SELECT name, setting, source FROM pg_settings;

Q2：如何重置参数到默认值？ ​

A2：重置参数到默认值的方法：

sql
-- 重置全局参数
ALTER SYSTEM RESET parameter_name;
-- 重置会话参数
RESET parameter_name;
-- 重置数据库级参数
ALTER DATABASE dbname RESET parameter_name;
-- 重置用户级参数
ALTER USER username RESET parameter_name;

Q3：哪些参数不能在线修改？ ​

A3：不能在线修改的参数包括：

  • postmaster级参数（如shared_buffers、max_connections）
  • 核心配置参数（如wal_level、archive_mode）
  • 编译时参数（如wal_segment_size）

Q4：如何在多个会话中应用相同的参数修改？ ​

A4：在多个会话中应用参数修改的方法：

  1. 修改全局参数（ALTER SYSTEM）
  2. 通知所有应用重启连接
  3. 使用连接池管理工具强制刷新连接

Q5：如何监控参数修改的影响？ ​

A5：监控参数修改影响的方法：

sql
-- 监控查询性能
SELECT * FROM pg_stat_statements ORDER BY calls DESC;
-- 监控系统负载
SELECT * FROM pg_stat_bgwriter;
-- 监控连接数
SELECT count(*) FROM pg_stat_activity;

Q6：如何处理在线参数修改导致的问题？ ​

A6：处理在线参数修改问题的方法：

  1. 立即回滚修改
  2. 分析问题原因
  3. 调整修改策略
  4. 验证修复效果

Q7：如何批量修改在线参数？ ​

A7：批量修改在线参数的方法：

sql
-- 使用DO语句批量修改
DO $$
BEGIN
  ALTER SYSTEM SET parameter1 = 'value1';
  ALTER SYSTEM SET parameter2 = 'value2';
  ALTER SYSTEM SET parameter3 = 'value3';
END $$;

Q8：如何验证在线参数修改的正确性？ ​

A8：验证在线参数修改正确性的方法：

  1. 查看参数当前值
  2. 测试相关功能
  3. 监控性能指标
  4. 检查日志是否有错误

Q9：在线参数修改会影响性能吗？ ​

A9：在线参数修改的性能影响：

  • 大多数参数修改影响很小
  • 某些参数修改可能导致短暂的性能波动
  • 全局参数修改可能需要重载配置，影响较小
  • 会话级参数修改几乎没有性能影响

Q10：如何管理大量的在线参数修改？ ​

A10：管理大量在线参数修改的建议：

  1. 使用配置管理工具（如Ansible、Puppet）
  2. 建立参数修改审批流程
  3. 定期审查参数配置
  4. 使用版本控制系统管理配置文件
  5. 建立参数基线，避免过度调整