如何实现Linux服务器间PostgreSQL数据库每日自动同步备份

核心思路 #

借助PostgreSQL自带的pg_dump工具导出源数据库，通过scp传输到备份服务器，再用系统定时任务crontab实现每日自动执行。

具体实现步骤 #

1. 配置源与备份服务器的免密登录 #

避免脚本执行时需要手动输入密码，先在源服务器完成密钥配置：

# 源服务器生成RSA密钥对（一路回车使用默认配置即可）
ssh-keygen -t rsa

# 将公钥复制到备份服务器（替换backup_user和backup_server_ip为实际信息）
ssh-copy-id backup_user@backup_server_ip

2. 编写备份同步脚本 #

在源服务器创建脚本文件，比如/opt/pg_backup_sync.sh，内容如下：

#!/bin/bash

# 配置参数（根据实际环境修改）
SOURCE_DB_NAME="your_source_db"
SOURCE_DB_USER="postgres"
LOCAL_BACKUP_DIR="/tmp/pg_backups"
BACKUP_SERVER_USER="backup_user"
BACKUP_SERVER_IP="x.x.x.x"
BACKUP_SERVER_DIR="/data/pg_backups"
DATE_TAG=$(date +%Y%m%d_%H%M%S)

# 创建本地临时备份目录
mkdir -p $LOCAL_BACKUP_DIR

# 导出数据库（需压缩可添加-z参数，如pg_dump -z -U ...）
pg_dump -U $SOURCE_DB_USER $SOURCE_DB_NAME > $LOCAL_BACKUP_DIR/${SOURCE_DB_NAME}_${DATE_TAG}.sql

# 传输备份文件到备份服务器
scp $LOCAL_BACKUP_DIR/${SOURCE_DB_NAME}_${DATE_TAG}.sql $BACKUP_SERVER_USER@$BACKUP_SERVER_IP:$BACKUP_SERVER_DIR

# 清理本地7天前的旧备份（可选，按需调整保留天数）
find $LOCAL_BACKUP_DIR -name "*.sql" -mtime +7 -delete

给脚本添加执行权限：

chmod +x /opt/pg_backup_sync.sh

3. 配置定时任务自动执行 #

编辑源服务器的crontab：

crontab -e

添加一行配置（示例为每天凌晨2点执行，可按需调整时间）：

0 2 * * * /opt/pg_backup_sync.sh >> /var/log/pg_backup_sync.log 2>&1

保存退出后，crontab会自动生效，日志文件会记录执行细节，方便排查问题。

4. 验证备份有效性 #

手动执行脚本测试流程是否正常：

/opt/pg_backup_sync.sh

之后登录备份服务器，检查目标目录下是否生成对应日期的备份文件；也可定期用psql导入备份文件验证完整性。

注意事项 #

• 确保源服务器的postgres用户拥有pg_dump执行权限，且能免密登录备份服务器
• 备份服务器的目标目录需提前创建，并给指定用户分配写入权限
• 大数据库建议启用压缩导出，减少传输带宽和存储占用
• 可根据业务需求调整备份保留天数，修改find命令中的-mtime +7参数

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