fed notes

Appearance

postgresql 备份留存 ​

单机如何保证高可用

  1. 不要让业务使用超级用户 postgres
  2. 一定要有备份，并且验证备份能恢复
  3. 不要暴露 5432 到公网
  4. 升级前一定先备份
sql
CREATE DATABASE app;

CREATE USER app_user WITH PASSWORD 'xxx';

GRANT ALL PRIVILEGES ON DATABASE app TO app_user;

备份：

sh
docker exec postgres pg_dump -U app_user app > /mydata/postgres/backup/app_$(date +%F).sql

恢复：

sh
cat /mydata/postgres/backup/app_2026-06-26.sql | docker exec -i postgres psql -U app_user -d app

定时备份 ​

sh
mkdir -p /opt/pg17/scripts
vim /opt/pg17/scripts/backup.sh
bash
#!/bin/bash

set -e

BACKUP_DIR="/opt/pg17/backup"
DATE=$(date +%F_%H-%M-%S)

mkdir -p "$BACKUP_DIR"

docker exec postgres \
  pg_dump \
  -U app_user \
  -d app \
  -F c \
  -f "/backup/app_${DATE}.dump"

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

需要注意 /backup 目录

sh
services:
  postgres:
    image: postgres:17-alpine
    container_name: postgres

    volumes:
      - ./data:/var/lib/postgresql/data
      - ./backup:/backup
sh
chmod +x /opt/pg17/scripts/backup.sh

配置 cron

sh
crontab -e

0 2 * * * /mydata/postgres/scripts/backup.sh >> /mydata/postgres/backup.log 2>&1

恢复 ​

创建空数据库

sh
docker exec -it postgres psql -U postgres

需要确保有 database 和 user，如 app_user、app

开始恢复

sh
docker exec postgres \
  pg_restore \
  -U app_user \
  -d app \
  /backup/app_2026-07-02_02-00-00.dump

查看备份内容

sh
pg_restore -l app.dump

只恢复某一张表

sh
pg_restore \
  -t users \
  -d app \
  app.dump

异地备份 ​

rclone sync

ossutil cp

rsync 到另一台机器

备份思路 ​

小型项目：

pg_dump 每日备份即可

中型项目：

pg_dump + 压缩 + 定期演练恢复

较大项目：

pg_basebackup / 物理备份 + WAL 归档

高要求项目：

主从复制 + PITR 时间点恢复 + 异地备份

关于生产可靠 1 ​

🔹 ① 逻辑备份（防误操作） ​

  • 工具：pg_dump
  • 作用：
  • 防止 DROP TABLE / DELETE / 误更新
  • 跨版本恢复
sh
pg_dump -Fc mydb > mydb_$(date +%F).dump

🔹 ② 物理备份 + WAL（防硬件/系统崩溃） ​

  • 工具：
  • pg_basebackup
  • 或 pgBackRest（生产首选）
  • 作用：
    • PITR（时间点恢复）
    • 整库秒级恢复
conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

3️⃣ 自动清理与膨胀控制 ​

PG 没有自动回收空间（MVCC）：

sql
SHOW autovacuum;

配置

conf
autovacuum = on
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

4️⃣ 连接数控制（事故高发点） ​

sh
max_connections = 100   # 常见生产值

5️⃣ 权限最小化 ​

  • 禁止应用用 superuser
  • 禁止直接 DROP SCHEMA
  • 读写分离账号

6️⃣ 监控 ​

工具推荐：

  • pg_stat_statements
  • Prometheus + Grafana

至少监控：

  • 磁盘使用率
  • WAL 增长速度
  • 活跃连接数
  • 慢查询

开启 慢 SQL ​

sql
log_min_duration_statement = 500