📖 基础备份制作命令

# 切换到postgres用户
su - postgres

# 创建基础备份目录
mkdir -p /data/backups/base/

# 制作基础备份（推荐参数）
pg_basebackup \
  -D /data/backups/base/backup_$(date +%Y%m%d_%H%M%S) \
  -Ft -z -P -v \
  -X stream \
  -c fast

参数说明：

• -D：备份输出目录
• -Ft：输出为tar格式
• -z：使用gzip压缩备份文件
• -P：显示备份进度
• -v：详细输出
• -X stream：流式传输备份过程中产生的WAL文件，确保备份一致性
• -c fast：快速执行检查点，减少备份时间

🛠️ 自动化备份脚本

#!/bin/bash
# ========== 配置变量 ==========
BACKUP_BASE_DIR="/data/postgresql/wal-basic-backup"
RETENTION_DAYS=7
LOG_FILE="/var/log/pg_base_backup.log"

# ========== 准备工作 ==========
mkdir -p "${BACKUP_BASE_DIR}" "$(dirname "${LOG_FILE}")"
if [ $? -ne 0 ]; then
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 无法创建备份或日志目录，退出" | tee -a "${LOG_FILE}" >&2
    exit 1
fi

# 生成带时间戳的备份目录名
BACKUP_NAME="backup_$(date +%Y%m%d_%H%M%S)"
BACKUP_DIR="${BACKUP_BASE_DIR}/${BACKUP_NAME}"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" >> "${LOG_FILE}"
}

# ========== 执行备份 ==========
log "开始制作基础备份: ${BACKUP_NAME}"

pg_basebackup \
  -D "${BACKUP_DIR}" \
  -Ft -z -P -v \
  -X stream \
  -c fast >> "${LOG_FILE}" 2>&1

if [ $? -eq 0 ]; then
    log "基础备份成功完成"

    # ========== 历史备份清理（基于目录名日期，比 find -mtime 更可靠） ==========
    log "开始清理 ${RETENTION_DAYS} 天前的旧备份（基于目录名日期）"
    cd "${BACKUP_BASE_DIR}" || exit 1

    # 计算截止日期（格式与备份目录一致：YYYYMMDD）
    cutoff_date=$(date -d "-${RETENTION_DAYS} days" +%Y%m%d)
    log "截止日期：${cutoff_date}"

    for dir in backup_*; do
        [ -d "$dir" ] || continue

        # 提取目录名中的日期部分（YYYYMMDD）
        # 目录名格式：backup_YYYYMMDD_HHMMSS
        dir_date=${dir#backup_}          # 去掉前缀
        dir_date=${dir_date%%_*}         # 只取第一个_前的部分，即YYYYMMDD

        # 格式校验：必须为8位数字
        if [[ ! ${dir_date} =~ ^[0-9]{8}$ ]]; then
            log "警告：目录名格式异常，跳过 ${dir}"
            continue
        fi

        # 字符串直接比较（YYYYMMDD 可直接比大小）
        if [[ "${dir_date}" < "${cutoff_date}" ]]; then
            log "删除过期备份目录：${dir}"
            rm -rf "${dir}"
        fi
    done
else
    log "基础备份失败！"
    exit 1
fi

log "备份任务结束"
echo "----------------------------------------" >> "${LOG_FILE}"

2.开启WAL归档并配置自动压缩

1）备份原始配置文件

# 切换到PostgreSQL配置目录（默认路径）
cd /var/lib/pgsql/14/data/

# 备份原始配置
cp postgresql.conf postgresql.conf.bak.$(date +%Y%m%d)
cp pg_hba.conf pg_hba.conf.bak.$(date +%Y%m%d)

2）修改postgresql.conf核心参数

编辑/var/lib/pgsql/14/data/postgresql.conf，找到并修改以下参数：

# 开启WAL归档（生产环境必须设为replica或更高）
wal_level = replica

# 开启归档模式
archive_mode = on

# 归档命令（自动压缩，使用gzip，保留原始文件名）
archive_command = 'gzip -c %p > /data/waldata/%f.gz && test -f /data/waldata/%f.gz'

# 归档超时（防止WAL文件长时间不切换，生产建议10-30分钟）
archive_timeout = 10min

# 以下为生产环境优化参数（可选但推荐）
max_wal_size = 16GB  # 根据磁盘空间调整，建议为磁盘的10%-20%
min_wal_size = 4GB
wal_keep_size = 4GB  # 保留的WAL文件大小，用于流复制和本地恢复
wal_compression = on  # 开启WAL页级压缩，减少磁盘IO

参数说明：

• %p：PostgreSQL替换为WAL文件的完整路径
• %f：PostgreSQL替换为WAL文件的文件名
• test -f：确保压缩文件成功创建后才返回成功，否则PostgreSQL会重试归档

3）重启PostgreSQL服务使配置生效

systemctl restart postgresql-14

# 验证服务状态
systemctl status postgresql-14

4）验证归档是否正常工作

# 切换到postgres用户
su - postgres

# 手动切换WAL文件，触发归档
psql -c "SELECT pg_switch_wal();"

# 检查归档目录是否有压缩后的WAL文件
ls -l /data/waldata/

如果看到类似000000010000000000000001.gz的文件，说明归档配置成功。

🛠️ 自动化备份脚本

#!/bin/bash
# ========== 配置变量 ==========
ARCHIVE_DIR="/data/postgresql/wal-archive-path"
RETENTION_DAYS=7
LOG_FILE="/var/log/pg_wal_clean.log"

# ========== 日志函数 ==========
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" >> "$LOG_FILE"
}

# ========== 前置检查 ==========
if [ ! -d "$ARCHIVE_DIR" ]; then
    log "错误：归档目录 ${ARCHIVE_DIR} 不存在，退出。"
    exit 1
fi

# 确保日志目录存在
mkdir -p "$(dirname "$LOG_FILE")"

# ========== 清理旧 WAL ==========
log "开始清理 ${RETENTION_DAYS} 天前的WAL归档文件"

# 查找并删除超过 N 天的 .gz 归档文件
deleted_count=$(find "$ARCHIVE_DIR" -type f -name "*.gz" -mtime +${RETENTION_DAYS} -print -delete | wc -l)

log "清理完成，共删除 ${deleted_count} 个过期WAL文件"

echo "----------------------------------------" >> "$LOG_FILE"

📄 WAL恢复

恢复前准备

1. 确保PostgreSQL服务已停止
2. 备份当前损坏的数据目录
3. 准备好最新的基础备份和对应的WAL归档文件

1.停止PostgreSQL服务

systemctl stop postgresql-14

2.备份当前数据目录（防止恢复失败）

# 重命名当前数据目录
mv /var/lib/pgsql/14/data /var/lib/pgsql/14/data.corrupted.$(date +%Y%m%d)

3.解压基础备份

# 创建新的数据目录
mkdir -p /var/lib/pgsql/14/data

# 找到最新的基础备份目录
LATEST_BACKUP=$(ls -td /data/backups/base/backup_* | head -1)
echo "使用最新备份: $LATEST_BACKUP"

# 解压备份文件到新的数据目录
cd $LATEST_BACKUP
for file in *.tar.gz; do
  tar -xzf $file -C /var/lib/pgsql/14/data/
done

# 设置正确的权限
chown -R postgres:postgres /var/lib/pgsql/14/data
chmod 700 /var/lib/pgsql/14/data

4.创建恢复配置文件

在新的数据目录下创建recovery.signal文件（PostgreSQL 12+可以使用此文件触发恢复）：

touch /var/lib/pgsql/14/data/recovery.signal
chown postgres:postgres /var/lib/pgsql/14/data/recovery.signal

编辑/var/lib/pgsql/14/data/postgresql.conf，添加恢复相关参数：

# 恢复命令（解压归档WAL文件供PostgreSQL使用）
restore_command = 'gunzip -c /data/waldata/%f.gz > %p'

# 恢复到最新时间点（默认）
# recovery_target = 'latest'

# 可选：恢复到指定时间点
# recovery_target_time = '2024-05-20 10:30:00'

# 可选：恢复到指定事务ID
# recovery_target_xid = '12345'

# 恢复完成后自动提升为主库
recovery_target_action = 'promote'

5.启动PostgreSQL服务开始恢复

systemctl start postgresql-14

6.监控恢复过程

# 查看PostgreSQL日志
tail -f /var/lib/pgsql/14/data/log/postgresql-*.log

恢复过程中会看到类似以下日志：

LOG:  starting archive recovery
LOG:  restore_command = 'gunzip -c /data/waldata/%f.gz > %p'
LOG:  restored log file "000000010000000000000002.gz" from archive
LOG:  restored log file "000000010000000000000003.gz" from archive
...
LOG:  redo done at 0/40000D8
LOG:  last completed transaction was at log time 2024-05-20 14:25:30.123456+08
LOG:  restored log file "000000010000000000000004.gz" from archive
LOG:  selected new timeline ID: 2
LOG:  archive recovery complete
LOG:  database system is ready to accept connections

7.验证恢复结果

docker exec -it postgres psql -U postgres

# 验证
\l                                       -- 列出所有数据库
\c 业务库名                                -- 切到业务库
\dt                                      -- 查表
SELECT count(*) FROM 某张表;               -- 抽查数据量
SELECT now();              -- 查看当前数据库时间
SELECT pg_is_in_recovery();              -- - 返回 true（恢复模式）
SELECT pg_last_xact_replay_timestamp();  -- 显示恢复时间

# 提升为主库
docker exec -it postgres psql -U postgres -c "SELECT pg_wal_replay_resume();"

# 应返回 f（不再处于恢复模式）
docker exec -it postgres psql -U postgres -c "SELECT pg_is_in_recovery();"

Tips

1. 权限要求：
   • pg_dump 需要对目标数据库有读取权限。
   • pg_dumpall 需要超级用户权限（以备份角色和表空间）。
2. 备份格式选择：
   • 如果需要灵活的恢复选项（如选择性恢复表），建议使用自定义格式（-F c）。
   • 如果需要快速恢复，SQL 文件可能更直接。
3. 并行备份：
   • 对大型数据库，使用 -j <并行任务数> 可加速备份（需 PostgreSQL 12+）。

删除数据库报错 ERROR: database “xxx_prod” is being accessed by other usersDETAlL: There are 33 other sessions using the database.

踢掉所有连接的服务，然后在删除数据库

-- 步骤 1: 阻止新连接到目标数据库
ALTER DATABASE "xxx_prod" ALLOW_CONNECTIONS false;

-- 步骤 2: 终止所有现有连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'xxx_prod';

-- 步骤 3: 删除数据库
DROP DATABASE "xxx_prod";