PostgreSQL 日常运维与备份策略总结

2025-09-12 · Albert

PostgreSQL 是我在个人项目中最常用的数据库。虽然个人项目数据量不大，但养成良好的运维和备份习惯很重要。本文记录一些日常用到的操作和踩过的坑。

Docker 部署 PostgreSQL

用 Docker 跑 PostgreSQL 非常方便，关键是把数据目录映射出来：

services:
  db:
    image: postgres:15-alpine
    container_name: my-db
    restart: unless-stopped
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_DB: myapp
    volumes:
      - ./data:/var/lib/postgresql/data
    ports:
      - "127.0.0.1:5432:5432"

注意端口只绑定到 127.0.0.1，避免数据库暴露到公网。

常用运维命令

# 进入容器的 psql
docker exec -it my-db psql -U myuser -d myapp

# 查看数据库大小
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;

# 查看活跃连接
SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity WHERE state = 'active';

# 查看表大小排名
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

备份策略

对于个人项目，我采用两层备份：

第一层：pg_dump 逻辑备份

#!/bin/bash
# /opt/scripts/backup-db.sh
BACKUP_DIR="/backup/postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR

docker exec my-db pg_dump -U myuser myapp \
  | gzip > "$BACKUP_DIR/myapp_$TIMESTAMP.sql.gz"

# 保留最近 30 天
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

第二层：data 目录快照

在云服务器上，配合云盘快照做物理备份，可以快速恢复整个数据库实例。

恢复

# 从 pg_dump 备份恢复
gunzip -c myapp_20251201_030000.sql.gz \
  | docker exec -i my-db psql -U myuser -d myapp

踩过的坑

• 忘记映射 data 目录：容器重建后数据全丢，血泪教训
• 端口暴露到 0.0.0.0：被扫描器发现，差点被脱库
• 备份没定期验证：备份了三个月才发现脚本权限问题，备份文件全是空的

经验：备份不验证等于没备份。每月至少做一次恢复测试。

总结

PostgreSQL 运维不复杂，但要形成习惯：端口不外露、数据目录映射出来、定时备份加定期验证。做好这三点，个人项目基本不会有数据风险。