PostgreSQL 17 在 openEuler 2403 上的备份与恢复：pg_dump 与 pg_restore 用法

1. 核心概念

• 备份：将数据库结构和数据导出为可移植文件
  • 逻辑备份：通过 SQL 语句重建数据库（pg_dump）
  • 物理备份：直接复制数据文件（本文不涉及）
• 恢复：将备份文件还原到数据库（pg_restore）

2. 准备工作

1. 安装 PostgreSQL 17（openEuler 2403）：

   sudo dnf install postgresql17-server
   sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
   sudo systemctl start postgresql-17
   

2. 创建测试数据库：

   CREATE DATABASE testdb;
   \c testdb
   CREATE TABLE employees (id SERIAL PRIMARY KEY, name VARCHAR(50));
   

3. pg_dump 备份操作

基本语法：

pg_dump [选项] 数据库名 > 备份文件.sql

常用选项：

选项	说明
-Fc	生成自定义压缩格式（推荐）
-U username	指定连接用户
-h host	数据库主机地址
-p port	连接端口（默认 5432）

备份示例：

# 备份整个数据库（SQL 文本格式）
pg_dump -U postgres testdb > testdb_backup.sql

# 压缩格式备份（高效存储）
pg_dump -Fc -U postgres testdb > testdb_backup.dump

4. pg_restore 恢复操作

基本语法：

pg_restore [选项] -d 目标数据库 备份文件.dump

关键选项：

选项	说明
-c	恢复前清除现有对象
-C	自动创建目标数据库
-j NUM	使用多线程恢复（加速大库还原）

恢复示例：

# 恢复到新数据库（自动建库）
pg_restore -C -U postgres -d postgres testdb_backup.dump

# 覆盖现有数据库（清除旧数据）
pg_restore -c -U postgres -d testdb testdb_backup.dump

5. 高级场景

1. 并行备份加速：

pg_dump -Fd -j 4 -U postgres testdb -f /backup/testdb_dir

-Fd 生成目录格式，-j 4 启用 4 个线程

2. 增量备份策略：

# 每日全量备份
pg_dump -Fc testdb > full_$(date +%F).dump

# 配合 WAL 归档实现时间点恢复
vim ${PGDATA}/postgresql.conf

配置项：

archive_mode = on
archive_command = 'cp %p /wal_archive/%f'

6. 故障排查

1. 权限问题：

   chown postgres:postgres /backup/*
   

2. 版本兼容性：
   • pg_dump 17 可恢复至 ≥12 的版本
   • 低版本备份需升级后才能恢复至高版本
3. 连接失败： 检查 pg_hba.conf 文件：

   host    all     all     0.0.0.0/0     md5
   

7. 最佳实践

• 定时备份：通过 cron 每日自动执行

  0 2 * * * pg_dump -Fc -U postgres testdb > /backup/daily_$(date +\%F).dump
  

• 验证备份：定期执行测试恢复
• 存储分离：备份文件保存至独立存储设备
• 监控指标：关注备份文件大小和时长变化

重要提示：生产环境建议结合 WAL 归档实现 PITR（时间点恢复），最小化数据丢失风险。备份文件应加密存储，敏感数据需符合 GDPR/HIPAA 规范。