Debian PostgreSQL如何升级与维护

Debian 上 PostgreSQL 的升级与维护指南

一 升级总览与准备

• 明确升级类型：
  • 小版本（如 14.5 → 14.7）：通过 APT 更新即可，风险低。
  • 大版本（如 14.x → 15.x）：需安装新版本并迁移数据，常用方式为 pg_upgrade（速度快）或逻辑转储（兼容性好）。
• 准备与评估：
  • 完整备份（停机窗口内执行）：sudo -u postgres pg_dumpall > /path/to/backup.sql。
  • 检查现状：sudo -u postgres psql -c "SELECT version();"。
  • 阅读目标版本的兼容性说明与弃用项。
  • 规划维护窗口、回滚方案与验证步骤。

二 小版本升级步骤 Debian

• 更新索引并就地升级对应主版本包：
  • sudo apt update
  • sudo apt install --only-upgrade postgresql-<主版本号>（如：postgresql-14）
• 重启服务并验证：
  • sudo systemctl restart postgresql
  • sudo -u postgres psql -c "SELECT version();"
• 说明：Debian 的 PostgreSQL 包以主版本号区分实例（如 14、15），小版本补丁由包管理器自动处理。

三 大版本升级步骤 Debian

• 安装新版本二进制（建议使用官方仓库）：
  • 添加源与密钥：
    • echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
    • wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.ASC | sudo apt-key add -
    • sudo apt update
  • 安装新主版本（如 15）：sudo apt install postgresql-15
• 准备新集群（若未自动初始化）：
  • sudo -u postgres /usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main
• 数据迁移（二选一）：
  • 方式 A（推荐，速度快）：pg_upgrade
    • 检查兼容性：
      • sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/15/bin -d /var/lib/postgresql/14/main -D /var/lib/postgresql/15/main --check
    • 执行升级：
      • sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/15/bin -d /var/lib/postgresql/14/main -D /var/lib/postgresql/15/main -o '-c config_file=/etc/postgresql/14/main/postgresql.conf' -O '-c config_file=/etc/postgresql/15/main/postgresql.conf'
    • 重建统计信息：sudo -u postgres /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages
  • 方式 B（逻辑转储，兼容性好）：
    • 导出：sudo -u postgres pg_dumpall -f /path/to/backup.sql
    • 初始化新集群：sudo -u postgres /usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main
    • 导入：sudo -u postgres psql -f /path/to/backup.sql postgres
• 配置与启动：
  • 复制或合并配置：/etc/postgresql/<旧版本>/main/*.conf → /etc/postgresql/<新版本>/main/（注意端口、ssl、shared_preload_libraries 等差异）。
  • 若需并行验证，可临时将新实例端口改为 5433，验证无误后切回 5432。
  • 启动新实例：sudo systemctl start postgresql@15-main（或 pg_ctlcluster 15 main start）。
• 验证与回滚：
  • 验证：sudo -u postgres psql -c "SELECT version();"、检查关键业务、日志与复制状态。
  • 回滚：切回旧实例端口或利用备份执行逻辑恢复。

四 日常维护要点

• 备份策略：
  • 全量：sudo -u postgres pg_dumpall > /path/to/full_$(date +%F).sql（建议纳入定时任务与异地存储）。
  • 按库：pg_dump -U <用户> -d <库名> -f <库>.sql；大型库可用 pg_dump | gzip 或 pg_basebackup（物理备份，配合 WAL 归档）。
• 自动化维护：
  • 确保 autovacuum 开启并合理配置阈值；定期执行 VACUUM/ANALYZE，升级后用 vacuumdb --all --analyze-in-stages 重建统计信息。
• 监控与日志：
  • 关注 /var/log/postgresql/ 日志；监控连接数、锁、复制延迟、慢查询等。
• 安全加固：
  • 使用 scram-sha-256 认证，限制 pg_hba.conf 来源网段，仅开放必要端口，启用 SSL/TLS，按需配置防火墙。
• 配置调优（示例起点，视内存与工作负载调整）：
  • shared_buffers（常设为内存的约 25%）、work_mem、maintenance_work_mem、effective_cache_size、max_connections；合理设置 log_statement/log_min_duration_statement 以捕获慢查询。

五 常见问题与排错

• 新旧版本并存端口冲突：为新实例设置不同端口（如 5433），验证完成后再切换。
• pg_upgrade 检查失败：逐项处理不兼容对象（如自定义函数/扩展版本）、检查 shared_preload_libraries、文件权限与磁盘空间。
• 服务无法启动：核对 postgresql.conf 与 pg_hba.conf 语法、端口占用、数据目录权限（属主应为 postgres:postgres）。
• 逻辑恢复报错：确认转储文件完整、目标集群为空、编码/区域设置一致，必要时按对象逐个导入定位问题。
• 回滚路径：保留旧集群与备份直至新版本稳定运行至少一个维护周期。