pg 17

mkdir -p /opt/pg17
cd /opt/pg17
mkdir data backup 
docker network create pg-17-net

vim .env

POSTGRES_DB=admin_db
POSTGRES_USER=admin
POSTGRES_PASSWORD=strong_password_here
TZ=Asia/Shanghai

vim docker-compose.yml

services:
  postgres:
    image: postgres:17-alpine
    container_name: postgres
    restart: unless-stopped

    # environment:
    #   TZ: ${TZ}
    #   POSTGRES_DB: ${POSTGRES_DB}
    #   POSTGRES_USER: ${POSTGRES_USER}
    #   POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}

    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

    networks:
      - pg-17-net

networks:
  pg-17-net:
    external: true

创建业务账户

docker exec -it pg17 psql -U admin -d admin_db

例如：

CREATE USER wanx_user WITH PASSWORD 'wanx123123';

CREATE DATABASE wanx3 OWNER wanx_user;

\connect wanx3

-- 把 public schema 的 owner 改成 wanx_user
ALTER SCHEMA public OWNER TO wanx_user;
-- 业务表默认创建在 public schema 下，所以应用用户必须能在 public 里建表。
GRANT ALL ON SCHEMA public TO wanx_user;

-- 删除
DROP USER wanx_user;

-- 查询目前所有 user
SELECT rolname AS 用户名, rolsuper, rolcreatedb, rolcanlogin
FROM pg_roles
WHERE rolcanlogin = true; -- 只查可登录的普通用户

隧道

Host cc-pg
    HostName q123q.cc
    User qin
    IdentityFile ~/.ssh/cc_ed25519
    IdentitiesOnly yes
    LocalForward 15432 127.0.0.1:5432

ssh -N cc-pg

原始命令

ssh -N -L 15432:127.0.0.1:5432 cc

init 脚本创建

也可选择初始化脚本方案，但是初始化仅仅在 init 时生效。

/opt/pg17/init/001-init.sql

volumes:
  - ./init:/docker-entrypoint-initdb.d

定时备份

cd /opt/pg17
mkdir -p scripts logs
vim /opt/pg17/scripts/backup.sh

#!/bin/bash

set -e

PG_CONTAINER="pg17"
PG_USER="admin"
PG_DATABASE="admin_db"

BASE_DIR="/opt/pg17"
BACKUP_DIR="${BASE_DIR}/backup"

DATE=$(date +%F_%H-%M-%S)

mkdir -p "$BACKUP_DIR"

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

docker exec "$PG_CONTAINER" \
  pg_dump \
  -U "$PG_USER" \
  -d "$PG_DATABASE" \
  -F c \
  -f "/backup/${PG_DATABASE}_${DATE}.dump"

echo "[$(date '+%F %T')] backup success: ${BACKUP_DIR}/${PG_DATABASE}_${DATE}.dump"

# 删除 14 天前的备份
find "$BACKUP_DIR" \
  -name "${PG_DATABASE}_*.dump" \
  -mtime +14 \
  -delete

注意备份是直接写入的 backup 目录

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

crontab -e

0 2 * * * /opt/pg17/scripts/backup.sh >> /opt/pg17/logs/backup-$(date +\%F).log 2>&1

logrotate