deploy docs

Appearance

pg_dump + oss + systemd timer ​

sh
postgres18/
├── compose.yml
├── .env
├── data/
├── backup/
└── scripts/
    ├── backup.sh
    └── config.env

安装 oss util ​

sh
sudo apt update
sudo apt install -y curl unzip ca-certificates

sudo -v
curl -fsSL https://gosspublic.alicdn.com/ossutil/install.sh | sudo bash

方式2

sh
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

cd ossutil-2.3.0-linux-amd64

chmod 755 ossutil

sudo install -m 755 ossutil /usr/local/bin/ossutil

创建配置

sh
ossutil config -c /opt/base/pg18-vector/ossutil.conf

chmod 600 /opt/base/pg18-vector/ossutil.conf

备份脚本 ​

sh
vim config.env
sh
CONTAINER_NAME=pg18-vector

# 本地保留多少天
LOCAL_RETENTION_DAYS=7

# OSS
OSS_DEST=oss://your-bucket/postgres/pg18-vector
sh
vim backup.sh
sh
#!/usr/bin/env bash

set -Eeuo pipefail


# =========================================================
# 基础路径
# =========================================================

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_DIR="$(cd "${SCRIPT_DIR}/.." && pwd)"

CONFIG_FILE="${SCRIPT_DIR}/config.env"

if [[ ! -f "${CONFIG_FILE}" ]]; then
  echo "ERROR: config file not found: ${CONFIG_FILE}"
  exit 1
fi

# shellcheck disable=SC1090
source "${CONFIG_FILE}"


# =========================================================
# 配置检查
# =========================================================

: "${CONTAINER_NAME:?CONTAINER_NAME is required}"
: "${LOCAL_RETENTION_DAYS:?LOCAL_RETENTION_DAYS is required}"

BACKUP_ROOT="${PROJECT_DIR}/backup"

MONTH="$(date '+%Y-%m')"
TIMESTAMP="$(date '+%Y%m%d_%H%M%S')"

MONTH_DIR="${BACKUP_ROOT}/${MONTH}"

BACKUP_NAME="${TIMESTAMP}"
TMP_NAME=".${BACKUP_NAME}.incomplete"

TMP_DIR="${MONTH_DIR}/${TMP_NAME}"
FINAL_DIR="${MONTH_DIR}/${BACKUP_NAME}"

LOCK_FILE="${BACKUP_ROOT}/.backup.lock"

# 标记本地备份是否已经完成
LOCAL_BACKUP_FINALIZED=false


# =========================================================
# 日志
# =========================================================

log() {
  echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"
}

fail() {
  log "ERROR: $*"
  exit 1
}


# =========================================================
# 异常处理
# =========================================================

on_error() {
  local exit_code=$?

  log "========================================"
  log "backup failed with exit code ${exit_code}"

  if [[ "${LOCAL_BACKUP_FINALIZED}" == "true" ]]; then

    log "local backup was completed successfully:"
    log "${FINAL_DIR}"

    if [[ ! -f "${FINAL_DIR}/.uploaded" ]]; then
      log "OSS upload was not completed"
      log "local backup will NOT be removed automatically"
    fi

  elif [[ -d "${TMP_DIR}" ]]; then

    log "incomplete backup kept at:"
    log "${TMP_DIR}"

  fi

  log "========================================"

  exit "${exit_code}"
}

trap on_error ERR


# =========================================================
# 检查宿主机依赖
# =========================================================

command -v docker >/dev/null 2>&1 \
  || fail "docker command not found"

command -v flock >/dev/null 2>&1 \
  || fail "flock command not found"

command -v sha256sum >/dev/null 2>&1 \
  || fail "sha256sum command not found"

command -v gzip >/dev/null 2>&1 \
  || fail "gzip command not found"


# =========================================================
# OSS 配置检查
#
# config.env 示例：
#
#   OSS_DEST=oss://qins-backup-oss/postgres/pg18-vector
#   OSS_CONFIG=ossutil.conf
#
# OSS_CONFIG 如果不是绝对路径，则默认相对于 PROJECT_DIR。
#
# 例如：
#
#   ossutil.conf
#
# 会解析成：
#
#   /opt/base/pg18-vector/ossutil.conf
# =========================================================

if [[ -n "${OSS_DEST:-}" ]]; then

  command -v ossutil >/dev/null 2>&1 \
    || fail "ossutil command not found"

  : "${OSS_CONFIG:?OSS_CONFIG is required when OSS_DEST is configured}"

  if [[ "${OSS_CONFIG}" != /* ]]; then
    OSS_CONFIG="${PROJECT_DIR}/${OSS_CONFIG}"
  fi

  if [[ ! -f "${OSS_CONFIG}" ]]; then
    fail "ossutil config file not found: ${OSS_CONFIG}"
  fi

  if [[ ! -r "${OSS_CONFIG}" ]]; then
    fail "ossutil config file is not readable: ${OSS_CONFIG}"
  fi

fi


# =========================================================
# 创建备份目录
# =========================================================

mkdir -p "${BACKUP_ROOT}"
mkdir -p "${MONTH_DIR}"


# =========================================================
# 防止重复执行
# =========================================================

exec 9>"${LOCK_FILE}"

if ! flock -n 9; then
  fail "another backup process is already running"
fi


# =========================================================
# 检查 PostgreSQL 容器
# =========================================================

if ! docker inspect "${CONTAINER_NAME}" >/dev/null 2>&1; then
  fail "container not found: ${CONTAINER_NAME}"
fi

CONTAINER_RUNNING="$(
  docker inspect \
    -f '{{.State.Running}}' \
    "${CONTAINER_NAME}"
)"

if [[ "${CONTAINER_RUNNING}" != "true" ]]; then
  fail "container is not running: ${CONTAINER_NAME}"
fi


# =========================================================
# 检查 PostgreSQL 是否可用
# =========================================================

log "checking PostgreSQL..."

if ! docker exec "${CONTAINER_NAME}" sh -c '
  export PGPASSWORD="$POSTGRES_PASSWORD"

  pg_isready \
    -h 127.0.0.1 \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB"
' >/dev/null 2>&1; then
  fail "PostgreSQL is not ready"
fi

log "PostgreSQL is ready"


# =========================================================
# 创建临时备份目录
# =========================================================

if [[ -e "${TMP_DIR}" ]]; then
  fail "temporary backup directory already exists: ${TMP_DIR}"
fi

if [[ -e "${FINAL_DIR}" ]]; then
  fail "backup directory already exists: ${FINAL_DIR}"
fi

mkdir -p "${TMP_DIR}"


# =========================================================
# 获取数据库列表
#
# 排除：
#
#   template0
#   template1
#
# postgres 如果存在，会正常备份。
# =========================================================

log "reading database list..."

mapfile -t DATABASES < <(
  docker exec "${CONTAINER_NAME}" sh -c '
    export PGPASSWORD="$POSTGRES_PASSWORD"

    psql \
      -h 127.0.0.1 \
      -U "$POSTGRES_USER" \
      -d "$POSTGRES_DB" \
      -At \
      -c "
        SELECT datname
        FROM pg_database
        WHERE datallowconn = true
          AND datistemplate = false
        ORDER BY datname;
      "
  '
)

if [[ "${#DATABASES[@]}" -eq 0 ]]; then
  fail "no database found"
fi

log "found ${#DATABASES[@]} database(s):"

for db in "${DATABASES[@]}"; do
  log "  - ${db}"
done


# =========================================================
# 数据库名安全检查
#
# database name 会直接作为备份文件名。
#
# 当前允许：
#
#   A-Z
#   a-z
#   0-9
#   _
#   .
#   -
#
# 如果以后需要中文、空格等名称，再增加 filename
# encode 逻辑。
# =========================================================

for db in "${DATABASES[@]}"; do

  if [[ ! "${db}" =~ ^[A-Za-z0-9_.-]+$ ]]; then
    fail "unsupported database name for backup filename: ${db}"
  fi

done


# =========================================================
# 备份 PostgreSQL globals
#
# 包括：
#
#   roles
#   grants
#   tablespaces
#   role configuration
#
# database 本身单独使用 pg_dump。
# =========================================================

log "dumping PostgreSQL globals..."

docker exec "${CONTAINER_NAME}" sh -c '
  export PGPASSWORD="$POSTGRES_PASSWORD"

  exec pg_dumpall \
    -h 127.0.0.1 \
    -U "$POSTGRES_USER" \
    --globals-only
' | gzip > "${TMP_DIR}/globals.sql.gz"

if [[ ! -s "${TMP_DIR}/globals.sql.gz" ]]; then
  fail "globals backup is empty"
fi

log "globals backup completed"


# =========================================================
# 逐 database 备份
#
# 使用 PostgreSQL custom format：
#
#   pg_dump -Fc
#
# 优点：
#
#   - 自带压缩
#   - 支持 pg_restore
#   - 支持选择性恢复
#   - 比 plain SQL 更适合正式备份
# =========================================================

for db in "${DATABASES[@]}"; do

  FILE="${TMP_DIR}/${db}.dump"

  log "dumping database: ${db}"

  docker exec "${CONTAINER_NAME}" sh -c '
    export PGPASSWORD="$POSTGRES_PASSWORD"

    exec pg_dump \
      -h 127.0.0.1 \
      -U "$POSTGRES_USER" \
      -Fc \
      -d "$1"
  ' sh "${db}" > "${FILE}"

  if [[ ! -s "${FILE}" ]]; then
    fail "database backup is empty: ${db}"
  fi

  log "database backup completed: ${db}"

done


# =========================================================
# 校验 database dump
#
# compose 中已经配置：
#
#   ./backup:/backup
#
# 因此宿主机：
#
#   backup/2026-08/.xxx.incomplete/
#
# 对应容器：
#
#   /backup/2026-08/.xxx.incomplete/
# =========================================================

log "verifying database dumps..."

for db in "${DATABASES[@]}"; do

  CONTAINER_FILE="/backup/${MONTH}/${TMP_NAME}/${db}.dump"

  if ! docker exec "${CONTAINER_NAME}" \
    pg_restore \
    -l \
    "${CONTAINER_FILE}" \
    >/dev/null; then

    fail "backup verification failed: ${db}"
  fi

  log "verified: ${db}"

done


# =========================================================
# 校验 globals gzip
# =========================================================

log "verifying globals backup..."

gzip -t "${TMP_DIR}/globals.sql.gz"

log "globals backup verified"


# =========================================================
# 生成 SHA256
# =========================================================

log "generating SHA256 checksums..."

(
  cd "${TMP_DIR}"

  sha256sum \
    globals.sql.gz \
    ./*.dump \
> SHA256SUMS
)

if [[ ! -s "${TMP_DIR}/SHA256SUMS" ]]; then
  fail "failed to generate SHA256SUMS"
fi

log "SHA256 checksums generated"


# =========================================================
# 生成备份信息
# =========================================================

POSTGRES_VERSION="$(
  docker exec "${CONTAINER_NAME}" \
    postgres --version
)"

HOSTNAME_VALUE="$(hostname)"

{
  echo "backup_version=1"
  echo "timestamp=${TIMESTAMP}"
  echo "month=${MONTH}"
  echo "hostname=${HOSTNAME_VALUE}"
  echo "container=${CONTAINER_NAME}"
  echo "postgres_version=${POSTGRES_VERSION}"
  echo "database_count=${#DATABASES[@]}"
  echo

  echo "[databases]"

  for db in "${DATABASES[@]}"; do
    echo "${db}"
  done

} > "${TMP_DIR}/backup.info"


# =========================================================
# 完成本地备份
#
# 只有：
#
#   dump
#   verify
#   checksum
#   backup.info
#
# 全部完成后，才从：
#
#   .xxx.incomplete
#
# 原子改名为：
#
#   xxx
# =========================================================

log "finalizing local backup..."

mv "${TMP_DIR}" "${FINAL_DIR}"

LOCAL_BACKUP_FINALIZED=true

log "local backup completed:"
log "${FINAL_DIR}"


# =========================================================
# 上传 OSS
#
# 最终结构：
#
# oss://qins-backup-oss/postgres/pg18-vector/
#
# └── 2026-08/
#     └── 20260825_030001/
#         ├── postgres.dump
#         ├── xxx.dump
#         ├── globals.sql.gz
#         ├── SHA256SUMS
#         └── backup.info
#
#
# 注意：
#
# .uploaded 是本地状态文件。
#
# OSS 上传全部成功后才创建。
#
# 当前使用 ossutil v1.7.19，因此只使用兼容的
# 基础参数。
# =========================================================

if [[ -n "${OSS_DEST:-}" ]]; then

  OSS_TARGET="${OSS_DEST%/}/${MONTH}/${BACKUP_NAME}/"

  log "uploading backup to OSS..."
  log "target: ${OSS_TARGET}"

  ossutil \
    -c "${OSS_CONFIG}" \
    cp \
    -r \
    -f \
    "${FINAL_DIR}/" \
    "${OSS_TARGET}"

  touch "${FINAL_DIR}/.uploaded"

  log "OSS upload completed"

else

  log "OSS_DEST is empty, skip OSS upload"

fi


# =========================================================
# 清理本地旧备份
#
# 必须同时满足：
#
#   1. 超过 LOCAL_RETENTION_DAYS
#   2. 存在 .uploaded
#
# 没有成功上传 OSS 的备份绝不自动删除。
# =========================================================

log "cleaning local backups older than ${LOCAL_RETENTION_DAYS} days..."

find "${BACKUP_ROOT}" \
  -mindepth 2 \
  -maxdepth 2 \
  -type d \
  -mtime "+${LOCAL_RETENTION_DAYS}" \
  -print0 |
while IFS= read -r -d '' dir; do

  name="$(basename "${dir}")"

  # incomplete 单独处理
  if [[ "${name}" == .*".incomplete" ]]; then
    continue
  fi

  if [[ -f "${dir}/.uploaded" ]]; then

    log "removing old local backup:"
    log "${dir}"

    rm -rf -- "${dir}"

  else

    log "skip old backup because it was not uploaded:"
    log "${dir}"

  fi

done


# =========================================================
# 清理非常旧的 incomplete 目录
#
# 默认：
#
#   INCOMPLETE_RETENTION_DAYS=7
#
# 保留失败现场一段时间，方便排查。
# =========================================================

INCOMPLETE_RETENTION_DAYS="${INCOMPLETE_RETENTION_DAYS:-7}"

log "cleaning incomplete backups older than ${INCOMPLETE_RETENTION_DAYS} days..."

find "${BACKUP_ROOT}" \
  -mindepth 2 \
  -maxdepth 2 \
  -type d \
  -name '.*.incomplete' \
  -mtime "+${INCOMPLETE_RETENTION_DAYS}" \
  -print0 |
while IFS= read -r -d '' dir; do

  log "removing old incomplete backup:"
  log "${dir}"

  rm -rf -- "${dir}"

done


# =========================================================
# 删除空月份目录
#
# 例如：
#
# backup/
#
# ├── 2026-06/   <- 已空，删除
# ├── 2026-07/
# └── 2026-08/
# =========================================================

find "${BACKUP_ROOT}" \
  -mindepth 1 \
  -maxdepth 1 \
  -type d \
  -empty \
  -delete


# =========================================================
# 完成
# =========================================================

log "========================================"
log "backup finished successfully"
log "backup: ${MONTH}/${BACKUP_NAME}"
log "databases: ${#DATABASES[@]}"
log "local: ${FINAL_DIR}"

if [[ -n "${OSS_DEST:-}" ]]; then
  log "OSS: uploaded"
  log "OSS target: ${OSS_TARGET}"
else
  log "OSS: skipped"
fi

log "========================================"

systemd timer ​

sh
sudo timedatectl set-timezone Asia/Shanghai
sh
sudo vim /etc/systemd/system/pg18-backup.service
sh
[Unit]
Description=PostgreSQL 18 Backup
After=docker.service network-online.target

[Service]
Type=oneshot

User=qin
WorkingDirectory=/opt/base/pg18-vector

ExecStart=/opt/base/pg18-vector/scripts/backup.sh

# 数据库备份可能随着数据量增长超过 systemd 默认超时时间
TimeoutStartSec=0

# 新生成的备份文件默认仅当前用户可访问
UMask=0077
sh
sudo vim /etc/systemd/system/pg18-backup.timer
sh
[Unit]
Description=PostgreSQL 18 Daily Backup

[Timer]
OnCalendar=*-*-* 03:00:00
# 错过时间补救
Persistent=true

Unit=pg18-backup.service

[Install]
WantedBy=timers.target

启动：

sh
sudo systemctl daemon-reload

sudo systemctl enable --now pg18-backup.timer
sh
# 看下次什么时候执行
systemctl list-timers pg18-backup.timer

# 看最近一次备份日志
journalctl -u pg18-backup.service -n 100 --no-pager

# 手动立即备份一次
sudo systemctl start pg18-backup.service