非生产环境PostgreSQL主库WAL归档清理方法及bash脚本问询

核心思路 #

由于你的主库是非生产环境且无备份，清理WAL归档的核心原则是：确保所有备库已接收并处理完对应WAL段后，再删除主库归档目录中的旧文件，避免误删备库尚未同步的WAL导致备库无法正常同步。

可执行的Bash脚本（适配Cron任务） #

以下脚本会自动获取所有备库已接收的最新WAL LSN，找出所有备库中最滞后的LSN（确保所有备库都已同步到该点），然后删除主库归档目录中早于该LSN对应WAL文件的所有归档：

#!/bin/bash

# -------------------------- 配置区域 --------------------------
# 主库WAL归档目录路径
MAIN_ARCHIVE_DIR="/var/lib/postgresql/14/main/pg_wal_archive"
# 备库连接信息，格式："host:port:数据库用户"，多个备库用空格分隔
STANDBY_NODES=("standby1:5432:postgres" "standby2:5432:postgres")
# （推荐）使用~/.pgpass存储密码，避免明文暴露，格式：*:5432:postgres:your_password，权限设为600
# 如果必须明文，取消下面注释并填写密码
# PGPASSWORD="your_db_password"
# -------------------------------------------------------------

# 初始化最小LSN为最大值（确保第一个备库的LSN能替换它）
MIN_LSN="FFFFFFFF/FFFFFFFF"

# 遍历所有备库，获取已接收的WAL LSN，找出最小的LSN（最滞后的备库）
for node in "${STANDBY_NODES[@]}"
do
    IFS=':' read -r HOST PORT USER <<< "$node"
    # 从备库获取最后接收的WAL LSN
    RECEIVED_LSN=$(psql -h "$HOST" -p "$PORT" -U "$USER" -t -c "SELECT pg_last_wal_receive_lsn();" | tr -d '[:space:]')
    
    # 检查LSN获取是否成功
    if [ -z "$RECEIVED_LSN" ] || [ "$RECEIVED_LSN" = "NULL" ]; then
        echo "[$(date '+%Y-%m-%d %H:%M:%S')] 错误：无法连接备库 $HOST:$PORT 或获取LSN，终止清理"
        exit 1
    fi
    
    # 更新最小LSN（确保所有备库都已同步到该点）
    if [[ "$RECEIVED_LSN" < "$MIN_LSN" ]]; then
        MIN_LSN="$RECEIVED_LSN"
    fi
done

# 将最小LSN转换为对应的WAL文件名
TARGET_WAL_FILE=$(psql -h localhost -p 5432 -U postgres -t -c "SELECT pg_walfile_name('$MIN_LSN');" | tr -d '[:space:]')

if [ -z "$TARGET_WAL_FILE" ]; then
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 错误：无法将LSN $MIN_LSN 转换为WAL文件名，终止清理"
    exit 1
fi

echo "[$(date '+%Y-%m-%d %H:%M:%S')] 所有备库已同步的最小LSN：$MIN_LSN，对应WAL文件：$TARGET_WAL_FILE"

# 进入归档目录并清理旧文件
cd "$MAIN_ARCHIVE_DIR" || {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 错误：无法进入主库归档目录 $MAIN_ARCHIVE_DIR"
    exit 1
}

# 遍历并删除早于目标WAL文件的归档
for wal_file in *
do
    if [[ -f "$wal_file" && "$wal_file" < "$TARGET_WAL_FILE" ]]; then
        echo "[$(date '+%Y-%m-%d %H:%M:%S')] 删除归档文件：$wal_file"
        rm -f "$wal_file"
        # 测试阶段替换为echo，确认要删除的文件：echo "[$(date '+%Y-%m-%d %H:%M:%S')] 待删除归档文件：$wal_file"
    fi
done

echo "[$(date '+%Y-%m-%d %H:%M:%S')] 主库WAL归档清理完成"

脚本使用步骤 #

1. 配置调整：修改脚本中的归档目录、备库连接信息，推荐使用.pgpass文件存储数据库密码（安全且避免明文）。
2. 赋予执行权限：

   chmod +x /path/to/cleanup_main_wal_archive.sh
   

3. 测试验证：将脚本中的rm -f "$wal_file"替换为echo "待删除：$wal_file"，运行脚本确认要删除的文件范围是否正确。
4. 配置Cron任务：
   编辑当前用户的Cron表：

   crontab -e
   

   添加定时任务（例如每天凌晨2点执行，输出日志到指定文件）：

   0 2 * * * /path/to/cleanup_main_wal_archive.sh >> /var/log/pg_wal_cleanup.log 2>&1
   

备选方案（风险较高） #

如果你的非生产环境可以接受备库随时重建（无需保留WAL用于恢复），也可以直接按文件创建时间清理，例如删除7天前的归档文件：

find /var/lib/postgresql/14/main/pg_wal_archive -type f -mtime +7 -delete

注意：此方式可能导致备库因缺失未同步的WAL而中断，仅在备库可快速重建的场景下使用。

内容的提问来源于stack exchange，提问作者nick