Ubuntu下能否直接将本地所有PostgreSQL数据库导出至远程并设置cron定时任务？

这个方案完全可行，通过PostgreSQL原生工具、SSH传输工具和系统定时任务即可实现全自动化的本地全库备份+远程传输流程。以下是具体实现细节：

一、核心操作逻辑 #

用pg_dumpall导出本地所有PostgreSQL数据库（含角色、表空间配置）→ 将备份文件通过rsync/scp传输到远程服务器→ 封装为脚本后用cron定时执行。

二、具体实现步骤 #

1. 导出全库到单个SQL文件 #

使用PostgreSQL自带的pg_dumpall（而非单库导出的pg_dump），可一次性导出所有数据库及全局配置：

# 以postgres超级用户导出，生成带时间戳的压缩文件（节省空间）
pg_dumpall -U postgres | gzip > /tmp/all_pg_databases_$(date +%Y%m%d_%H%M%S).sql.gz

• 若不需要压缩，去掉| gzip即可；若你的超级用户不是postgres，替换为对应用户名。

2. 传输备份文件到远程服务器 #

推荐用rsync（支持断点续传、增量传输，大文件更高效），也可使用scp：

# rsync示例（替换为你的远程服务器信息）
rsync -avz /tmp/all_pg_databases_20240520_020000.sql.gz remote_user@192.168.1.100:/var/backups/postgres/

• 关键前提：必须配置SSH密钥无密码登录，否则cron执行时会因需要输入密码而失败：

  # 本地生成密钥对（一路回车用默认配置）
  ssh-keygen -t ed25519
  # 将公钥复制到远程服务器
  ssh-copy-id remote_user@192.168.1.100
  

3. 封装为可执行脚本 #

将上述步骤写入脚本（比如/usr/local/bin/pg_full_backup.sh），方便cron调用：

#!/bin/bash
# PostgreSQL全库备份并远程传输脚本

# 配置参数
BACKUP_DIR="/tmp"
REMOTE_USER="remote_user"
REMOTE_HOST="192.168.1.100"
REMOTE_PATH="/var/backups/postgres"
PG_USER="postgres"

# 生成带时间戳的备份文件名
BACKUP_FILE="${BACKUP_DIR}/all_pg_databases_$(date +%Y%m%d_%H%M%S).sql.gz"

# 执行导出并压缩
pg_dumpall -U "${PG_USER}" | gzip > "${BACKUP_FILE}"

# 检查导出是否成功
if [ $? -ne 0 ]; then
    echo "$(date): 全库导出失败" >> /var/log/pg_backup_error.log
    exit 1
fi

# 传输到远程服务器
rsync -avz "${BACKUP_FILE}" "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}/"

# 检查传输是否成功
if [ $? -ne 0 ]; then
    echo "$(date): 备份文件传输失败" >> /var/log/pg_backup_error.log
    exit 1
fi

# 删除本地备份文件（可选，需保留则注释）
rm "${BACKUP_FILE}"

# 记录成功日志
echo "$(date): 全库备份并传输成功" >> /var/log/pg_backup_success.log

给脚本添加执行权限：

chmod +x /usr/local/bin/pg_full_backup.sh

4. 设置cron定时任务 #

编辑当前用户的cron任务（若需以postgres用户执行，用su - postgres -c 'crontab -e'）：

crontab -e

添加定时规则，比如每天凌晨2点执行，并将输出写入日志：

# 每天凌晨2点执行备份脚本，日志追加到pg_backup.log
0 2 * * * /usr/local/bin/pg_full_backup.sh >> /var/log/pg_backup.log 2>&1

• 若执行失败，检查pg_dumpall的绝对路径（用which pg_dumpall查看），在脚本中替换为绝对路径即可（比如/usr/bin/pg_dumpall）。

三、关键注意事项 #

• 权限验证：执行脚本的用户必须拥有PostgreSQL超级用户权限，以及远程服务器的SSH登录权限。
• 空间监控：定期检查远程服务器备份目录的剩余空间，避免因空间不足导致备份失败。
• 日志排查：定期查看/var/log/pg_backup.log和错误日志，确认备份流程正常。
• 备份验证：定期从远程服务器下载备份文件，尝试恢复到测试环境，确保备份有效。

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