centos postgresql升级与维护指南

CentOS 上 PostgreSQL 升级与维护指南

一 升级总览与准备

• 版本区分与影响
  • 小版本升级（如 14.5 → 14.7）：仅补丁修复，存储格式不变，通常可直接替换二进制并重启。
  • 大版本升级（如 14.x → 15.x）：内部存储格式可能变化，需使用 pg_upgrade 或逻辑迁移，且需逐版本阅读发行说明的 Migration 部分。跨多个主版本需依次评估中间版本差异。
• 升级前准备
  • 完整备份：物理与逻辑双保险。
    • 物理：pg_basebackup -D /backup/base -U postgres -P -Fp -Xs -R
    • 逻辑：pg_dumpall -U postgres > /backup/alldb.sql
  • 检查现状：sudo -u postgres psql -c "SELECT version();"；梳理扩展、FDW、外部字典、自定义函数等兼容性。
  • 资源与窗口：确保磁盘空间充足（升级时新旧数据可能并存），选择低峰维护窗口并做好回滚预案。

二 小版本升级步骤 CentOS

• 适用场景：同一主版本内的补丁升级（如 14.5 → 14.7）。
• 操作流程
  1. 切换维护窗口并停止服务：sudo systemctl stop postgresql-<主版本>（如 postgresql-14）
  2. 更新软件包：sudo yum update postgresql<主版本>-server postgresql<主版本>-contrib
  3. 启动并验证：sudo systemctl start postgresql-<主版本>；sudo -u postgres psql -c "SELECT version();"
  4. 如启用 SELinux，必要时执行：sudo restorecon -R /var/lib/pgsql/<主版本>/data
  5. 回滚预案：保留旧包与数据目录，异常时停止新版本、恢复旧包并重启。

三 大版本升级步骤 CentOS

• 方案对比
  • pg_upgrade：速度快、适合大中型库（> 100 GB），本机就地升级，分钟级完成；支持 --link 硬链接以节省空间（升级完成前严禁回滚）。
  • 逻辑迁移（pg_dump/pg_dumpall）：最稳妥、跨平台/跨版本兼容性好，适合中小型库（< 100 GB），但停机时间与数据量成正比。
  • 逻辑复制：适合大中型库，几乎零停机，但需应用适配与复制槽管理。
• 操作步骤（以 14.x → 15.x 为例，pg_upgrade）
  1. 安装新版本仓库与软件包
     • EL7 示例：sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
     • 安装新版本：sudo yum install -y postgresql15-server
  2. 初始化新集群：sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
  3. 停止旧版本：sudo systemctl stop postgresql-14
  4. 兼容性检查（关键）：

     sudo -u postgres /usr/pgsql-15/bin/pg_upgrade \
       -b /usr/pgsql-14/bin -B /usr/pgsql-15/bin \
       -d /var/lib/pgsql/14/data -D /var/lib/pgsql/15/data \
       --check
     

     按提示修复扩展、参数、文件权限等问题后再继续。
  5. 执行升级（示例不使用硬链接）

     sudo -u postgres /usr/pgsql-15/bin/pg_upgrade \
       -b /usr/pgsql-14/bin -B /usr/pgsql-15/bin \
       -d /var/lib/pgsql/14/data -D /var/lib/pgsql/15/data
     

     如需节省空间且确认可回滚窗口已过，可加 --link（升级完成前勿回滚）。
  6. 启动新版本：sudo systemctl start postgresql-15
  7. 更新统计信息：sudo -u postgres /usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages
  8. 验证：sudo -u postgres psql -c "SELECT version();"，抽样检查表数量、扩展、复制槽、FDW 等。
  9. 回滚预案：升级失败或异常，停止新版本，恢复旧版本软件包与数据目录后重启；使用 --link 时切勿在升级完成前回滚。
  10. 清理：确认稳定后移除旧版本包与旧数据目录（可选）。
• 逻辑迁移简要流程（备选）
  • 停写后导出：sudo -u postgres pg_dumpall > /backup/alldb.sql
  • 初始化新集群：sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
  • 启动新实例：sudo systemctl start postgresql-15
  • 导入：sudo -u postgres psql -f /backup/alldb.sql postgres
  • 验证与切换应用连接。

四 日常维护要点

• 备份与恢复
  • 物理备份 + WAL 归档：pg_basebackup -D /backup/base -U postgres -P -Fp -Xs -R；定期在测试环境验证可恢复性。
  • 逻辑备份：pg_dump/pg_dumpall 用于迁移、克隆与环境搭建；定时任务示例：
    • 0 2 * * * pg_dump -U postgres dbname | gzip > /backup/db_$(date +\%F).sql.gz
• 空间与膨胀治理
  • 确保 autovacuum 开启并合理调参；按需执行 VACUUM ANALYZE，谨慎使用 VACUUM FULL（会锁表）。
  • 索引维护：监控未使用/低效索引，必要时 REINDEX（或 REINDEX CONCURRENTLY 降低影响）。
• 监控与日志
  • 内置视图：pg_stat_activity、pg_stat_bgwriter、pg_stat_user_tables、pg_locks 等定位会话、阻塞、写入与表膨胀。
  • 日志建议：log_destination='csvlog'，logging_collector=on，log_min_duration_statement=1000（记录 >1s 慢 SQL），配合 pgBadger 做日志分析。
• 性能与参数
  • 使用 pg_stat_statements 定位高频低效 SQL；用 EXPLAIN (ANALYZE) 优化执行计划。
  • 结合负载与硬件，调优 shared_buffers、work_mem、maintenance_work_mem 等关键参数（可借助 pgTune 做初版建议）。

五 常见问题与排错清单

• 扩展/字典/外部文件缺失：升级前确认新版本已安装对应扩展与文件，升级后再创建/升级扩展对象，避免直接用旧扩展脚本覆盖。
• 端口与并存：新旧版本并存需配置不同端口（如 5432/5433），验证无误后再切换应用。
• 升级检查失败：按 pg_upgrade --check 输出逐项修复（扩展版本、参数废弃、文件权限、第三方组件）。
• 统计信息缺失导致计划退化：升级后执行 vacuumdb --all --analyze-in-stages。
• 备份有效性：定期恢复演练，确保物理与逻辑备份均可用；WAL 归档链路与保留策略需验证。