PostgreSQL 性能调优与运维

HSXISAWD2026/3/20大约 16 分钟

PostgreSQL 性能调优与运维

一、性能调优概述

1.1 性能优化层次

1.2 性能问题诊断流程

-- 1. 检查系统整体状态
SELECT
    numbackends AS connections,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    blks_read AS disk_reads,
    blks_hit AS cache_hits,
    round(100.0 * blks_hit / NULLIF(blks_read + blks_hit, 0), 2) AS cache_hit_ratio,
    tup_returned AS rows_returned,
    tup_fetched AS rows_fetched,
    tup_inserted AS rows_inserted,
    tup_updated AS rows_updated,
    tup_deleted AS rows_deleted
FROM pg_stat_database
WHERE datname = current_database();

-- 2. 检查活动连接
SELECT
    state,
    COUNT(*) AS count,
    MAX(NOW() - query_start) AS max_duration
FROM pg_stat_activity
GROUP BY state;

-- 3. 检查慢查询
SELECT
    pid,
    NOW() - query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < NOW() - INTERVAL '5 seconds'
ORDER BY duration DESC;

-- 4. 检查锁等待
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON
    blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

二、内存配置

2.1 共享内存配置

# postgresql.conf

# 共享缓冲区 - 最重要的内存配置
# 建议值：物理内存的 25%，最大不超过 8GB（32 位系统）
# 过大可能导致 checkpoint 时间过长
shared_buffers = 4GB

# 有效缓存大小 - 用于优化器估算
# 建议值：物理内存的 50-75%
# 包括 shared_buffers + OS 文件缓存
effective_cache_size = 12GB

# WAL 缓冲区
# 建议值：shared_buffers 的 1/32，至少 64MB
wal_buffers = 128MB

# 维护操作内存（VACUUM、CREATE INDEX 等）
# 建议值：物理内存的 5%，通常 512MB-2GB
maintenance_work_mem = 1GB

# 自动清理工作内存
# 建议值：与 maintenance_work_mem 相同或更小
autovacuum_work_mem = 512MB

2.2 进程内存配置

# 每个连接的工作内存（排序、哈希等）
# 注意：每个查询可能使用多个 work_mem
# 总内存 = work_mem * 并发查询数 * 每查询操作数
# 建议值：总内存 / (max_connections * 2)
work_mem = 64MB

# 临时缓冲区（临时表）
temp_buffers = 32MB

# 哈希聚合内存限制
hash_mem_multiplier = 1.0

# 逻辑解码工作内存
logical_decoding_work_mem = 64MB

2.3 内存调优示例

-- 查看当前内存配置
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'effective_cache_size', 'work_mem',
    'maintenance_work_mem', 'wal_buffers', 'temp_buffers'
);

-- 查看共享缓冲区使用情况（需要 pg_buffercache 扩展）
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT
    c.relname,
    COUNT(*) AS buffers,
    pg_size_pretty(COUNT(*) * 8192) AS size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 20;

-- 计算缓存命中率
SELECT
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    round(sum(heap_blks_hit) * 100.0 / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS ratio
FROM pg_statio_user_tables;

-- 索引缓存命中率
SELECT
    sum(idx_blks_read) AS idx_read,
    sum(idx_blks_hit) AS idx_hit,
    round(sum(idx_blks_hit) * 100.0 / NULLIF(sum(idx_blks_hit) + sum(idx_blks_read), 0), 2) AS ratio
FROM pg_statio_user_indexes;

三、磁盘 I/O 配置

3.1 检查点配置

# 检查点触发条件
checkpoint_timeout = 10min        # 时间间隔（默认 5min）
max_wal_size = 4GB               # WAL 大小触发（默认 1GB）
min_wal_size = 1GB               # 最小保留 WAL

# 检查点完成目标（平滑写入）
# 建议值：0.9，使检查点在下一次之前的 90% 时间内完成
checkpoint_completion_target = 0.9

# 检查点警告
checkpoint_warning = 30s

# 后台写进程
bgwriter_delay = 200ms           # 休眠间隔
bgwriter_lru_maxpages = 100      # 每轮最大写入页数
bgwriter_lru_multiplier = 2.0    # 需要清理的页数乘数
bgwriter_flush_after = 512kB     # 强制刷盘阈值

3.2 WAL 配置

# WAL 级别
wal_level = replica              # minimal, replica, logical

# 同步提交
synchronous_commit = on          # on, off, local, remote_write, remote_apply

# WAL 写入
wal_sync_method = fdatasync      # fsync, fdatasync, open_sync, open_datasync
full_page_writes = on            # 首次修改后写入完整页（崩溃恢复需要）

# WAL 压缩（PostgreSQL 15+）
wal_compression = off

# WAL 段大小（只能在 initdb 时设置）
# 默认 16MB，大写入量可以增加到 64MB 或 256MB

3.3 SSD 优化

# SSD 优化配置
random_page_cost = 1.1           # SSD 随机读接近顺序读（HDD 默认 4.0）
effective_io_concurrency = 200   # SSD 支持高并发 IO（HDD 建议 2）

# 并行查询
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# 预读（Linux）
# echo 256 > /sys/block/sda/queue/read_ahead_kb

四、连接与并发配置

4.1 连接配置

# 最大连接数
# 每个连接消耗约 5-10MB 内存
max_connections = 200

# 超级用户保留连接
superuser_reserved_connections = 3

# TCP 设置
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10

# 连接超时
authentication_timeout = 1min

4.2 连接池配置（PgBouncer）

# pgbouncer.ini
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# 连接池模式
pool_mode = transaction          # session, transaction, statement

# 连接池大小
default_pool_size = 25           # 每用户/数据库的连接数
min_pool_size = 5
max_client_conn = 1000           # 最大客户端连接

# 超时设置
server_connect_timeout = 5
server_login_retry = 3
query_timeout = 0
client_idle_timeout = 0
server_idle_timeout = 600

# 连接生命周期
server_lifetime = 3600
server_round_robin = 1

4.3 并发控制

# 锁超时
lock_timeout = 0                 # 0 表示无限等待
deadlock_timeout = 1s            # 死锁检测间隔

# 语句超时
statement_timeout = 0            # 0 表示无限（生产环境建议设置）
idle_in_transaction_session_timeout = 0

# 并行查询
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
parallel_leader_participation = on

五、VACUUM 与 ANALYZE

5.1 VACUUM 原理

5.2 自动 VACUUM 配置

# 启用自动 VACUUM
autovacuum = on

# 自动 VACUUM 工作进程数
autovacuum_max_workers = 3

# 检查间隔
autovacuum_naptime = 1min

# VACUUM 触发条件：dead_tuples > threshold + scale_factor * live_tuples
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1     # 10% 死元组触发

# ANALYZE 触发条件
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05   # 5% 变更触发

# 冻结年龄触发
autovacuum_freeze_max_age = 200000000

# 资源限制（防止影响正常业务）
autovacuum_vacuum_cost_delay = 2ms       # 每次操作后休眠
autovacuum_vacuum_cost_limit = 200       # 成本累计上限

# 单表配置覆盖
ALTER TABLE hot_table SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.01
);

5.3 VACUUM 监控

-- 查看需要 VACUUM 的表
SELECT
    schemaname || '.' || relname AS table_name,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 查看表膨胀情况
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- 查看事务 ID 年龄（防止回卷）
SELECT
    datname,
    age(datfrozenxid) AS age,
    2^31 - age(datfrozenxid) AS remaining
FROM pg_database
ORDER BY age DESC;

-- 查看表的年龄
SELECT
    c.oid::regclass AS table_name,
    age(c.relfrozenxid) AS age,
    pg_size_pretty(pg_table_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age DESC
LIMIT 20;

-- 手动 VACUUM
VACUUM VERBOSE users;              -- 标准 VACUUM
VACUUM ANALYZE users;              -- VACUUM + ANALYZE
VACUUM FULL users;                 -- 完全清理（锁表）
VACUUM FREEZE users;               -- 冻结事务 ID

六、监控与告警

6.1 关键指标监控

-- 创建监控视图
CREATE VIEW v_pg_monitor AS
SELECT
    -- 连接信息
    (SELECT count(*) FROM pg_stat_activity) AS total_connections,
    (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS active_connections,
    (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
    (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_in_transaction,

    -- 缓存命中率
    (SELECT round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2)
     FROM pg_stat_database) AS cache_hit_ratio,

    -- 事务统计
    (SELECT sum(xact_commit) FROM pg_stat_database) AS total_commits,
    (SELECT sum(xact_rollback) FROM pg_stat_database) AS total_rollbacks,

    -- 死元组
    (SELECT sum(n_dead_tup) FROM pg_stat_user_tables) AS total_dead_tuples,

    -- WAL 位置
    pg_current_wal_lsn() AS current_wal_lsn,

    -- 复制延迟（如果是主库）
    (SELECT max(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn))
     FROM pg_stat_replication) AS max_replication_lag;

-- 查看慢查询（使用 pg_stat_statements）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    round(total_exec_time::numeric, 2) AS total_time_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 查看表访问统计
SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

-- 查看索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

6.2 日志配置

# postgresql.conf

# 日志目标
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# 日志级别
log_min_messages = warning
log_min_error_statement = error

# 慢查询日志
log_min_duration_statement = 1000   # 记录超过 1 秒的查询
log_statement = 'ddl'               # none, ddl, mod, all

# 连接日志
log_connections = on
log_disconnections = on

# 检查点日志
log_checkpoints = on

# 锁等待日志
log_lock_waits = on
deadlock_timeout = 1s

# 临时文件日志
log_temp_files = 0                  # 记录所有临时文件

# 自动 VACUUM 日志
log_autovacuum_min_duration = 0     # 记录所有自动 VACUUM

6.3 Prometheus + Grafana 监控

# postgres_exporter 配置
# docker-compose.yml
version: "3"
services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://user:pass@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"

# prometheus.yml
scrape_configs:
  - job_name: "postgresql"
    static_configs:
      - targets: ["postgres-exporter:9187"]

-- 自定义监控 SQL（postgres_exporter queries.yaml）
pg_replication:
  query: "SELECT CASE WHEN pg_is_in_recovery() THEN 1 ELSE 0 END AS is_replica,
          COALESCE(EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp())), 0) AS lag_seconds"
  metrics:
    - is_replica:
        usage: "GAUGE"
        description: "Is this a replica"
    - lag_seconds:
        usage: "GAUGE"
        description: "Replication lag in seconds"

pg_database_size:
  query: "SELECT datname, pg_database_size(datname) AS size_bytes FROM pg_database"
  metrics:
    - datname:
        usage: "LABEL"
        description: "Database name"
    - size_bytes:
        usage: "GAUGE"
        description: "Database size in bytes"

七、备份与恢复

7.1 备份策略

7.2 pg_dump 备份

# 备份单个数据库
pg_dump -h localhost -U postgres -Fc -f backup.dump mydb

# 参数说明
# -Fc: 自定义格式（压缩，推荐）
# -Fp: 纯文本格式（可读）
# -Fd: 目录格式（并行备份）
# -Ft: tar 格式

# 并行备份（目录格式）
pg_dump -h localhost -U postgres -Fd -j 4 -f backup_dir mydb

# 备份特定表
pg_dump -h localhost -U postgres -t users -t orders -Fc -f tables.dump mydb

# 只备份结构
pg_dump -h localhost -U postgres -s -Fc -f schema.dump mydb

# 只备份数据
pg_dump -h localhost -U postgres -a -Fc -f data.dump mydb

# 备份所有数据库
pg_dumpall -h localhost -U postgres -f all_databases.sql

# 只备份角色和表空间
pg_dumpall -h localhost -U postgres -r -f roles.sql

# 恢复
pg_restore -h localhost -U postgres -d mydb backup.dump

# 并行恢复
pg_restore -h localhost -U postgres -d mydb -j 4 backup_dir

# 恢复到新数据库
createdb -h localhost -U postgres newdb
pg_restore -h localhost -U postgres -d newdb backup.dump

7.3 pg_basebackup 备份

# 基础备份
pg_basebackup -h localhost -U replicator -D /backup/base -Fp -Xs -P

# 参数说明
# -Fp: 纯文本格式
# -Ft: tar 格式
# -Xs: 使用流复制传输 WAL
# -Xf: 使用 fetch 方式传输 WAL
# -P: 显示进度
# -R: 生成复制配置

# tar 格式备份（推荐）
pg_basebackup -h localhost -U replicator -D /backup/base -Ft -z -Xs -P

# 带复制配置的备份（用于创建从库）
pg_basebackup -h localhost -U replicator -D /var/lib/postgresql/data -Fp -Xs -P -R

# 配置 WAL 归档
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /archive/%f'
# 或使用远程存储
# archive_command = 'aws s3 cp %p s3://bucket/archive/%f'

7.4 时间点恢复（PITR）

# 1. 准备恢复环境
# 停止 PostgreSQL
systemctl stop postgresql

# 清空数据目录
rm -rf /var/lib/postgresql/data/*

# 2. 恢复基础备份
# tar 格式
tar -xzf /backup/base/base.tar.gz -C /var/lib/postgresql/data

# 3. 配置恢复参数
cat > /var/lib/postgresql/data/postgresql.auto.conf << EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-01-15 10:30:00'
recovery_target_action = 'promote'
EOF

# 4. 创建恢复信号文件
touch /var/lib/postgresql/data/recovery.signal

# 5. 启动数据库
systemctl start postgresql

# 6. 检查恢复状态
psql -c "SELECT pg_is_in_recovery();"

# 恢复目标选项
# recovery_target_time = '时间点'
# recovery_target_xid = '事务ID'
# recovery_target_lsn = 'WAL位置'
# recovery_target_name = '还原点名称'

# 创建还原点
SELECT pg_create_restore_point('before_migration');

7.5 自动备份脚本

#!/bin/bash
# backup.sh

# 配置
BACKUP_DIR="/backup/postgresql"
ARCHIVE_DIR="/archive"
RETENTION_DAYS=7
DB_HOST="localhost"
DB_USER="postgres"
DATE=$(date +%Y%m%d_%H%M%S)

# 创建目录
mkdir -p $BACKUP_DIR/$DATE

# 执行备份
echo "Starting backup at $(date)"

# 全量备份
pg_basebackup -h $DB_HOST -U $DB_USER \
    -D $BACKUP_DIR/$DATE \
    -Ft -z -Xs -P \
    --checkpoint=fast

# 检查备份结果
if [ $? -eq 0 ]; then
    echo "Backup completed successfully"

    # 记录 WAL 位置
    psql -h $DB_HOST -U $DB_USER -c "SELECT pg_current_wal_lsn();" > $BACKUP_DIR/$DATE/wal_position.txt

    # 清理旧备份
    find $BACKUP_DIR -maxdepth 1 -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;
    echo "Old backups cleaned"
else
    echo "Backup failed!"
    exit 1
fi

# 清理旧 WAL 归档
find $ARCHIVE_DIR -name "*.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup finished at $(date)"

八、日常运维

8.1 健康检查清单

-- 1. 连接数检查
SELECT
    max_conn.setting::int AS max_connections,
    current_conn.count AS current_connections,
    round(current_conn.count * 100.0 / max_conn.setting::int, 2) AS usage_percent
FROM (SELECT setting FROM pg_settings WHERE name = 'max_connections') max_conn,
     (SELECT count(*) FROM pg_stat_activity) current_conn;

-- 2. 数据库大小
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 3. 表大小 TOP 20
SELECT
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

-- 4. 未使用的索引
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
    idx_scan
FROM pg_stat_user_indexes i
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- 5. 表膨胀检查
SELECT
    schemaname || '.' || relname AS table_name,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 6. 长事务检查
SELECT
    pid,
    usename,
    application_name,
    state,
    NOW() - xact_start AS transaction_duration,
    NOW() - query_start AS query_duration,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND state != 'idle'
ORDER BY xact_start;

-- 7. 复制状态检查（主库）
SELECT
    client_addr,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication;

8.2 常用运维命令

# 重新加载配置（不重启）
pg_ctl reload -D /var/lib/postgresql/data
# 或
SELECT pg_reload_conf();

# 优雅关闭
pg_ctl stop -D /var/lib/postgresql/data -m smart   # 等待连接关闭
pg_ctl stop -D /var/lib/postgresql/data -m fast    # 回滚活动事务
pg_ctl stop -D /var/lib/postgresql/data -m immediate # 立即停止

# 查看配置文件位置
SHOW config_file;
SHOW hba_file;
SHOW data_directory;

# 终止查询
SELECT pg_cancel_backend(pid);    -- 取消查询
SELECT pg_terminate_backend(pid); -- 终止连接

# 批量终止空闲连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND query_start < NOW() - INTERVAL '1 hour';

# 查看锁并终止
SELECT pg_terminate_backend(pid)
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE NOT l.granted
AND a.query_start < NOW() - INTERVAL '5 minutes';

8.3 版本升级

# 小版本升级（14.1 -> 14.5）
# 直接替换二进制文件即可

# 大版本升级（14 -> 15）
# 方法 1：pg_dump 导出导入
pg_dumpall -h old_server -U postgres > all_databases.sql
psql -h new_server -U postgres -f all_databases.sql

# 方法 2：pg_upgrade（就地升级）
# 1. 安装新版本
# 2. 停止旧实例
# 3. 初始化新实例
/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/data

# 4. 运行 pg_upgrade
/usr/lib/postgresql/15/bin/pg_upgrade \
    -b /usr/lib/postgresql/14/bin \
    -B /usr/lib/postgresql/15/bin \
    -d /var/lib/postgresql/14/data \
    -D /var/lib/postgresql/15/data \
    -p 5432 -P 5433 \
    --check  # 先检查

# 5. 实际升级
/usr/lib/postgresql/15/bin/pg_upgrade \
    -b /usr/lib/postgresql/14/bin \
    -B /usr/lib/postgresql/15/bin \
    -d /var/lib/postgresql/14/data \
    -D /var/lib/postgresql/15/data

# 方法 3：逻辑复制升级
# 配置逻辑复制从旧版本到新版本
# 切换完成后停止旧实例

九、总结

性能调优检查清单

类别	检查项	建议值/操作
内存	shared_buffers	物理内存 25%
内存	effective_cache_size	物理内存 50-75%
内存	work_mem	总内存/(连接数*2)
磁盘	random_page_cost	SSD: 1.1, HDD: 4.0
磁盘	checkpoint_completion_target	0.9
连接	max_connections	根据实际需要
连接	使用连接池	PgBouncer
VACUUM	autovacuum	确保开启
监控	pg_stat_statements	分析慢查询
备份	定期备份	每日全量 + WAL 归档

运维最佳实践

监控先行：建立完善的监控和告警
定期维护：VACUUM、ANALYZE、REINDEX
容量规划：监控增长趋势，提前扩容
备份验证：定期测试备份恢复流程
文档记录：记录配置变更和问题处理
演练测试：定期进行故障转移演练