leaudit-platform-frontend/docs/postgresql-backup-guide.md

# PostgreSQL 数据库备份指南

## 目录
- [方法一：使用 pg_dump（推荐）](#方法一使用-pg_dump推荐)
- [方法二：使用 pgAdmin 图形界面](#方法二使用-pgadmin-图形界面)
- [方法三：使用 SQL COPY 命令](#方法三使用-sql-copy-命令)
- [方法四：Docker 容器数据库备份](#方法四docker-容器数据库备份)
- [方法五：定时自动备份](#方法五定时自动备份)
- [数据恢复](#数据恢复)
- [最佳实践](#最佳实践)

---

## 方法一：使用 pg_dump（推荐）

### 1.1 备份整个数据库

**命令格式：**
```bash
pg_dump -h <host> -p <port> -U <username> -d <database_name> -F c -b -v -f <backup_file.dump>
```

**参数说明：**
- `-h` : 数据库主机地址（如 `localhost` 或 `10.79.97.17`）
- `-p` : 端口号（默认 5432）
- `-U` : 数据库用户名
- `-d` : 数据库名称
- `-F c` : 导出格式为自定义格式（custom format），支持压缩
- `-b` : 包含大对象（blobs）
- `-v` : 详细模式，显示进度
- `-f` : 输出文件路径

**实际示例：**
```bash
# 备份整个数据库（自定义格式，推荐）
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview -F c -b -v -f backup_docreview_$(date +%Y%m%d_%H%M%S).dump

# 备份为纯文本 SQL 格式（可读性好）
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview -F p -f backup_docreview_$(date +%Y%m%d_%H%M%S).sql

# 备份并压缩为 gzip 格式
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview | gzip > backup_docreview_$(date +%Y%m%d_%H%M%S).sql.gz
```

**Windows 示例：**
```cmd
# PowerShell
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview -F c -f "D:\backups\docreview_backup.dump"

# CMD（使用环境变量避免输密码）
set PGPASSWORD=your_password
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview -F c -f "D:\backups\docreview_backup.dump"
```

---

### 1.2 备份指定表

**单个表：**
```bash
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview -t documents -F c -f documents_backup.dump
```

**多个表：**
```bash
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview \
  -t documents \
  -t sso_users \
  -t evaluation_results \
  -F c -f selected_tables_backup.dump
```

**使用通配符匹配表名：**
```bash
# 备份所有以 "cross_" 开头的表
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview -t 'cross_*' -F c -f cross_tables_backup.dump
```

---

### 1.3 备份表结构（不包含数据）

```bash
# 仅备份表结构
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview --schema-only -f schema_only.sql

# 仅备份指定表的结构
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview -t documents --schema-only -f documents_schema.sql
```

---

### 1.4 仅备份数据（不包含结构）

```bash
# 仅备份数据
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview --data-only -f data_only.sql

# 仅备份指定表的数据
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview -t documents --data-only -f documents_data.sql
```

---

### 1.5 排除特定表

```bash
# 排除测试数据表
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview \
  --exclude-table=test_* \
  --exclude-table=temp_* \
  -F c -f backup_without_test_tables.dump
```

---

## 方法二：使用 pgAdmin 图形界面

### 2.1 备份数据库

1. **打开 pgAdmin**
   - 连接到 PostgreSQL 服务器

2. **选择数据库**
   - 在左侧树形结构中找到要备份的数据库
   - 右键点击数据库名称 → 选择 **"Backup..."**

3. **配置备份选项**
   ```
   General 选项卡：
   - Filename: 选择保存路径和文件名（如 D:\backups\docreview_20250117.backup）
   - Format:
     - Custom: 自定义格式（推荐，支持压缩和选择性恢复）
     - Tar: Tar 归档格式
     - Plain: 纯 SQL 文本
     - Directory: 目录格式

   Data/Objects 选项卡：
   - Type of Objects: 选择要备份的对象类型
     - Schema only: 仅结构
     - Data only: 仅数据
     - Schema and data: 结构和数据（默认）

   Options 选项卡：
   - ✓ Blobs: 包含大对象
   - ✓ Use INSERT commands: 使用 INSERT 语句（可读性好）
   - ✓ Include DROP DATABASE statement: 包含删除数据库语句（谨慎使用）
   ```

4. **执行备份**
   - 点击 **"Backup"** 按钮
   - 等待进度条完成

### 2.2 备份单个表

1. **展开数据库 → Schemas → public → Tables**
2. **右键点击表名 → "Backup..."**
3. **配置备份选项**（同上）
4. **点击 "Backup"**

---

## 方法三：使用 SQL COPY 命令

### 3.1 导出为 CSV 格式

**连接到数据库：**
```bash
psql -h 10.79.97.17 -p 5432 -U postgres -d docreview
```

**导出单个表：**
```sql
-- 导出所有列
COPY documents TO 'D:/backups/documents.csv' WITH CSV HEADER;

-- 导出指定列
COPY (SELECT id, name, type_id, audit_status FROM documents)
TO 'D:/backups/documents_selected.csv' WITH CSV HEADER;

-- 导出查询结果
COPY (
  SELECT d.*, t.name as type_name
  FROM documents d
  LEFT JOIN document_types t ON d.type_id = t.id
  WHERE d.audit_status = 1
) TO 'D:/backups/documents_passed.csv' WITH CSV HEADER;
```

**导出为其他分隔符：**
```sql
-- 使用制表符分隔
COPY documents TO 'D:/backups/documents.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER);

-- 使用自定义分隔符
COPY documents TO 'D:/backups/documents.txt' WITH (FORMAT csv, DELIMITER '|', HEADER);
```

### 3.2 使用 \copy 命令（客户端执行）

**优势：** 不需要服务器端文件系统权限

```bash
# 在 psql 命令行中执行
\copy documents TO 'D:/backups/documents.csv' WITH CSV HEADER

# 导出查询结果
\copy (SELECT * FROM documents WHERE created_at > '2025-01-01') TO 'D:/backups/recent_documents.csv' WITH CSV HEADER
```

---

## 方法四：Docker 容器数据库备份

### 4.1 备份 Docker 容器中的 PostgreSQL

**查看容器名称：**
```bash
docker ps
# 找到 PostgreSQL 容器名称，如 postgres-container
```

**备份整个数据库：**
```bash
# 使用 docker exec 执行 pg_dump
docker exec -t postgres-container pg_dump -U postgres -d docreview -F c > backup_$(date +%Y%m%d).dump

# 或者指定完整路径
docker exec -t postgres-container pg_dump -U postgres -d docreview -F c -f /tmp/backup.dump

# 然后从容器复制到宿主机
docker cp postgres-container:/tmp/backup.dump ./backup_docreview.dump
```

**备份所有数据库：**
```bash
docker exec -t postgres-container pg_dumpall -U postgres > all_databases_backup.sql
```

### 4.2 备份 Docker Volume

```bash
# 查看 PostgreSQL 数据卷
docker volume ls

# 备份整个数据卷
docker run --rm \
  -v postgres_data:/data \
  -v $(pwd):/backup \
  ubuntu tar cvf /backup/postgres_data_backup.tar /data
```

---

## 方法五：定时自动备份

### 5.1 Linux Cron Job

**创建备份脚本：**
```bash
nano /usr/local/bin/backup_postgres.sh
```

**脚本内容：**
```bash
#!/bin/bash

# 配置
DB_HOST="10.79.97.17"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME="docreview"
BACKUP_DIR="/backups/postgresql"
RETENTION_DAYS=7  # 保留7天

# 创建备份目录
mkdir -p $BACKUP_DIR

# 设置密码（不推荐，建议使用 .pgpass 文件）
export PGPASSWORD="your_password"

# 生成备份文件名（带时间戳）
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d_%H%M%S).dump"

# 执行备份
pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -F c -b -v -f $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
  echo "✅ 备份成功: $BACKUP_FILE"

  # 删除过期备份（7天前）
  find $BACKUP_DIR -name "${DB_NAME}_*.dump" -type f -mtime +$RETENTION_DAYS -delete
  echo "🗑️ 已删除 $RETENTION_DAYS 天前的旧备份"
else
  echo "❌ 备份失败"
  exit 1
fi

# 清除密码环境变量
unset PGPASSWORD
```

**添加执行权限：**
```bash
chmod +x /usr/local/bin/backup_postgres.sh
```

**配置 Cron 定时任务：**
```bash
crontab -e
```

**添加定时规则：**
```cron
# 每天凌晨 2 点执行备份
0 2 * * * /usr/local/bin/backup_postgres.sh >> /var/log/postgres_backup.log 2>&1

# 每 6 小时备份一次
0 */6 * * * /usr/local/bin/backup_postgres.sh >> /var/log/postgres_backup.log 2>&1

# 每周日凌晨 3 点执行完整备份
0 3 * * 0 /usr/local/bin/backup_postgres.sh >> /var/log/postgres_backup.log 2>&1
```

### 5.2 Windows 计划任务

**创建 PowerShell 脚本：**
```powershell
# backup_postgres.ps1

$DB_HOST = "10.79.97.17"
$DB_PORT = "5432"
$DB_USER = "postgres"
$DB_NAME = "docreview"
$BACKUP_DIR = "D:\backups\postgresql"
$RETENTION_DAYS = 7

# 创建备份目录
if (!(Test-Path $BACKUP_DIR)) {
    New-Item -ItemType Directory -Path $BACKUP_DIR
}

# 设置密码环境变量
$env:PGPASSWORD = "your_password"

# 生成备份文件名
$TIMESTAMP = Get-Date -Format "yyyyMMdd_HHmmss"
$BACKUP_FILE = "$BACKUP_DIR\${DB_NAME}_$TIMESTAMP.dump"

# 执行备份
& "C:\Program Files\PostgreSQL\16\bin\pg_dump.exe" -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -F c -b -v -f $BACKUP_FILE

if ($LASTEXITCODE -eq 0) {
    Write-Host "✅ 备份成功: $BACKUP_FILE"

    # 删除旧备份
    Get-ChildItem $BACKUP_DIR -Filter "${DB_NAME}_*.dump" |
        Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$RETENTION_DAYS) } |
        Remove-Item -Force

    Write-Host "🗑️ 已删除 $RETENTION_DAYS 天前的旧备份"
} else {
    Write-Host "❌ 备份失败"
    exit 1
}

# 清除密码
Remove-Item Env:\PGPASSWORD
```

**配置 Windows 计划任务：**
1. 打开 **任务计划程序**
2. 创建基本任务 → 命名为 "PostgreSQL 定时备份"
3. 触发器：选择 **每天** → 设置时间（如 02:00）
4. 操作：**启动程序**
   - 程序或脚本：`powershell.exe`
   - 参数：`-ExecutionPolicy Bypass -File "D:\scripts\backup_postgres.ps1"`
5. 完成创建

---

## 数据恢复

### 6.1 恢复自定义格式备份（.dump）

**恢复到新数据库：**
```bash
# 1. 创建新数据库
createdb -h 10.79.97.17 -p 5432 -U postgres docreview_restored

# 2. 恢复数据
pg_restore -h 10.79.97.17 -p 5432 -U postgres -d docreview_restored -v backup_docreview.dump
```

**恢复到现有数据库（覆盖）：**
```bash
# ⚠️ 警告：会删除现有数据
pg_restore -h 10.79.97.17 -p 5432 -U postgres -d docreview --clean -v backup_docreview.dump
```

**选择性恢复（仅恢复指定表）：**
```bash
# 查看备份文件内容
pg_restore -l backup_docreview.dump

# 仅恢复 documents 表
pg_restore -h 10.79.97.17 -p 5432 -U postgres -d docreview -t documents -v backup_docreview.dump
```

### 6.2 恢复 SQL 格式备份（.sql）

```bash
# 方法1：使用 psql
psql -h 10.79.97.17 -p 5432 -U postgres -d docreview < backup_docreview.sql

# 方法2：使用 psql 的 \i 命令
psql -h 10.79.97.17 -p 5432 -U postgres -d docreview
\i backup_docreview.sql

# 恢复压缩的备份
gunzip -c backup_docreview.sql.gz | psql -h 10.79.97.17 -p 5432 -U postgres -d docreview
```

### 6.3 恢复 CSV 数据

```sql
-- 连接到数据库
psql -h 10.79.97.17 -p 5432 -U postgres -d docreview

-- 先清空表（可选）
TRUNCATE documents;

-- 导入 CSV 数据
COPY documents FROM 'D:/backups/documents.csv' WITH CSV HEADER;

-- 或使用 \copy（客户端执行）
\copy documents FROM 'D:/backups/documents.csv' WITH CSV HEADER
```

---

## 最佳实践

### 1. 安全建议

**使用 .pgpass 文件存储密码（推荐）：**

**Linux/Mac（~/.pgpass）：**
```
10.79.97.17:5432:docreview:postgres:your_password
```

**Windows（%APPDATA%\postgresql\pgpass.conf）：**
```
10.79.97.17:5432:docreview:postgres:your_password
```

**设置权限：**
```bash
# Linux/Mac
chmod 600 ~/.pgpass

# Windows（PowerShell）
icacls %APPDATA%\postgresql\pgpass.conf /inheritance:r /grant:r "%USERNAME%:F"
```

### 2. 备份策略

**3-2-1 原则：**
- **3** 份副本：原始数据 + 2 份备份
- **2** 种介质：本地硬盘 + 云存储/NAS
- **1** 份异地：防止物理灾难

**备份频率：**
```
- 关键业务数据：每小时增量 + 每天全量
- 一般业务数据：每天全量
- 测试环境：每周全量
```

### 3. 备份验证

**定期验证备份可用性：**
```bash
# 1. 创建测试数据库
createdb -h localhost -U postgres test_restore

# 2. 恢复备份到测试库
pg_restore -h localhost -U postgres -d test_restore backup.dump

# 3. 验证数据完整性
psql -h localhost -U postgres -d test_restore -c "SELECT count(*) FROM documents;"

# 4. 删除测试库
dropdb -h localhost -U postgres test_restore
```

### 4. 性能优化

**大数据库备份优化：**
```bash
# 使用并行备份（PostgreSQL 9.3+）
pg_dump -h 10.79.97.17 -U postgres -d docreview -F d -j 4 -f backup_dir/

# 参数说明：
# -F d : 目录格式
# -j 4 : 使用 4 个并行工作进程
```

**恢复性能优化：**
```bash
# 并行恢复
pg_restore -h 10.79.97.17 -U postgres -d docreview -j 4 -v backup_dir/

# 禁用触发器和约束（恢复完再启用）
pg_restore --disable-triggers --no-owner --no-acl -d docreview backup.dump
```

### 5. 监控与日志

**记录备份日志：**
```bash
pg_dump -h 10.79.97.17 -U postgres -d docreview -F c -f backup.dump \
  2>&1 | tee backup_$(date +%Y%m%d).log
```

**检查备份文件完整性：**
```bash
# 验证 dump 文件
pg_restore -l backup.dump > /dev/null && echo "✅ 备份文件有效" || echo "❌ 备份文件损坏"

# 检查文件大小
ls -lh backup.dump

# 计算校验和
md5sum backup.dump > backup.dump.md5
```

---

## 项目特定备份示例

### 针对本项目（docreview）的备份命令

**完整备份：**
```bash
# 备份整个数据库
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview \
  -F c -b -v \
  -f "docreview_full_$(date +%Y%m%d_%H%M%S).dump"
```

**核心表备份：**
```bash
# 备份核心业务表
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview \
  -t documents \
  -t sso_users \
  -t evaluation_results \
  -t evaluation_points \
  -t cross_examination_tasks \
  -t cross_scoring_proposals \
  -F c -f "docreview_core_tables_$(date +%Y%m%d).dump"
```

**排除临时表：**
```bash
# 排除测试和临时数据
pg_dump -h 10.79.97.17 -p 5432 -U postgres -d docreview \
  --exclude-table='test_*' \
  --exclude-table='temp_*' \
  --exclude-table='_*' \
  -F c -f "docreview_production_$(date +%Y%m%d).dump"
```

---

## 故障排查

### 常见错误

**1. 权限错误：**
```
ERROR: permission denied for table xxx
```
**解决方案：**
```bash
# 使用具有足够权限的用户
pg_dump -h 10.79.97.17 -U postgres -d docreview ...
```

**2. 连接超时：**
```
could not connect to server: Connection timed out
```
**解决方案：**
```bash
# 检查防火墙
# 检查 PostgreSQL 监听地址（postgresql.conf）
# 检查 pg_hba.conf 访问控制
```

**3. 磁盘空间不足：**
```
ERROR: could not write to file: No space left on device
```
**解决方案：**
```bash
# 检查磁盘空间
df -h

# 使用压缩格式
pg_dump ... | gzip > backup.sql.gz
```

---

## 总结

**推荐方案：**

| 场景 | 推荐方法 | 命令示例 |
|------|---------|---------|
| 日常全量备份 | `pg_dump -F c` | `pg_dump -h host -U user -d db -F c -f backup.dump` |
| 快速查看数据 | CSV 导出 | `COPY table TO 'file.csv' WITH CSV HEADER` |
| 大型数据库 | 并行备份 | `pg_dump -F d -j 4 -f backup_dir/` |
| 定时自动备份 | Cron + 脚本 | 见方法五 |
| 数据迁移 | `pg_dump` + `pg_restore` | 见数据恢复章节 |

**记住：**
- ✅ 定期测试恢复流程
- ✅ 异地备份防止数据丢失
- ✅ 保留多个版本的备份
- ✅ 记录备份和恢复操作日志
- ⚠️ 保护备份文件安全，避免泄露敏感数据

---

**文档版本：** 1.0
**最后更新：** 2025-01-17
**适用版本：** PostgreSQL 12+