印刻数据库

外观

PostgreSQL 参数类型区分 ​

按数据类型分类 ​

1. 布尔型参数 ​

布尔型参数只有 on 或 off 两种值：

sql
-- 启用自动清理
ALTER SYSTEM SET autovacuum = on;
-- 禁用自动清理
ALTER SYSTEM SET autovacuum = off;
-- 验证配置
SHOW autovacuum;

常见布尔型参数：

  • autovacuum：是否启用自动清理
  • wal_compression：是否启用WAL压缩
  • track_io_timing：是否跟踪I/O时间
  • log_statement：是否记录SQL语句

2. 整型参数 ​

整型参数使用整数作为值：

sql
-- 设置最大连接数
ALTER SYSTEM SET max_connections = 200;
-- 设置自动清理工作进程数
ALTER SYSTEM SET autovacuum_max_workers = 5;
-- 验证配置
SHOW max_connections;

常见整型参数：

  • max_connections：最大并发连接数
  • autovacuum_max_workers：自动清理工作进程数
  • max_wal_senders：WAL发送器最大数量
  • maintenance_work_mem：维护操作内存大小（字节）

3. 浮点型参数 ​

浮点型参数使用小数作为值：

sql
-- 设置检查点完成目标
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
-- 设置随机页面成本
ALTER SYSTEM SET random_page_cost = 1.1;
-- 验证配置
SHOW checkpoint_completion_target;

常见浮点型参数：

  • checkpoint_completion_target：检查点完成目标比例
  • random_page_cost：随机页面读取成本
  • seq_page_cost：顺序页面读取成本
  • effective_cache_size：有效缓存大小（字节，PostgreSQL 12+支持浮点数表示）

4. 字符串型参数 ​

字符串型参数使用字符串作为值：

sql
-- 设置归档命令
ALTER SYSTEM SET archive_command = 'cp %p /path/to/archive/%f';
-- 设置预加载共享库
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements,pg_prewarm';
-- 验证配置
SHOW archive_command;

常见字符串型参数：

  • archive_command：WAL归档命令
  • shared_preload_libraries：预加载共享库
  • default_tablespace：默认表空间
  • log_directory：日志目录

5. 枚举型参数 ​

枚举型参数有固定的可选值：

sql
-- 设置事务隔离级别
ALTER SYSTEM SET default_transaction_isolation = 'read committed';
-- 设置同步提交级别
ALTER SYSTEM SET synchronous_commit = 'remote_write';
-- 验证配置
SHOW default_transaction_isolation;

常见枚举型参数：

  • default_transaction_isolation：默认事务隔离级别
  • synchronous_commit：同步提交级别
  • wal_level：WAL级别
  • log_destination：日志输出目标

6. 时间型参数 ​

时间型参数使用时间单位作为值：

sql
-- 设置检查点超时时间
ALTER SYSTEM SET checkpoint_timeout = '15min';
-- 设置WAL写入器延迟
ALTER SYSTEM SET wal_writer_delay = '100ms';
-- 验证配置
SHOW checkpoint_timeout;

常见时间型参数：

  • checkpoint_timeout：检查点超时时间
  • wal_writer_delay：WAL写入器延迟
  • autovacuum_naptime：自动清理休眠时间
  • deadlock_timeout：死锁检测超时时间

按生效方式分类 ​

1. postmaster级参数 ​

需要重启数据库才能生效：

sql
-- 查看所有postmaster级参数
SELECT name, setting FROM pg_settings WHERE context = 'postmaster';

常见postmaster级参数：

  • shared_buffers：共享缓冲区大小
  • max_connections：最大连接数
  • wal_level：WAL级别
  • archive_mode：归档模式

2. sighup级参数 ​

需要发送SIGHUP信号或重载配置才能生效：

sql
-- 查看所有sighup级参数
SELECT name, setting FROM pg_settings WHERE context = 'sighup';
-- 重载配置
SELECT pg_reload_conf();

常见sighup级参数：

  • log_directory：日志目录
  • log_filename：日志文件名格式
  • autovacuum：是否启用自动清理
  • autovacuum_max_workers：自动清理工作进程数

3. backend级参数 ​

新连接生效，现有连接不受影响：

sql
-- 查看所有backend级参数
SELECT name, setting FROM pg_settings WHERE context = 'backend';

常见backend级参数：

  • default_transaction_isolation：默认事务隔离级别
  • default_transaction_read_only：默认事务是否只读
  • client_encoding：客户端编码
  • timezone：时区

4. superuser级参数 ​

超级用户可以在会话中修改，普通用户只能使用默认值：

sql
-- 查看所有superuser级参数
SELECT name, setting FROM pg_settings WHERE context = 'superuser';
-- 在会话中修改参数
SET maintenance_work_mem = '1GB';

常见superuser级参数：

  • maintenance_work_mem：维护操作内存大小
  • work_mem：工作内存大小
  • temp_buffers：临时缓冲区大小
  • log_min_duration_statement：记录慢查询的阈值

5. user级参数 ​

普通用户可以在会话中修改：

sql
-- 查看所有user级参数
SELECT name, setting FROM pg_settings WHERE context = 'user';
-- 在会话中修改参数
SET search_path = 'public, myschema';

常见user级参数：

  • search_path：模式搜索路径
  • application_name：应用程序名称
  • statement_timeout：语句超时时间
  • idle_in_transaction_session_timeout：事务空闲超时时间

按作用域分类 ​

1. 全局参数 ​

作用于整个数据库实例：

sql
-- 设置全局参数
ALTER SYSTEM SET max_connections = 200;
-- 查看全局参数
SHOW max_connections;

常见全局参数：

  • shared_buffers：共享缓冲区大小
  • max_connections：最大连接数
  • wal_level：WAL级别
  • autovacuum：是否启用自动清理

2. 数据库级参数 ​

作用于特定数据库：

sql
-- 设置数据库级参数
ALTER DATABASE mydb SET default_transaction_isolation = 'repeatable read';
-- 查看数据库级参数
ALTER DATABASE mydb SHOW default_transaction_isolation;

常见数据库级参数：

  • default_transaction_isolation：默认事务隔离级别
  • default_transaction_read_only：默认事务是否只读
  • datestyle：日期格式
  • timezone：时区

3. 用户级参数 ​

作用于特定用户：

sql
-- 设置用户级参数
ALTER USER myuser SET work_mem = '16MB';
-- 查看用户级参数
ALTER USER myuser SHOW work_mem;

常见用户级参数：

  • work_mem：工作内存大小
  • search_path：模式搜索路径
  • statement_timeout：语句超时时间
  • application_name：应用程序名称

4. 会话级参数 ​

作用于当前会话：

sql
-- 设置会话级参数
SET work_mem = '32MB';
-- 查看会话级参数
SHOW work_mem;

常见会话级参数：

  • work_mem：工作内存大小
  • search_path：模式搜索路径
  • statement_timeout：语句超时时间
  • enable_seqscan：是否启用顺序扫描

按功能分类 ​

1. 内存相关参数 ​

控制PostgreSQL内存使用：

sql
-- 查看内存相关参数
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%mem%' OR name LIKE '%buffer%';

常见内存相关参数：

  • shared_buffers：共享缓冲区大小
  • work_mem：工作内存大小
  • maintenance_work_mem：维护操作内存大小
  • effective_cache_size：有效缓存大小

2. 存储相关参数 ​

控制PostgreSQL存储使用：

sql
-- 查看存储相关参数
SELECT name, setting FROM pg_settings WHERE name LIKE '%table%' OR name LIKE '%space%';

常见存储相关参数：

  • default_tablespace：默认表空间
  • temp_tablespaces：临时表空间
  • random_page_cost：随机页面读取成本
  • effective_io_concurrency：有效I/O并发数

3. WAL相关参数 ​

控制PostgreSQL WAL（预写日志）：

sql
-- 查看WAL相关参数
SELECT name, setting FROM pg_settings WHERE name LIKE '%wal%' OR name LIKE '%checkpoint%';

常见WAL相关参数：

  • wal_level：WAL级别
  • archive_mode：归档模式
  • checkpoint_timeout：检查点超时时间
  • max_wal_size：最大WAL大小

4. 查询优化相关参数 ​

控制PostgreSQL查询优化：

sql
-- 查看查询优化相关参数
SELECT name, setting FROM pg_settings WHERE name LIKE '%cost%' OR name LIKE '%enable%';

常见查询优化相关参数：

  • random_page_cost：随机页面读取成本
  • seq_page_cost：顺序页面读取成本
  • enable_seqscan：是否启用顺序扫描
  • enable_indexscan：是否启用索引扫描

5. 安全相关参数 ​

控制PostgreSQL安全：

sql
-- 查看安全相关参数
SELECT name, setting FROM pg_settings WHERE name LIKE '%auth%' OR name LIKE '%ssl%';

常见安全相关参数：

  • listen_addresses：监听地址
  • ssl：是否启用SSL
  • password_encryption：密码加密方式
  • authentication_timeout：认证超时时间

参数配置最佳实践 ​

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

在修改参数前，先了解参数的生效方式，避免不必要的数据库重启：

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

2. 从合适的作用域修改参数 ​

根据需求选择合适的作用域修改参数：

  • 全局修改：ALTER SYSTEM SET parameter_name = 'value';
  • 数据库级修改：ALTER DATABASE dbname SET parameter_name = 'value';
  • 用户级修改：ALTER USER username SET parameter_name = 'value';
  • 会话级修改：SET parameter_name = 'value';

3. 验证参数修改效果 ​

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

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

4. 记录参数修改 ​

记录所有参数修改，包括修改原因、修改时间和修改人：

  • 使用版本控制系统管理配置文件
  • 记录修改日志
  • 定期备份配置文件

常见问题（FAQ） ​

Q1：如何查看参数的完整信息？ ​

A1：查看参数完整信息的方法：

sql
-- 查看参数的完整信息
SELECT * FROM pg_settings WHERE name = 'parameter_name';
-- 查看参数的描述
SELECT name, short_desc FROM pg_settings WHERE name = 'parameter_name';

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

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

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

Q3：如何查看所有可修改的参数？ ​

A3：查看所有可修改参数的方法：

sql
-- 查看所有可修改的参数
SELECT name, setting, context FROM pg_settings WHERE vartype != 'internal';
-- 查看超级用户可修改的参数
SELECT name, setting, context FROM pg_settings WHERE context IN ('superuser', 'user');

Q4：如何在会话中临时修改参数？ ​

A4：在会话中临时修改参数的方法：

sql
-- 临时修改参数，当前会话有效
SET work_mem = '32MB';
-- 临时修改参数，当前事务有效
SET LOCAL work_mem = '32MB';

Q5：如何重载配置而不重启数据库？ ​

A5：重载配置的方法：

sql
-- 使用SQL命令重载配置
SELECT pg_reload_conf();
-- 使用pg_ctl命令重载配置
pg_ctl reload -D /path/to/data
-- 发送SIGHUP信号
kill -SIGHUP postmaster_pid

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

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

  1. 查看数据库日志：

    sql
    SELECT * FROM pg_file_settings WHERE name = 'parameter_name';

  2. 查看配置文件的版本控制历史

  3. 使用外部工具监控配置变化

Q7：参数值的单位有哪些？ ​

A7：PostgreSQL参数值支持的单位：

  • 内存单位：B（字节）、KB、MB、GB、TB
  • 时间单位：ms（毫秒）、s（秒）、min（分钟）、h（小时）、d（天）
  • 大小单位：B、KB、MB、GB、TB

Q8：如何配置参数的默认值？ ​

A8：配置参数默认值的方法：

  • 修改postgresql.conf文件
  • 使用ALTER SYSTEM SET命令
  • 使用ALTER DATABASE SET命令（数据库级默认值）
  • 使用ALTER USER SET命令（用户级默认值）

Q9：如何查看参数的允许值范围？ ​

A9：查看参数允许值范围的方法：

sql
-- 查看参数的允许值范围
SELECT name, enumvals, min_val, max_val FROM pg_settings WHERE name = 'parameter_name';

Q10：如何批量修改参数？ ​

A10：批量修改参数的方法：

  1. 直接编辑postgresql.conf文件
  2. 使用脚本批量执行ALTER SYSTEM SET命令
  3. 使用配置管理工具（如Ansible、Puppet）

Q11：参数优先级是怎样的？ ​

A11：PostgreSQL参数优先级从高到低：

  1. 会话级参数（SET命令）
  2. 用户级参数（ALTER USER SET）
  3. 数据库级参数（ALTER DATABASE SET）
  4. 全局参数（ALTER SYSTEM SET或postgresql.conf）
  5. 编译时默认值

Q12：如何监控参数的使用情况？ ​

A12：监控参数使用情况的方法：

  1. 使用pg_stat_statements扩展：

    sql
    SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC;

  2. 监控数据库性能指标

  3. 使用外部监控工具（如Prometheus、Grafana）

Q13：哪些参数对性能影响最大？ ​

A13：对性能影响较大的参数：

  • shared_buffers：共享缓冲区大小
  • work_mem：工作内存大小
  • effective_cache_size：有效缓存大小
  • random_page_cost：随机页面读取成本
  • checkpoint_completion_target：检查点完成目标比例
  • synchronous_commit：同步提交级别

Q14：如何备份当前的参数配置？ ​

A14：备份参数配置的方法：

sql
-- 导出所有参数配置
COPY (SELECT name, setting FROM pg_settings) TO '/path/to/params_backup.csv' WITH CSV;
-- 查看当前配置文件路径
SHOW config_file;

Q15：如何恢复参数配置？ ​

A15：恢复参数配置的方法：

  1. 从备份的配置文件恢复
  2. 使用脚本批量执行ALTER SYSTEM SET命令
  3. 使用配置管理工具
  4. 重启数据库（如果修改了postmaster级参数）