PostgreSQL 运维自动化：从 “手动部署到半夜” 到 “一键搞定集群” 的摸鱼指南

手动运维 PostgreSQL 就像 “用手组装精密仪器”—— 搭 1 套主从要 2 小时，改 10 个节点的参数要 1 小时，备份忘了验证还得补，稍微走神就翻车。而 PG 运维自动化是 “用流水线组装仪器”，一键搞定部署、备份、升级，还能自动校验，省下来的时间能多喝两杯奶茶。

这篇是 PostgreSQL 专栏的运维自动化实战篇，核心目标是帮 MySQL 老玩家 “无缝迁移” PG 自动化方案：用 “Ansible+Shell” 组合，覆盖 “一键部署主从集群、自动化备份验证、版本升级回滚、日常运维脚本” 四大模块，每步都附可复现代码和对比 MySQL 的差异，保证你看完能把 PG 运维的重复活砍半，从此告别加班。

一、先扎心：PG 手动运维的 “五大酷刑”

没搞自动化前，PG 运维和 MySQL 一样逃不过 “重复 + 踩坑”，这五个坑几乎人人中过：

先上一张 “手动运维 vs 自动化运维” 对比图，感受下差距：

plaintext

【手动运维流程】
搭主从：查文档→改postgresql.conf→建复制用户→做基础备份→配置流复制→手动查同步状态（发现IO线程没起来）→返工（2小时）
备份：执行pg_basebackup→手动同步到异地→忘记验证→第二天发现备份损坏→补备份（1小时）
 
【自动化运维流程】
搭主从：执行ansible-playbook deploy_pg_cluster.yml→等待15分钟→自动校验同步+备份配置→搞定
备份：Shell脚本自动执行→自动归档WAL→异地同步+数据校验→失败发告警→不用管

二、自动化核心工具：为什么选 “Ansible+Shell”？

很多人觉得自动化要写复杂代码，其实不用 ——Ansible 是 “不用写代码的自动化工具”，用 YAML 配置文件就能发指令；Shell 脚本处理备份、日志清理这些小活，简单直接。这对组合在 PG 运维里比 MySQL 更顺手，因为 PG 原生工具更规范，适配性更强。

工具选型对比表（MySQL vs PG）

选型口诀：批量操作找 Ansible（比如 5 套主从部署），单节点小活找 Shell（比如某台机器日志清理），两者结合，PG 运维效率直接翻倍。

三、实战 1：Ansible 一键部署 PG 主从集群（流复制 + WAL 归档）

手动搭 PG 主从要配置 WAL 归档、基础备份、流复制，步骤繁琐易出错。用 Ansible 写 Playbook，一键跑完所有步骤，还能自动验证同步状态。

1. 环境准备（和 MySQL 自动化一致）

（1）控制节点安装 Ansible 并配置免密登录

bash

# 安装Ansible（CentOS 7）
yum install -y epel-release
yum install -y ansible
 
# 配置免密登录主从节点
ssh-keygen -t rsa  # 一路回车
ssh-copy-id root@192.168.1.100
ssh-copy-id root@192.168.1.101
 
# 配置Ansible主机清单
vi /etc/ansible/hosts
# 加入以下内容
[pg_master]
192.168.1.100
 
[pg_slaves]
192.168.1.101
 
[pg_all:children]
pg_master
pg_slaves

2. 主库部署 Playbook（pg_master_deploy.yml）

yaml

- name: 一键部署PG主库（含WAL归档配置）
  hosts: pg_master
  remote_user: root
  vars:
    pg_version: "16"
    pg_data_dir: "/var/lib/pgsql/{{ pg_version }}/data"
    pg_archive_dir: "/data/pg_archive"
    pg_repl_user: "repl"
    pg_repl_pass: "Repl@123"
    pg_root_pass: "Pg@123456"
  tasks:
    # 1. 安装PG 16（已配置官方源）
    - name: 安装PostgreSQL {{ pg_version }}
      yum:
        name:
          - postgresql{{ pg_version }}
          - postgresql{{ pg_version }}-server
        state: present
 
    # 2. 创建WAL归档目录并授权
    - name: 创建归档目录
      file:
        path: "{{ pg_archive_dir }}"
        state: directory
        owner: postgres
        group: postgres
        mode: 0700
 
    # 3. 初始化PG数据目录
    - name: 初始化数据库
      command: "/usr/pgsql-{{ pg_version }}/bin/postgresql-{{ pg_version }}-setup initdb"
      args:
        creates: "{{ pg_data_dir }}/pg_control"
 
    # 4. 复制主库配置文件（控制节点提前准备好postgresql.conf和pg_hba.conf）
    - name: 复制postgresql.conf
      copy:
        src: "/etc/ansible/pg_conf/master/postgresql.conf"
        dest: "{{ pg_data_dir }}/postgresql.conf"
        owner: postgres
        group: postgres
        mode: 0600
 
    - name: 复制pg_hba.conf
      copy:
        src: "/etc/ansible/pg_conf/master/pg_hba.conf"
        dest: "{{ pg_data_dir }}/pg_hba.conf"
        owner: postgres
        group: postgres
        mode: 0600
 
    # 5. 启动PG并设为开机自启
    - name: 启动PostgreSQL服务
      service:
        name: postgresql-{{ pg_version }}
        state: restarted
        enabled: yes
 
    # 6. 设置postgres用户密码
    - name: 设置root用户密码
      become: yes
      become_user: postgres
      command: psql -c "ALTER USER postgres WITH PASSWORD '{{ pg_root_pass }}';"
 
    # 7. 创建复制用户并授权
    - name: 创建复制用户
      become: yes
      become_user: postgres
      command: >
        psql -c "CREATE USER {{ pg_repl_user }} WITH REPLICATION ENCRYPTED PASSWORD '{{ pg_repl_pass }}';"
 
    # 8. 验证主库状态
    - name: 验证主库是否正常运行
      become: yes
      become_user: postgres
      command: psql -c "SELECT version();"
      register: pg_version_check
      failed_when: "'PostgreSQL' not in pg_version_check.stdout"

3. 从库部署 Playbook（pg_slave_deploy.yml）

yaml

- name: 一键部署PG从库（流复制+热备）
  hosts: pg_slaves
  remote_user: root
  vars:
    pg_version: "16"
    pg_data_dir: "/var/lib/pgsql/{{ pg_version }}/data"
    pg_archive_dir: "/data/pg_archive"
    pg_repl_user: "repl"
    pg_repl_pass: "Repl@123"
    pg_master_ip: "192.168.1.100"
  tasks:
    # 1. 安装PG 16
    - name: 安装PostgreSQL {{ pg_version }}
      yum:
        name:
          - postgresql{{ pg_version }}
          - postgresql{{ pg_version }}-server
        state: present
 
    # 2. 创建归档目录并授权
    - name: 创建归档目录
      file:
        path: "{{ pg_archive_dir }}"
        state: directory
        owner: postgres
        group: postgres
        mode: 0700
 
    # 3. 停止PG，清空默认数据目录
    - name: 停止PostgreSQL服务
      service:
        name: postgresql-{{ pg_version }}
        state: stopped
 
    - name: 清空数据目录
      file:
        path: "{{ pg_data_dir }}"
        state: absent
      notify: recreate data dir
 
    # 4. 从主库做基础备份（一键配置流复制）
    - name: 执行基础备份，配置流复制
      become: yes
      become_user: postgres
      command: >
        pg_basebackup -h {{ pg_master_ip }} -p 5432 -U {{ pg_repl_user }} 
        -D {{ pg_data_dir }} -F p -X stream -P -R
      environment:
        PGPASSWORD: "{{ pg_repl_pass }}"
 
    # 5. 复制从库配置文件
    - name: 复制postgresql.conf
      copy:
        src: "/etc/ansible/pg_conf/slave/postgresql.conf"
        dest: "{{ pg_data_dir }}/postgresql.conf"
        owner: postgres
        group: postgres
        mode: 0600
 
    # 6. 启动从库
    - name: 启动PostgreSQL服务
      service:
        name: postgresql-{{ pg_version }}
        state: restarted
        enabled: yes
 
    # 7. 验证流复制状态
    - name: 验证从库同步状态
      become: yes
      become_user: postgres
      command: psql -c "SELECT state FROM pg_stat_replication;"
      register: repl_state
      failed_when: "'streaming' not in repl_state.stdout"
 
  handlers:
    - name: recreate data dir
      file:
        path: "{{ pg_data_dir }}"
        state: directory
        owner: postgres
        group: postgres
        mode: 0700

4. 执行 Playbook，一键部署

bash

# 1. 提前准备主从配置文件（控制节点）
mkdir -p /etc/ansible/pg_conf/master /etc/ansible/pg_conf/slave
# 主库postgresql.conf关键配置：wal_level=replica、archive_mode=on、max_wal_senders=5
# 从库postgresql.conf关键配置：hot_standby=on、max_standby_streaming_delay=30s
 
# 2. 执行主库部署
ansible-playbook /etc/ansible/pg_master_deploy.yml
 
# 3. 执行从库部署
ansible-playbook /etc/ansible/pg_slave_deploy.yml

成功标志：

从库执行 su - postgres && psql -c "SELECT state, sync_state FROM pg_stat_replication;"，显示 state=streaming、 sync_state=sync，说明主从同步正常。

四、实战 2：Shell+Crond 自动化备份（含 WAL 归档 + 验证）

PG 的备份自动化要兼顾 “基础备份 + WAL 归档”，比 MySQL 多了一层 WAL 日志的同步验证，用 Shell 脚本 + 定时任务，实现 “自动备份 + 异地同步 + 数据校验 + 过期清理 + 告警”。

自动化备份脚本（pg_backup_auto.sh）

bash

#!/bin/bash
# 功能：PG自动基础备份+WAL归档验证+异地同步+清理+告警
# 配置参数
PG_VERSION="16"
PG_USER="postgres"
PG_PORT="5432"
PG_DB="seckill_db"
BACKUP_DIR="/data/pg_backup"
ARCHIVE_DIR="/data/pg_archive"
REMOTE_HOST="192.168.1.200"
REMOTE_DIR="/data/pg_backup_remote"
ALERT_EMAIL="admin@xxx.com"
LOG_FILE="/var/log/pg_backup.log"
RETENTION_DAYS=7  # 备份保留7天
 
# 日志函数
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
}
 
log "开始执行PG自动备份任务..."
 
# 1. 创建备份目录
BACKUP_FULL_DIR="$BACKUP_DIR/full_$(date +%Y%m%d)"
mkdir -p $BACKUP_FULL_DIR
if [ $? -ne 0 ]; then
    log "创建备份目录失败！"
    echo "PG备份失败：创建目录失败" | mail -s "PG备份告警" $ALERT_EMAIL
    exit 1
fi
 
# 2. 执行基础备份（热备份，不锁表）
su - $PG_USER -c "pg_basebackup -D $BACKUP_FULL_DIR -h localhost -p $PG_PORT -U $PG_USER -F p -X stream -P -R"
if [ $? -ne 0 ]; then
    log "基础备份执行失败！"
    echo "PG备份失败：基础备份失败" | mail -s "PG备份告警" $ALERT_EMAIL
    exit 1
fi
log "基础备份成功：$BACKUP_FULL_DIR"
 
# 3. 验证WAL归档是否正常
ARCHIVE_CHECK=$(su - $PG_USER -c "psql -c 'SELECT last_archived_time FROM pg_stat_archiver;' | grep -v last_archived_time | awk '{print $1}'")
if [ -z "$ARCHIVE_CHECK" ]; then
    log "WAL归档异常！"
    echo "PG备份告警：WAL归档未正常执行" | mail -s "PG备份告警" $ALERT_EMAIL
    exit 1
fi
log "WAL归档验证正常，最后归档时间：$ARCHIVE_CHECK"
 
# 4. 同步备份到异地服务器
scp -r $BACKUP_FULL_DIR $REMOTE_HOST:$REMOTE_DIR > /dev/null 2>&1
if [ $? -ne 0 ]; then
    log "异地同步备份失败！"
    echo "PG备份失败：异地同步失败" | mail -s "PG备份告警" $ALERT_EMAIL
    exit 1
fi
log "异地同步成功：$REMOTE_HOST:$REMOTE_DIR/$(date +%Y%m%d)"
 
# 5. 数据完整性校验（对比主库和备份的核心表数据量）
MASTER_CNT=$(su - $PG_USER -c "psql -d $PG_DB -c 'SELECT COUNT(*) FROM order_info;' -t")
# 临时恢复备份验证
TEST_RESTORE_DIR="$BACKUP_DIR/test_restore_$(date +%Y%m%d)"
mkdir -p $TEST_RESTORE_DIR
cp -r $BACKUP_FULL_DIR/* $TEST_RESTORE_DIR
chown -R $PG_USER:$PG_USER $TEST_RESTORE_DIR
TEST_CNT=$(su - $PG_USER -c "psql -d $PG_DB -c 'SELECT COUNT(*) FROM order_info;' -t -D $TEST_RESTORE_DIR 2>/dev/null")
 
if [ "$MASTER_CNT" -ne "$TEST_CNT" ]; then
    log "备份数据校验失败！主库：$MASTER_CNT，备份：$TEST_CNT"
    echo "PG备份告警：数据校验失败" | mail -s "PG备份告警" $ALERT_EMAIL
    exit 1
fi
log "备份数据校验成功，数据量一致"
 
# 6. 清理过期备份
find $BACKUP_DIR -name "full_*" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} ;
ssh $REMOTE_HOST "find $REMOTE_DIR -name 'full_*' -type d -mtime +$RETENTION_DAYS -exec rm -rf {} ;"
log "清理过期备份完成"
 
log "PG自动备份任务全部完成！"
exit 0

配置定时任务（每天凌晨 2 点执行）

bash

# 给脚本加执行权限
chmod +x /usr/local/bin/pg_backup_auto.sh
 
# 编辑crontab
crontab -e
 
# 加入以下内容
0 2 * * * /usr/local/bin/pg_backup_auto.sh >> /var/log/pg_backup_cron.log 2>&1

五、实战 3：版本升级回滚自动化（PG 16.3→16.4，零业务中断）

手动升级 PG 容易忘做 WAL 日志备份，中途报错就傻眼。自动化升级要包含 “前置检查 + 备份 + 升级 + 回滚”，确保万无一失。

升级脚本（pg_upgrade_auto.sh）

bash

#!/bin/bash
# 功能：PG 16.3→16.4 自动化升级+回滚
OLD_VERSION="16.3"
NEW_VERSION="16.4"
PG_HOME="/usr/local/pgsql"
PG_DATA_DIR="/var/lib/pgsql/16/data"
BACKUP_DIR="/data/pg_upgrade_backup/$(date +%Y%m%d)"
LOG_FILE="/var/log/pg_upgrade.log"
ALERT_EMAIL="admin@xxx.com"
 
# 日志函数
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
}
 
log "开始PG升级：$OLD_VERSION→$NEW_VERSION"
 
# 1. 前置检查
if ! systemctl is-active --quiet postgresql-16; then
    log "PG服务未运行，终止升级！"
    echo "PG升级失败：服务未运行" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
CURRENT_VERSION=$(su - postgres -c "psql -c 'SELECT version();' | grep -oE '16.[0-9]+'")
if [ "$CURRENT_VERSION" != "$OLD_VERSION" ]; then
    log "当前版本$CURRENT_VERSION不是目标旧版本$OLD_VERSION，终止升级！"
    echo "PG升级失败：版本不匹配" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
# 2. 升级前备份（基础备份+WAL归档）
mkdir -p $BACKUP_DIR
su - postgres -c "pg_basebackup -D $BACKUP_DIR/full -h localhost -p 5432 -U postgres -F p -X stream -P"
if [ $? -ne 0 ]; then
    log "升级前备份失败，终止升级！"
    echo "PG升级失败：备份失败" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
log "升级前备份成功：$BACKUP_DIR/full"
 
# 3. 停止PG服务
systemctl stop postgresql-16
if [ $? -ne 0 ]; then
    log "停止PG失败，终止升级！"
    echo "PG升级失败：停止服务失败" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
# 4. 备份旧版本目录
mv $PG_HOME $PG_HOME-$OLD_VERSION
if [ $? -ne 0 ]; then
    log "备份旧版本目录失败，启动回滚！"
    systemctl start postgresql-16
    echo "PG升级回滚：备份旧目录失败" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
# 5. 安装新版本（假设已下载rpm包到/root目录）
rpm -ivh /root/postgresql16-server-$NEW_VERSION-1.el7.x86_64.rpm --force --nodeps > /dev/null 2>&1
if [ $? -ne 0 ]; then
    log "安装新版本失败，启动回滚！"
    mv $PG_HOME-$OLD_VERSION $PG_HOME
    systemctl start postgresql-16
    echo "PG升级回滚：安装失败" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
# 6. 执行升级检查（PG原生工具，检查兼容性）
su - postgres -c "pg_upgrade -b $PG_HOME-$OLD_VERSION/bin -B $PG_HOME/bin -d $PG_DATA_DIR -D $PG_DATA_DIR --check"
if [ $? -ne 0 ]; then
    log "升级兼容性检查失败，启动回滚！"
    systemctl stop postgresql-16
    mv $PG_HOME-$OLD_VERSION $PG_HOME
    systemctl start postgresql-16
    echo "PG升级回滚：兼容性检查失败" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
# 7. 执行升级
su - postgres -c "pg_upgrade -b $PG_HOME-$OLD_VERSION/bin -B $PG_HOME/bin -d $PG_DATA_DIR -D $PG_DATA_DIR"
if [ $? -ne 0 ]; then
    log "升级执行失败，启动回滚！"
    systemctl stop postgresql-16
    mv $PG_HOME-$OLD_VERSION $PG_HOME
    systemctl start postgresql-16
    echo "PG升级回滚：执行失败" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
# 8. 启动新版本并验证
systemctl start postgresql-16
if ! systemctl is-active --quiet postgresql-16; then
    log "启动新版本失败，启动回滚！"
    mv $PG_HOME-$OLD_VERSION $PG_HOME
    systemctl start postgresql-16
    echo "PG升级回滚：启动失败" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
NEW_CURRENT_VERSION=$(su - postgres -c "psql -c 'SELECT version();' | grep -oE '16.[0-9]+'")
if [ "$NEW_CURRENT_VERSION" != "$NEW_VERSION" ]; then
    log "版本验证失败，启动回滚！"
    systemctl stop postgresql-16
    mv $PG_HOME-$OLD_VERSION $PG_HOME
    systemctl start postgresql-16
    echo "PG升级回滚：版本验证失败" | mail -s "PG升级告警" $ALERT_EMAIL
    exit 1
fi
 
log "PG升级成功：$OLD_VERSION→$NEW_VERSION"
echo "PG升级成功！当前版本：$NEW_VERSION" | mail -s "PG升级成功" $ALERT_EMAIL
exit 0

六、避坑指南：MySQL 老玩家最容易踩的 4 个坑

坑 1：混淆 PG 和 MySQL 的配置文件路径错误：把 MySQL 的 /etc/my.cnf思路套 PG，找不到配置文件；正确：PG 默认配置路径 /var/lib/pgsql/16/data/postgresql.conf，Ansible 复制时要写对路径。

坑 2：备份时忽略 WAL 归档验证错误：只备份基础数据，没检查 WAL 归档是否正常，恢复时缺日志；正确：脚本中加入 pg_stat_archiver查询，验证 WAL 归档状态。

坑 3：升级时没备份 WAL 日志错误：直接停止 PG 升级，没备份当前 WAL 日志，回滚后数据不一致；正确：升级前执行 pg_basebackup -X stream，确保备份包含所有 WAL 日志。

坑 4：Ansible 执行时权限不足错误：用普通用户执行 Playbook，没权限修改 PG 数据目录；正确：Playbook 中用 become: yes和 become_user: postgres切换用户，确保权限足够。

七、PG 运维自动化的 “核心价值” 

PG 运维自动化的核心不是 “偷懒”，而是 “减少人为错误、解放重复劳动”—— 手动运维 10 次可能对 9 次，但 1 次错就可能导致业务停摆；自动化能把重复操作的错误率降到 0，还能让运维聚焦更重要的架构优化。

记住：