PostgreSQL 数据库导入导出实操

PostgreSQL 数据库导入导出完全指南

PostgreSQL 作为一款功能强大的开源关系型数据库，提供了多种数据导入导出的方法。本文将全面介绍常用的 pg_dump 和 pg_restore 工具的使用技巧。

一、数据库导出

1. 使用 pg_dump 导出数据库

pg_dump 是 PostgreSQL 自带的逻辑备份工具，可以导出整个数据库或特定对象。

基本语法

pg_dump -h 主机名 -p 端口 -U 用户名 -d 数据库名 -f 输出文件

常用示例

导出为 SQL 文本格式：

pg_dump -h localhost -p 5432 -U postgres -d mydb -f mydb.sql

导出为自定义二进制格式（适合 pg_restore）：

pg_dump -h localhost -U postgres -F c -f mydb.dump mydb

只导出数据结构（不含数据）：

pg_dump -h localhost -U postgres --schema-only -f schema.sql mydb

只导出数据（不包含 DDL）：

pg_dump -h localhost -U postgres --data-only -f data.sql mydb

2. 高级导出选项

选项	说明
-F c	自定义二进制格式
-F p	纯文本 SQL 格式（默认）
-j N	使用 N 个并行任务
-Z 0-9	压缩级别
--exclude-table=TABLE	排除特定表
-t TABLE	只导出特定表

二、数据库导入

1. 使用 psql 导入 SQL 文件

适用于由 pg_dump 导出的 SQL 文本文件：

psql -h 主机名 -p 端口 -U 用户名 -d 数据库名 -f 输入文件

示例：

psql -h localhost -p 5432 -U postgres -d newdb -f mydb.sql

2. 使用 pg_restore 导入二进制文件

适用于由 pg_dump -F c 导出的二进制文件：

pg_restore -h 主机名 -p 端口 -U 用户名 -d 数据库名 -F c 输入文件

示例：

pg_restore -h localhost -p 5432 -U postgres -d newdb -F c mydb.dump

3. 高级导入选项

选项	说明
-j N	使用 N 个并行任务恢复
--clean	在恢复前删除已有对象
--create	先创建数据库
--schema-only	只恢复结构
--data-only	只恢复数据
--no-owner	不设置对象所有权

三、实用场景示例

1. 数据库迁移

# 源服务器导出
pg_dump -h src_host -U src_user -F c -f mydb.dump mydb

# 目标服务器导入
pg_restore -h dst_host -U dst_user -d mydb -F c --clean --no-owner mydb.dump

2. 跨版本迁移

# 使用兼容模式导出
pg_dump -h old_host -U postgres -F p --no-comments -f olddb.sql olddb

# 导入到新版本
psql -h new_host -U postgres -d newdb -f olddb.sql

3. 大数据库并行处理

# 并行导出
pg_dump -h localhost -U postgres -F d -j 4 -f mydb_dir mydb

# 并行导入
pg_restore -h localhost -U postgres -d mydb -F d -j 4 mydb_dir

四、常见问题解决

1. 权限问题

# 创建用户并授权
psql -h localhost -U postgres -c "CREATE USER myuser WITH PASSWORD 'mypass';"
psql -h localhost -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;"

2. 连接问题

检查 pg_hba.conf 配置：

# 允许所有IP连接
host    all             all             0.0.0.0/0               md5

重载配置：

sudo systemctl reload postgresql

3. 版本兼容性

高版本导出的数据可能无法导入到低版本。建议：

1. 使用相同版本的 pg_dump 和 pg_restore
2. 导出为 SQL 格式时添加 --no-comments 选项

五、最佳实践

1. 定期备份：设置 cron 任务自动备份
2. 验证备份：定期测试恢复流程
3. 分离结构和数据：分别备份便于部分恢复
4. 记录元信息：备份时记录数据库版本、时间等信息
5. 安全存储：加密敏感数据备份

通过掌握这些导入导出技巧，可以轻松应对 PostgreSQL 数据库的迁移、备份和恢复需求。

备份常用命令

建议使用通个版本的pg_dump和pg_restore，不同版本可能会出错

# 备份
pg_dump -h localhost -U postgres -F c -C -f ./dify_plugin.dump dify_plugin
# 恢复
pg_restore -h 10.11.11.66 -p 5432 -U postgres -d dify_plugin -F c ./dify_plugin.dump