备份 Postgres 数据，自动上传至 Cloudflare R2 对象存储

安装 Rclone

Rclone 是一个命令行程序，同步你的文件到云存储。

在 Linux/BSD/macOS 系统安装，运行命令：

sudo -v ; curl https://rclone.org/install.sh | sudo bash

在 Windowes 系统安装，下载程序压缩包，解压并运行 rclone.exe。

配置 Rclone

终端运行：

rclone config

选择 Cloudflare R2 为远端存储：

n) New remote
name> r2
Storage> s3
provider> Cloudflare
env_auth> false
access_key_id> <YOUR_ACCESS_KEY>
secret_access_key> <YOUR_SECRET_KEY>
region> auto
endpoint> https://<ACCOUNT_ID>.r2.cloudflarestorage.com
Edit advanced config? > n
Keep this "r2" remote? > y

生成的配置文件存储在 ~/.config/rclone/rclone.conf，内容如下：

[r2]
type = s3
provider = Cloudflare
access_key_id = abc123
secret_access_key = xyz456
endpoint = https://<accountid>.r2.cloudflarestorage.com~/.config/rclone/rclone.conf

更多细节参考 rclone config docs。

[!WARNING] 如果你的 Cloudflare 对象存储 token 使用 Object-level permissions，你需要在 rclone 配置中增加 no_check_bucket = true。

[r2]
type = s3
provider = Cloudflare
access_key_id = abc123
secret_access_key = xyz456
endpoint = https://<accountid>.r2.cloudflarestorage.com
no_check_bucket = true~/.config/rclone/rclone.conf

Rclone 命令

List buckets & objects

rclone tree r2:
rclone tree r2:databasebackup_pg_rclone.sh

Upload and retrieve objects

# Upload dog.txt to the database bucket
rclone copy dog.txt r2:database/
rclone tree r2:database

# Download dog.txt from the database bucket
rclone copy r2:database/dot.txt .

更多命令细节参考 Rclone Commands。

Postgress 数据库备份

使用 pg_dumpall 既可以备份所有数据库数据（通过在集群每个数据库中调用 pg_dump 实现），又能备份每个数据库公用的全局对象，即 database roles, tablespaces 和 privilege grants for configuration parameters。（pg_dump 不会保存这些全局对象）。

基础命令

pg_dumpall > dumpfile

恢复备份数据（从 pg_dumpall 生成的文件）

psql -X -f dumpfile dbname
# 或者
psql -X dbname < dumpfile

注意：dbname 可以使用默认数据库 postgres

It is not important which database you connect to here since the script file created by pg_dumpall will contain the appropriate commands to create and connect to the saved databases. An exception is that if you specified --clean, you must connect to the postgres database initially; the script will attempt to drop other databases immediately, and that will fail for the database you are connected to. ---- 参考文档

压缩备份数据

pg_dumpall | gzip > filename.gz

恢复压缩数据（从 pg_dumpall 生成的文件）

gunzip -c filename.gz | psql -X dbname
# 或者
cat filename.gz | gunzip | psql -X dbname

恢复 psql 无法加载的自定义文件格式备份数据

pg_restore -d dbname filename

使用 Docker 方式部署 Postgres

备份数据

docker exec -i pg_container_name pg_dumpall | gzip > filename.gz

或者

docker exec -i pg_container_name pg_dumpall > filename.sql
gzip -c filename.sql > filename.gz

恢复数据

cat filename.gz | gunzip | docker exec -i pg_container_name psql -X dbname

或者

gunzip -c filename.gz | docker exec -i pg_container_name psql -X dbname

查看 pg_dump 、 pg_restore、pg_dumpall 和 psql 引用获取更多细节。

Postgres 数据自动备份和上传 Cloudflare R2 脚本

#!/bin/bash

# ------------------------
# 配置部分
# ------------------------
RCLONE_REMOTE="r2:database"   # rclone 远程 bucket
PG_CONTAINER="wcskkokws4co0o8800s0wcs0" # Docker 容器名
PG_USER_NAME="postgres"
BACKUP_DIR="/data/backups/postgres"
DATE=$(date +"%Y%m%d_%H%M%S")

# ------------------------
# 创建备份目录
# ------------------------
mkdir -p "$BACKUP_DIR"

# ------------------------
# 导出 PostgreSQL 数据库
# ------------------------
BACKUP_FILE="pg_dumpall_$DATE.sql"
BACKUP_PATH="$BACKUP_DIR/$BACKUP_FILE"

echo "[$(date)] 开始备份 PostgreSQL..."
docker exec -i "$PG_CONTAINER" pg_dumpall -U "$PG_USER_NAME" > "$BACKUP_PATH"

if [ $? -ne 0 ]; then
    echo "[$(date)] PostgreSQL 导出失败"
    exit 1
fi

# ------------------------
# 压缩备份文件
# ------------------------
GZ_FILE="$BACKUP_PATH.gz"
gzip -c "$BACKUP_PATH" > "$GZ_FILE"
rm -f "$BACKUP_PATH"

# ------------------------
# 上传到 Cloudflare R2
# ------------------------
echo "[$(date)] 上传到 R2..."
rclone copy "$GZ_FILE" "$RCLONE_REMOTE" --progress --retries 3 --low-level-retries 5

if [ $? -ne 0 ]; then
    echo "[$(date)] 上传失败"
    exit 1
fi

echo "[$(date)] 上传成功: $RCLONE_REMOTE/$DATE.sql.gz"

给脚本添加可执行权限

chmod +x backup_pg_rclone.sh

设置 Cron 定时任务

终端运行 crontab -e，编辑任务

0 1 * * * /path/to/backup_pg_rclone.sh >> /var/log/pg_backup_rclone.log 2>&1