PostgreSQL 备份与恢复实战操作pg_dump / pg_restore 全方位指南

目录

• 一、pg_dhddzNump —— 逻辑备份工具
• ✅ 1. 基本语法
• 常用选项：
• ✅ 2. 四大备份格式详解
• ▶ 1. 纯文本格式（-Fp，默认）
• ▶ 2. 自定义格式（-Fc）
• ▶ 3. 目录格式（-Fd）
• ▶ 4. tar 格式（-Ft）
• ✅ 3. 实用备份示例
• 示例1：完整备份（推荐格式）
• 示例2：仅备份结构
• 示例3：仅备份数据
• 示例4：备份特定表
• 示例5：排除某些表
• 示例6：使用 INSERT 语句（便于部分编辑）
• ✅ 4. pg_dumpall —— 备份整个集群
• 二、pg_restore —— 逻辑恢复工具
• ✅ 1. 基本语法
• 常用选项：
• ✅ 2. 恢复实战示例
• 示例1：完整恢复（自定义格式）
• 示例2：选择性恢复（单表）
• 示例3：仅恢复数据（跳过结构）
• 示例4：清理后恢复（先 DROP 再 CREATE）
• 示例5：列出备份内容并选择性恢复
• 示例6：并行恢复（大幅提升速度）
• 三、备份恢复最佳实践
• ✅ 1. 自动化备份脚本（linux 示例）
• ✅ 2. 增量备份策略（WAL + 基础备份）
• ✅ 3. 备份验证与监控
• 验证备份完整性：
• 监控备份：
• ✅ 4. 常见问题与解决
• ❓ 1. 权限不足
• ❓ 2. 恢复时对象已存在
• ❓ 3. 编码问题
• ❓ 4. 大表备份中断
• 四、高级技巧
• ✅ 1. 压缩备份
• ✅ 2. 远程备份
• ✅ 3. 备份到云存储（AWS S3 示例）
• ✅ 4. 使用 .pgpass 文件免密
• 五、实践任务
• 备份策略推荐表

✅ PostgreSQL 备份与恢复详解 —— pg_dump / pg_restore 全方位指南

数据安全是数据库管理的核心。本篇将带你深入掌握 PostgreSQL 最核心的备份恢复工具 —— pg_dump 和 pg_restore，涵盖逻辑备份、物理备份、增量备份、自动化脚本等企业级实践。

一、备份类型总览

本篇重点：逻辑备份（pg_dump / pg_restore） —— 最常用、最灵活！

一、pg_dump —— 逻辑备份工具

✅ 1. 基本语法

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

常用选项：

✅ 2. 四大备份格式详解

▶ 1. 纯文本格式（-Fp，默认）

pg_dump -U postgres mydb > mydb.sql

• 优点：可读性强，可用 psql 恢复，支持跨版本
• 缺点：恢复慢，不支持选择性恢复

▶ 2. 自定义格式（-Fc）

pg_dump -U postgres -Fc mydb > mydb.dump

• 优点：压缩率高，支持并行恢复，支持选择性恢复（pg_restore）
• 缺点：只能用 pg_restore 恢复

▶ 3. 目录格式（-Fd）

pg_dump -U postgres -Fd -f mydb_backup_dir mydb

• 优点：支持并行备份/恢复，每个表独立文件，便于管理
• 缺点：占用目录空间

▶ 4. tar 格式（-Ft）

pg_dump -U postgres -Ft mydb > mydb.tar

• 优点：兼容 tar 工具
• 缺点：不支持压缩，不支持并行

生产环境推荐：-Fc 或 -Fd（支持并行和选择性恢复）

✅ 3. 实用备份示例

示例1：完整备份（推荐格式）

# 自定义格式（压缩+并行恢复）
pg_dump -U postgres -h localhost -p 5432 -Fc -f /backup/mydbhttp://www.devze.com_$(date +%Y%m%d).dump mydb
# 目录格式（并行备份python）
pg_dump -U postgres -Fd -j 4 -f /backup/mydb_dir mydb

示例2：仅备份结构

pg_dump -U postgres --schema-only -f mydb_schema.sql mydb

示例3：仅备份数据

pg_dump -U postgres --data-only -f mydb_data.sql mydb

示例4：备份特定表

pg_dump -U postgres -t users -t orders -f mydb_tables.dump mydb

示例5：排除某些表

pg_dump -U postgres --exclude-table=log_* -f mydb_no_logs.dump mydb

示例6：使用 INSERT 语句（便于部分编辑）

pg_dump -U postgres --inserts --column-inserts -f mydb_inserts.sql mydb

✅ 4. pg_dumpall —— 备份整个集群

# 备份所有数据库 + 全局对象（角色、表空间等）
pg_dumpall -U postgres -f cluster_backup.sql
# 只备份全局对象
pg_dumpall -U postgres --globals-only -f globals.sql
# 只备份某个数据库（等同 pg_dump）
pg_dumpall -U postgres -l mydb -f mydb.sql

⚠️ pg_dumpall 只能输出纯文本格式（-Fp）

二、pg_restore —— 逻辑恢复工具

✅ 1. 基本语法

pg_restore [选项] [备份文件]

常用选项：

✅ 2. 恢复实战示例

示例1：完整恢复（自定义格式）

# 方法1：直接恢复到现有数据库
pg_restore -U postgres -d mydb -j 4 mydb.dump
# 方法2：创建新数据库并恢复
pg_restore -U postgres -C -d postgres -j 4 mydb.dump
# -C 会创建数据库，-d postgres 表示连接到 postgres 数据库执行创建

示例2：选择性恢复（单表）

pg_restore -U postgres -d mydb -t users mydb.dupythonmp

示例3：仅恢复数据（跳过结构）

pg_restore -U postgres -d mydb --data-only mydb.dump

示例4：清理后恢复（先 DROP 再 CREATE）

pg_restore -U postgres -d mydb -c mydb.dump

示例5：列出备份内容并选择性恢复

# 生成内容列表
pg_restore -l mydb.dump > mydb.list
# 编辑 mydb.list，删除不需要的行（如注释掉某些表）
# 只恢复 users 表：
# ;2345; 0 12345 TABLE users postgres
# 按列表恢复
pg_restore -L mydb.list -d mydb mydb.dump

示例6：并行恢复（大幅提升速度）

pg_restore -U postgres -d mydb -j 8 -Fd mydb_backup_dir

并行恢复要求：

• 备份格式为 -Fc 或 -Fd
• PostgreSQL 9.3+

三、备份恢复最佳实践

✅ 1. 自动化备份脚本（Linux 示例）

#!/bin/bash
# backup_postgres.sh
BACKUP_DIR="/backup/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
USER="postgres"
# 创建备份目录
mkdir -p $BACKUP_DIR
# 执行备份
pg_dump -U $USER -Fc -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump $DB_NAME
# 删除7天前的备份
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete
# 记录日志
echo "[$(date)] 备份完成: ${DB_NAME}_${DATE}.dump" >> /var/log/pg_backup.log

添加到 crontab（每天凌晨2点）：

0 2 * * * /path/to/backup_postgres.sh

✅ 2. 增量备份策略（WAL + 基础备份）

# 1. 启用 WAL 归档（postgresql.conf）
wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'
# 2. 定期基础备份
pg_basebackup -D /backup/base -Ft -z -P
# 3. 恢复时先恢复基础备份，再应用 WAL 日志

本篇重点逻辑备份，物理备份另文详解。

✅ 3. 备份验证与监控

验证备份完整性：

# 检查自定义格式备份
pg_restore -l mydb.dump > /dev/null && echo "备份有效"
# 恢复到测试数据库验证
createdb test_restore
pg_restore -d test_restore mydb.dump
psql -d test_restore -c "SELECT COUNT(*) FROM users;"
dropdb test_restore

监控备份：

# 检查备份文件大小
ls -lh /backup/*.dump
# 检查备份日志
tail -f /var/log/pg_backup.log

✅ 4. 常见问题与解决

❓ 1. 权限不足

# 确保用户有 CONNECT 和 SELECT 权限
GRANT CONNECT ON DATABASE mydb TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

❓ 2. 恢复时对象已存在

# 使用 -c 选项先清理
pg_restore -c -d mydb mydb.dump
# 或手动 DROP DATABASE
dropdb mydb
createdb mydb
pg_restore -d mydb mydb.dump

❓ 3. 编码问题

# 指定客户端编码
pg_dump -U postgres --encoding=UTF8 -f backup.sql mydb

❓ 4. 大表备份中断

# 使用目录格式 + 并行，支持断点续备
pg_dump -Fd -j 4 -f backup_dir mydb

四、高级技巧

✅ 1. 压缩备份

# pg_dump + gzip
pg_dump -U postgres mydb | gzip > mydb.sql.gz
# 恢复
gunzip -c mydb.sql.gz | psql -U postgres mydb
# 或使用 -Fc 格式（内置压缩）
pg_dump -Fc -f mydb.dump mydb  # 压缩率通常 70-90%

✅ 2. 远程备份

# 从远程服务器备份
pg_dump -h remote_host -U postgres -Fc mydb > mydb.dump
# 通过 SSH 备份
ssh user@remote "pg_dump -U postgres mydb" > mydb.sql

✅ 3. 备份到云存储（AWS S3 示例）

# 备份并上传到 S3
pg_dump -U postgres -Fc mydb | aws s3 cp - s3://mybucket/mydb.dump
# 从 S3 恢复
aws s3 cp s3://m编程客栈ybucket/mydb.dump - | pg_restore -U postgres -d mydb

✅ 4. 使用 .pgpass 文件免密

创建 ~/.pgpass 文件：

hostname:port:database:username:password
localhost:5432:mydb:postgres:mypassword

设置权限：

chmod 600 ~/.pgpass

五、实践任务

请完成以下操作：

1. 使用 pg_dump 备份你的数据库（自定义格式）
2. 创建一个新数据库 mydb_restore
3. 使用 pg_restore 恢复备份到新数据库
4. 尝试只恢复某一张表
5. 编写一个自动化备份脚本，每天执行并保留最近7天备份
6. 验证备份文件的完整性

备份策略推荐表

到此这篇关于PostgreSQL 备份与恢复实战操作pg_dump / pg_restore 全方位指南的文章就介绍到这了,更多相关PostgreSQL 备份与恢复内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！