PostgreSQL教程

查看当前配置参数

-- 查看所有配置参数
SHOW ALL;

-- 查看特定配置参数
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;

-- 使用SQL查询查看配置参数
SELECT name, setting, unit, short_desc 
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');

-- 查看配置参数的详细信息
SELECT 
    name, 
    setting, 
    unit, 
    category, 
    short_desc, 
    extra_desc,
    context,  -- 参数生效的上下文
    vartype,  -- 参数类型
    source    -- 参数来源
FROM pg_settings 
WHERE name = 'shared_buffers';

会话级别设置

-- 在当前会话中设置参数（临时生效）
SET work_mem = '256MB';
SET enable_seqscan = off;
SET log_statement = 'all';

-- 使用SET LOCAL在当前事务中设置参数
BEGIN;
SET LOCAL work_mem = '512MB';
-- 执行一些查询
SELECT * FROM large_table WHERE condition = 'value';
COMMIT;
-- 事务结束后，work_mem恢复为会话级别或全局级别设置

会话级别设置的特点：

• 只在当前数据库会话中生效
• 会话结束后自动恢复为默认值
• 不会影响其他会话
• 适合临时调整参数进行特定操作

用户级别设置

-- 为特定用户设置默认参数
ALTER USER username SET work_mem = '128MB';
ALTER USER username SET search_path = '"$user", public, schema1';

-- 为所有用户设置默认参数
ALTER DATABASE database_name SET work_mem = '64MB';

-- 查看用户的参数设置
SELECT usename, useconfig FROM pg_user WHERE usename = 'username';

-- 重置用户的参数设置
ALTER USER username RESET work_mem;

通过postgresql.conf文件设置

# postgresql.conf文件示例
# 内存相关参数
shared_buffers = 256MB
work_mem = 4MB
maintenance_work_mem = 64MB
effective_cache_size = 1GB

# 连接相关参数
max_connections = 100
superuser_reserved_connections = 3

# WAL相关参数
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB

# 查询优化器参数
random_page_cost = 1.1
seq_page_cost = 1.0
effective_io_concurrency = 200

# 自动清理参数
autovacuum = on
log_autovacuum_min_duration = 0

# 日志相关参数
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'error'
log_min_duration_statement = 1000

修改postgresql.conf文件后需要重启PostgreSQL服务才能生效：

# 在Linux系统中重启PostgreSQL
sudo systemctl restart postgresql

# 或者使用pg_ctl命令
pg_ctl restart -D /path/to/data/directory

# 在Windows系统中重启PostgreSQL服务
net stop postgresql-x64-13
net start postgresql-x64-13

使用ALTER SYSTEM命令设置

-- 使用ALTER SYSTEM设置全局参数（PostgreSQL 9.4+）
ALTER SYSTEM SET shared_buffers = '512MB';
ALTER SYSTEM SET work_mem = '8MB';
ALTER SYSTEM SET max_connections = 200;

-- 查看通过ALTER SYSTEM设置的参数
SELECT name, setting FROM pg_file_settings WHERE name IN ('shared_buffers', 'work_mem');

-- 重新加载配置（无需重启服务器）
SELECT pg_reload_conf();

-- 或者在psql中使用命令
-- SELECT pg_reload_conf();
-- \! pg_ctl reload -D /path/to/data/directory

-- 重置参数为默认值
ALTER SYSTEM RESET work_mem;
ALTER SYSTEM RESET ALL;  -- 重置所有通过ALTER SYSTEM设置的参数

ALTER SYSTEM命令的特点：

• 将参数设置保存到postgresql.auto.conf文件中
• 不需要直接编辑配置文件
• 可以通过pg_reload_conf()函数重新加载配置，无需重启服务器
• 具有比postgresql.conf更高的优先级

参数优先级顺序

-- 优先级顺序（从高到低）：
-- 1. 会话级别设置（SET命令）
-- 2. 用户级别设置（ALTER USER ... SET）
-- 3. 数据库级别设置（ALTER DATABASE ... SET）
-- 4. ALTER SYSTEM设置（postgresql.auto.conf）
-- 5. 命令行启动选项（-c参数）
-- 6. postgresql.conf文件
-- 7. 默认值

-- 查看参数的实际值和来源
SELECT 
    name, 
    setting, 
    source, 
    sourcefile, 
    sourceline
FROM pg_settings 
WHERE name = 'work_mem';

示例说明不同优先级的效果：

-- 假设postgresql.conf中设置：work_mem = '4MB'
-- 假设ALTER SYSTEM设置：work_mem = '8MB'
-- 假设为用户test_user设置：work_mem = '16MB'

-- 以test_user身份连接数据库
-- 查看当前work_mem值
SHOW work_mem;  -- 结果：16MB（用户级别设置优先）

-- 在会话中临时修改
SET work_mem = '32MB';
SHOW work_mem;  -- 结果：32MB（会话级别设置优先）

-- 重置会话级别设置
RESET work_mem;
SHOW work_mem;  -- 结果：16MB（恢复为用户级别设置）

内存相关参数

-- shared_buffers
-- 设置PostgreSQL使用的共享内存缓冲区大小
-- 建议设置为系统内存的25%
ALTER SYSTEM SET shared_buffers = '256MB';

-- work_mem
-- 设置每个查询操作（如排序、哈希表）可以使用的内存量
-- 根据并发查询数量和系统内存合理设置
ALTER SYSTEM SET work_mem = '4MB';

-- maintenance_work_mem
-- 设置维护操作（如VACUUM、CREATE INDEX）可以使用的内存量
ALTER SYSTEM SET maintenance_work_mem = '64MB';

-- effective_cache_size
-- 告诉查询优化器操作系统和其他进程可用的缓存大小
ALTER SYSTEM SET effective_cache_size = '1GB';

WAL（Write-Ahead Logging）相关参数

-- wal_level
-- 设置WAL日志的详细程度（minimal, replica, logical）
ALTER SYSTEM SET wal_level = 'replica';

-- max_wal_size和min_wal_size
-- 控制WAL日志文件的最大和最小大小
ALTER SYSTEM SET max_wal_size = '1GB';
ALTER SYSTEM SET min_wal_size = '80MB';

-- checkpoint_completion_target
-- 设置检查点完成目标（0.0-1.0）
ALTER SYSTEM SET checkpoint_completion_target = '0.7';

-- wal_buffers
-- 设置WAL缓冲区大小
ALTER SYSTEM SET wal_buffers = '16MB';

连接和并发相关参数

-- max_connections
-- 设置最大并发连接数
ALTER SYSTEM SET max_connections = 100;

-- superuser_reserved_connections
-- 为超级用户保留的连接数
ALTER SYSTEM SET superuser_reserved_connections = 3;

-- max_prepared_transactions
-- 设置最大预处理事务数（用于两阶段提交）
ALTER SYSTEM SET max_prepared_transactions = 0;  -- 0表示禁用

查询优化相关参数

-- random_page_cost和seq_page_cost
-- 设置随机页面访问和顺序页面访问的成本
-- 对于SSD，建议random_page_cost = 1.1
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET seq_page_cost = '1.0';

-- effective_io_concurrency
-- 设置并发I/O操作的数量（适用于SSD）
ALTER SYSTEM SET effective_io_concurrency = 200;

-- default_statistics_target
-- 设置默认统计信息收集目标
ALTER SYSTEM SET default_statistics_target = 100;

性能调优建议

-- 1. 根据系统资源合理设置内存参数
-- 对于8GB内存的系统示例配置：
ALTER SYSTEM SET shared_buffers = '2GB';        -- 25%系统内存
ALTER SYSTEM SET effective_cache_size = '6GB';   -- 75%系统内存
ALTER SYSTEM SET work_mem = '32MB';              -- 根据并发查询调整
ALTER SYSTEM SET maintenance_work_mem = '512MB'; -- 维护操作内存

-- 2. 根据存储类型调整I/O参数
-- 对于SSD存储：
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET effective_io_concurrency = 200;

-- 3. 启用自动清理
ALTER SYSTEM SET autovacuum = 'on';
ALTER SYSTEM SET log_autovacuum_min_duration = '0';

-- 4. 配置适当的日志记录
ALTER SYSTEM SET log_destination = 'stderr';
ALTER SYSTEM SET logging_collector = 'on';
ALTER SYSTEM SET log_directory = 'log';
ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log';
ALTER SYSTEM SET log_statement = 'error';
ALTER SYSTEM SET log_min_duration_statement = '1000';  -- 记录超过1秒的查询

监控和调整参数

-- 监控当前参数设置
SELECT 
    name, 
    setting, 
    unit, 
    short_desc
FROM pg_settings 
WHERE name IN (
    'shared_buffers', 
    'work_mem', 
    'maintenance_work_mem',
    'effective_cache_size',
    'max_connections',
    'autovacuum'
)
ORDER BY name;

-- 检查参数是否需要调整
-- 1. 检查共享内存使用情况
SELECT 
    buffers_clean, 
    maxwritten_clean, 
    buffers_backend_fsync
FROM pg_stat_bgwriter;

-- 2. 检查连接使用情况
SELECT 
    max_conn, 
    used, 
    res_for_super, 
    max_conn - used - res_for_super AS res_for_normal
FROM (
    SELECT 
        setting::int AS max_conn,
        (SELECT count(*) FROM pg_stat_activity) AS used,
        (SELECT setting::int FROM pg_settings WHERE name = 'superuser_reserved_connections') AS res_for_super
    FROM pg_settings 
    WHERE name = 'max_connections'
) t;

-- 3. 检查WAL日志使用情况
SELECT 
    wal_records, 
    wal_fpi, 
    wal_bytes, 
    wal_buffers_full, 
    wal_write
FROM pg_stat_wal;