fed notes

Appearance

wanx n3 ​

pg 17

compose ​

yml
services:
  postgres:
    image: registry.cn-chengdu.aliyuncs.com/qins-open-img/postgres:17-alpine
    container_name: postgres17
    restart: unless-stopped

    env_file:
      - .env

    ports:
      - "127.0.0.1:5432:5432"

    volumes:
      - ./data:/var/lib/postgresql/data
      - ./backup:/backup

    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER"]
      interval: 10s
      timeout: 5s
      retries: 5
      
    logging:
      driver: local
      options:
        max-size: "10m"
        max-file: "3"

    networks:
      pg17-net:
        aliases:
          - pg17

networks:
  pg17-net:
    external: true

logging 开启日志轮转

自动备份 并 上传oss ​

sh
mkdir -p scripts backup-logs

vim scripts/backup.sh
sh
#!/usr/bin/env bash
set -euo pipefail
umask 077

# cron 不会读取用户的 Shell 配置，显式指定命令路径。
PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"

PG_CONTAINER="postgres17"
PG_USER="tdp"
PG_DATABASE="tdpdb"

BACKUP_DIR="/opt/pg17/backup"
OSS_BASE_PREFIX="oss://qins-backup-oss/3dp/pg17"
OSS_CONFIG="/opt/pg17/ossutil.conf"
OSSUTIL="/usr/local/bin/ossutil"
BACKUP_TZ="Asia/Shanghai"

mkdir -p "$BACKUP_DIR"

# 避免上一次备份尚未结束，又被 cron 启动一次。
exec 9>"$BACKUP_DIR/.backup.lock"
flock -n 9 || exit 0

# OSS 按年月分目录；文件名保留年月日与精确时间。
backup_month="$(TZ="$BACKUP_TZ" date +%Y-%m)"
timestamp="$(TZ="$BACKUP_TZ" date +%Y%m%dT%H%M%S%z)"
OSS_PREFIX="${OSS_BASE_PREFIX}/${backup_month}"

filename="${PG_DATABASE}_${timestamp}.dump"
tmp_host_file="$BACKUP_DIR/.${filename}.partial"
backup_file="$BACKUP_DIR/$filename"

trap 'rm -f -- "$tmp_host_file"' EXIT

echo "[$(TZ="$BACKUP_TZ" date '+%F %T')] backup start: ${PG_DATABASE}"

# /backup 是 PostgreSQL 容器内、挂载到宿主机 BACKUP_DIR 的目录。
docker exec "$PG_CONTAINER" \
  pg_dump \
  -U "$PG_USER" \
  -d "$PG_DATABASE" \
  -Fc \
  -f "/backup/.${filename}.partial"

# 同一文件系统内 mv 是原子操作，避免误把半成品当作完整备份。
mv "$tmp_host_file" "$backup_file"
trap - EXIT

# 上传所有尚未成功上传的本地备份；.uploaded 是成功标记。
for file in "$BACKUP_DIR"/"${PG_DATABASE}"_*.dump; do
  [ -e "$file" ] || continue
  [ -e "$file.uploaded" ] && continue

  sha256sum "$file" > "$file.sha256"

  "$OSSUTIL" cp -c "$OSS_CONFIG" \
    "$file" \
    "$OSS_PREFIX/$(basename "$file")"

  "$OSSUTIL" cp -c "$OSS_CONFIG" \
    "$file.sha256" \
    "$OSS_PREFIX/$(basename "$file").sha256"

  touch "$file.uploaded"
done

# 仅在待上传文件都成功上传后，清理 14 天前的本地备份。
find "$BACKUP_DIR" -type f \
  \( -name "${PG_DATABASE}_*.dump" \
     -o -name "${PG_DATABASE}_*.dump.sha256" \
     -o -name "${PG_DATABASE}_*.dump.uploaded" \) \
  -mtime +14 -delete

echo "[$(TZ="$BACKUP_TZ" date '+%F %T')] backup success: $backup_file"

安装 ossutil ​

sh
uname -m
sudo apt update
sudo apt install -y curl unzip

cd /tmp

curl -fLO https://gosspublic.alicdn.com/ossutil/v2/2.3.0/ossutil-2.3.0-linux-amd64.zip

echo "3ae4d9fc85a7a6e9f5654d1599766f1a3a42a3692870887b5ae9338d582ef65a  ossutil-2.3.0-linux-amd64.zip" \
  | sha256sum -c -

unzip ossutil-2.3.0-linux-amd64.zip

sudo install -m 0755 ossutil-2.3.0-linux-amd64/ossutil /usr/local/bin/ossutil

ossutil --help

如果 uname -m 输出是 aarch64，则下载地址里的文件名改为：ossutil-2.3.0-linux-arm64.zip

生产配置文件

sh
ossutil config -c /opt/pg17/ossutil.conf

chmod 600 /opt/pg17/ossutil.conf

配置测试：

sh
ossutil -c /opt/pg17/ossutil.conf \
  ls oss://qins-backup-oss/3dp/pg17/ -d

设置 cron ​

sh
chmod +x /opt/pg17/scripts/backup.sh

# 开始设置定时任务
crontab -e

# 新增一行写入
# 0 2 * * * /opt/pg17/scripts/backup.sh >> /opt/pg17/backup-logs/backup.log 2>&1
# 0 2 * * * /opt/pg17/scripts/backup.sh >> /opt/pg17/backup-logs/backup-$(date +\%F).log 2>&1
0 2 * * * mkdir -p /opt/pg17/backup-logs/$(date +\%Y-\%m) && /opt/pg17/scripts/backup.sh >> /opt/pg17/backup-logs/$(date +\%Y-\%m)/backup-$(date +\%F).log 2>&1

# 检查
crontab -l

backup-logs 定时清理 ​

sh
vim scripts/cleanup-backup-logs.sh
sh
#!/usr/bin/env bash
set -euo pipefail

LOG_ROOT="/opt/pg17/backup-logs"
target_month="$(date -d '2 months ago' +%Y-%m)"
target_dir="${LOG_ROOT}/${target_month}"

# 防止变量或路径异常时误删其他目录。
case "$target_dir" in
  "$LOG_ROOT"/????-??) ;;
  *)
    echo "invalid target directory: $target_dir" >&2
    exit 1
    ;;
esac

[ -d "$target_dir" ] || exit 0

rm -rf -- "$target_dir"

echo "[$(date '+%F %T')] removed log directory: $target_dir"
sh
chmod +x /opt/pg17/scripts/cleanup-backup-logs.sh
5 3 1 * * /opt/pg17/scripts/cleanup-backup-logs.sh >> /opt/pg17/backup-logs-cleanup.log 2>&1