PostgreSQL 备份 + 同步远程 + 完整还原操作手册

#=============================环境说明=============================
# 本地数据库服务器：test-zhou
# 远程备份存储服务器：192.168.152.56 登录账号root
# PostgreSQL端口：5432 本地连接地址：127.0.0.1
# 本地备份存放目录：/data/pg_backup_file
# 远程备份存放根目录：/pg_backup_storage
# 脚本存放目录：/opt/pgbackup
#==================================================================

#====================第一部分：一次性前置准备（仅执行1次）====================
## 1. 本地配置SSH免密登录远程存储服务器
# 1.1 生成ssh密钥，全程回车不输入任何内容

ssh-keygen -t ed25519

 

# 1.2 推送公钥至远程root用户

ssh-copy-id root@192.168.152.56

# 1.3 验证免密，无需输入密码即代表成功

ssh-copy-id root@192.168.152.56

## 2. 本地PostgreSQL免密配置文件（避免备份输入密码）
# 切换postgres系统用户
su - postgres
# 创建pgpass免密文件
vim ~/.pgpass
# 文件内写入内容，替换【你的PG数据库密码】
127.0.0.1:5432:*:postgres:你的PG数据库密码
# 设置强制权限，PG校验权限不通过会失效
chmod 600 ~/.pgpass
# 切回root用户
exit

## 3. 创建本地脚本目录、本地备份存储目录

mkdir -p /opt/pgbackup
mkdir -p /data/pg_backup_file
chmod 755 /opt/pgbackup
chmod 755 /data/pg_backup_file

　　

#====================第二部分：创建多库自动备份同步脚本====================
## 1. 进入脚本目录，新建备份脚本

cd /opt/pgbackup
vim pg_back_multi.sh

　　

## 2. 脚本完整内容，粘贴到文件内
#!/bin/bash
#=====================【配置区 按需修改】=====================

# 需要备份的数据库列表，空格分隔多个库名
DB_LIST=("maxeyeqmes" "maxeyeqmes-1")
# PG本地连接参数
PG_USER="postgres"
PG_PORT="5432"
PG_HOST="127.0.0.1"
LOCAL_BACKUP_DIR="/data/pg_backup_file"
RETENTION_DAY=7 # 本地备份保留天数
# 远程备份服务器参数
REMOTE_SSH_USER="root"
REMOTE_SSH_IP="192.168.152.56"
REMOTE_ROOT_DIR="/pg_backup_storage"
REMOTE_RETENTION_DAY=15 # 远程备份保留天数
#=============================================================
# 关闭终端彩色输出，防止手动执行刷屏乱码
export TERM=dumb
export RSYNC_NO_COLOR=1

DATE=$(date +%Y%m%d_%H%M%S)
LOG_MAIN=${LOCAL_BACKUP_DIR}/backup_all_log.log
mkdir -p ${LOCAL_BACKUP_DIR}

echo "==================== ${DATE} 多库备份任务开始 ====================" >> ${LOG_MAIN}
# 循环逐个备份数据库
for PG_DB in "${DB_LIST[@]}"; do
echo "===== 开始备份数据库：${PG_DB} =====" >> ${LOG_MAIN}
DUMP_FILE=${LOCAL_BACKUP_DIR}/${PG_DB}_${DATE}.dump
REMOTE_DB_DIR=${REMOTE_ROOT_DIR}/${PG_DB}_backup
# 执行数据库备份，自动过滤pg_cron定时模式，保留外部表
pg_dump -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} -d ${PG_DB} -F c \
--exclude-schema=cron \
-f ${DUMP_FILE}
if [ $? -eq 0 ];then
echo "✅ ${PG_DB} 本地备份成功 文件：${PG_DB}_${DATE}.dump" >> ${LOG_MAIN}
# 远程自动创建当前库专属备份目录
ssh -o LogLevel=error ${REMOTE_SSH_USER}@${REMOTE_SSH_IP} "mkdir -p ${REMOTE_DB_DIR} && chmod 700 ${REMOTE_DB_DIR}"
# 同步备份文件至远程对应文件夹
rsync -az --no-progress ${DUMP_FILE} ${REMOTE_SSH_USER}@${REMOTE_SSH_IP}:${REMOTE_DB_DIR}/ 2>>${LOG_MAIN}
if [ $? -eq 0 ];then
echo "✅ ${PG_DB} 远程同步完成" >> ${LOG_MAIN}
# 远程清理过期备份
ssh -o LogLevel=error ${REMOTE_SSH_USER}@${REMOTE_SSH_IP} "find ${REMOTE_DB_DIR} -name '*.dump' -type f -mtime +${REMOTE_RETENTION_DAY} -delete"
echo "✅ ${PG_DB} 远程旧备份清理完成" >> ${LOG_MAIN}
else
echo "❌ ${PG_DB} 远程同步失败，检查网络或SSH免密" >> ${LOG_MAIN}
fi
else
echo "❌ ${PG_DB} 本地备份失败，跳过远程同步" >> ${LOG_MAIN}
fi
done
# 统一清理本地全部过期备份文件
find ${LOCAL_BACKUP_DIR} -name "*.dump" -type f -mtime +${RETENTION_DAY} -delete
echo "✅ 本地所有过期备份清理完成" >> ${LOG_MAIN}
echo "=============================================================\n" >> ${LOG_MAIN}

## 3. 保存脚本：按Esc，输入:wq回车退出vim
## 4. 赋予脚本执行权限
chmod +x pg_back_multi.sh

　　

#====================第三部分：手动测试全库备份+远程同步====================

## 1. 执行备份脚本
/opt/pgbackup/pg_back_multi.sh
## 2. 校验本地备份文件
ls -lh /data/pg_backup_file
## 3. 查看完整备份日志
cat /data/pg_backup_file/backup_all_log.log
## 4. 校验远程同步文件
ssh root@192.168.152.56 "ls -lh /pg_backup_storage/maxeyeqmes_backup"

　　

#====================第四部分：配置定时自动备份（每日凌晨2点）====================

## 1. 编辑定时任务列表
crontab -e
## 2. 写入定时规则
0 2 * * * /opt/pgbackup/pg_back_multi.sh > /dev/null 2>&1
## 3. 查看已配置定时任务确认生效
crontab -l
## 4. Ubuntu查看定时运行日志
tail -f /var/log/syslog | grep CRON
## 5. CentOS查看定时运行日志
tail -f /var/log/cron

　　

#====================第五部分：本地dump文件还原数据库完整步骤====================

# 重要提醒：pg_restore 必须在root系统shell执行，禁止在postgres=#交互终端运行
# 示例备份文件路径：/data/pg_backup_file/maxeyeqmes_20260625_075519.dump
# 示例目标新库名：maxeyeqmes_back_20260625_1

## 步骤1：登录PG，创建空目标数据库
psql -h 127.0.0.1 -p 5432 -U postgres
# 下方SQL末尾必须带分号
CREATE DATABASE maxeyeqmes_back_20260625_1 ENCODING 'UTF8';
# 退出psql回到root命令行
\q

## 步骤2：root命令行执行还原命令
pg_restore -h 127.0.0.1 -p 5432 -U postgres \
-d maxeyeqmes_back_20260625_1 \
--exclude-schema=cron \
--no-owner \
/data/pg_backup_file/maxeyeqmes_20260625_075519.dump

## 步骤3：校验还原数据
psql -h 127.0.0.1 -p 5432 -U postgres -d maxeyeqmes_back_20260625_1
# 查看普通业务表
\dt
# 查看外部表
\det

　　

#====================第六部分：远程备份文件直拉还原（无需下载本地）====================
## 步骤1：先按第五部分步骤1创建目标数据库
## 步骤2：root执行远程管道还原命令（替换真实dump文件名）

ssh root@192.168.152.56 "cat /pg_backup_storage/qmes_backup/qmes_20260625_075519.dump" | pg_restore -h 127.0.0.1 -p 5432 -U postgres -d qmes_back_20260625_1 --exclude-schema=cron --no-owner

　　

#====================第七部分：常用排查命令====================

## 1. 查看远程库下所有备份文件
ssh root@192.168.152.56 "ls -lh /pg_backup_storage/maxeyeqmes_backup/"
## 2. 校验本地dump文件是否存在
ls -lh /data/pg_backup_file/
## 3. 查看备份内所有表/对象清单
pg_restore -l /data/pg_backup_file/maxeyeqmes_20260625_075519.dump
## 4. 新增/删减备份数据库：修改脚本内DB_LIST=("库名1" "库名2")，无需改动定时任务