postgresql

mkdir data conf logs scripts backups

拷贝配置

docker run --rm \
  -v $(pwd)/conf:/tmp/conf \
  postgres:16 \
  bash -c "cp /usr/share/postgresql/16/postgresql.conf.sample /tmp/conf/postgresql.conf && cp  /usr/share/postgresql/16/pg_hba.conf.sample /tmp/conf/pg_hba.conf"

（可选）开启 logs，vim conf/postgresql.conf

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_statement = 'none'
log_min_duration_statement = 1000

创建变量 vim .env

POSTGRES_USER=myuser
POSTGRES_PASSWORD=mypassword
POSTGRES_DB=mydb

---

vim scripts/backup.sh

chmod +x scripts/backup.sh

#!/bin/sh
# backup.sh - 在 docker 容器里执行 pg_dump 备份
set -e

# ---- 环境变量 ----
PGHOST="${PGHOST:-postgres}"
PGUSER="${POSTGRES_USER}"
PGDB="${POSTGRES_DB}"
PGPASSWORD="${POSTGRES_PASSWORD}"
BACKUP_DIR="/backups"
KEEP_DAYS=7

# ---- 文件名 ----
DATE=$(date +"%F_%H%M%S")
BACKUP_FILE="$BACKUP_DIR/${PGDB}_backup_$DATE.sql.gz"
LOG_FILE="$BACKUP_DIR/backup_$DATE.log"

echo "[$(date)] 开始备份数据库 $PGDB" | tee -a "$LOG_FILE"

export PGPASSWORD

# ---- 执行备份 ----
pg_dump -h "$PGHOST" -U "$PGUSER" "$PGDB" | gzip > "$BACKUP_FILE"

echo "[$(date)] 备份完成 -> $BACKUP_FILE" | tee -a "$LOG_FILE"

# ---- 清理过期备份 ----
echo "[$(date)] 清理过期备份..." | tee -a "$LOG_FILE"
BACKUP_COUNT=$(ls -1 "$BACKUP_DIR"/*_backup_*.sql.gz 2>/dev/null | wc -l)
if [ "$BACKUP_COUNT" -gt "$KEEP_DAYS" ]; then
  ls -1tr "$BACKUP_DIR"/*_backup_*.sql.gz \
    | head -n $(("$BACKUP_COUNT" - "$KEEP_DAYS")) \
    | xargs -r rm -f
fi

# ---- 清理过期日志 ----
LOG_COUNT=$(ls -1 "$BACKUP_DIR"/backup_*.log 2>/dev/null | wc -l)
if [ "$LOG_COUNT" -gt "$KEEP_DAYS" ]; then
  ls -1tr "$BACKUP_DIR"/backup_*.log \
    | head -n $(("$LOG_COUNT" - "$KEEP_DAYS")) \
    | xargs -r rm -f
fi

echo "[$(date)] 清理完成" | tee -a "$LOG_FILE"

---

vim scripts/cron_start.sh

chmod +x scripts/cron_start.sh

#!/bin/bash
# cron_start.sh - 在宿主机添加定时备份任务
set -e

# ---- 路径计算 ----
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"

ENV_FILE="$PROJECT_ROOT/.env"
BACKUP_DIR="$PROJECT_ROOT/backups"
SCRIPTS_DIR="$PROJECT_ROOT/scripts"
NETWORK_NAME="nginx-proxy-net"
CRON_SCHEDULE="0 2 * * *"   # 每天凌晨 2 点执行

# ---- 防御性检查 ----
[ -f "$ENV_FILE" ] || { echo ".env 文件不存在"; exit 1; }
[ -d "$BACKUP_DIR" ] || { echo "backups 目录不存在"; exit 1; }

# ---- 构造 cron 命令 ----
CRON_CMD=". $ENV_FILE && docker run --rm \
  --network $NETWORK_NAME \
  -v $BACKUP_DIR:/backups \
  -v $SCRIPTS_DIR:/scripts \
  -e POSTGRES_USER=\$POSTGRES_USER \
  -e POSTGRES_PASSWORD=\$POSTGRES_PASSWORD \
  -e POSTGRES_DB=\$POSTGRES_DB \
  -e PGHOST=postgres \
  postgres:16 /bin/sh /scripts/backup.sh"

# ---- 检查是否已存在 ----
if crontab -l 2>/dev/null | grep -Fq "$CRON_CMD"; then
    echo "cron 任务已存在，跳过添加"
    exit 0
fi

# ---- 添加 cron 任务 ----
( crontab -l 2>/dev/null; echo "$CRON_SCHEDULE $CRON_CMD" ) | crontab -

echo "cron 任务已添加："
echo "$CRON_SCHEDULE $CRON_CMD"

---

vim docker-compose.yml

networks:
  use-external-net:
    external: true # 使用外部网络
    name: nginx-proxy-net

services:
  postgres:
    networks:
      - use-external-net
    image: postgres:16
    container_name: pg-prod
    restart: unless-stopped
    env_file: .env
    environment:
      POSTGRES_INITDB_ARGS: "-E UTF8"
    volumes:
      - ./data:/var/lib/postgresql/data
      - ./conf:/etc/postgresql
      - ./logs:/var/lib/postgresql/log
    ports:
      - "5432:5432"
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER}"]
      interval: 10s
      timeout: 5s
      retries: 5

环境变量	说明
PGDATA	数据目录，默认 /var/lib/postgresql/data
POSTGRES_INITDB_ARGS	初始化数据库参数，如编码 --encoding=UTF8
POSTGRES_PASSWORD	必须，用户密码
POSTGRES_USER	超级用户
POSTGRES_DB	创建默认数据库

postgresql.conf

直接挂目录

未实践

version: "3.9"

services:
  postgres:
    image: postgres:16
    container_name: pg-prod
    restart: unless-stopped
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydb
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      # 数据持久化
      - pgdata:/var/lib/postgresql/data
      # 配置文件（可选自定义）
      - ./conf:/etc/postgresql/conf
      # 日志挂载
      - ./logs:/var/lib/postgresql/log
    ports:
      - "5432:5432"

  pg_backup:
    image: postgres:16
    container_name: pg-backup
    depends_on:
      - postgres
    restart: "no"
    environment:
      PGPASSWORD: mypassword
    volumes:
      - pgdata:/var/lib/postgresql/data:ro
      - ./backups:/backups
    entrypoint: >
      /bin/sh -c "
        mkdir -p /backups &&
        pg_dump -h postgres -U myuser mydb > /backups/backup_$(date +%F).sql
      "

volumes:
  pgdata:

迁移

需 docker stop 停掉服务

volumes:
  - /data/postgres:/var/lib/postgresql/data

rsync -avz /data/postgres user@newhost:/data/postgres

使用 volume

迁移

需 docker stop 停掉服务

volumes:
  pgdata:

导出卷：

docker run --rm -v pgdata:/data -v /tmp:/backup busybox tar czf /backup/pgdata.tar.gz -C /data .

拷贝 `pgdata.tar.gz 到新服务器

创建 docker volume create pgdata

导入卷：

docker run --rm -v pgdata:/data -v /tmp:/backup busybox tar xzf /backup/pgdata.tar.gz -C /data