上一篇
pg库如何导入导出数据库？详细步骤与命令解析
PostgreSQL（简称PG）是一款功能强大的开源对象关系型数据库管理系统，广泛应用于各种业务场景中，在实际运维和开发过程中，数据库的导入导出是常见操作，用于数据备份、迁移、环境同步等，本文将详细介绍PG库导入导出数据库的多种方法、操作步骤及注意事项。
数据导出方法
使用pg_dump工具导出
pg_dump是PG提供的逻辑备份工具,可将数据库导出为SQL脚本文件、自定义格式文件或目录格式文件，适合中小型数据库的备份。
基本语法：
pg_dump [选项] [数据库名称] > [输出文件]
常用选项：
- F c：导出为自定义格式（二进制，需用pg_restore恢复）。
- F d：导出为目录格式（包含多个文件，适合大型数据库）。
- f：指定输出文件路径。
- U：指定用户名。
- h：指定主机地址。
- p：指定端口号。
- v：显示详细过程。
示例：
- 导出单个数据库为SQL脚本： pg_dump U postgres h localhost p 5432 mydb > mydb_backup.sql
- 导出为自定义格式： pg_dump U postgres F c f mydb_backup.custom mydb
使用pg_dumpall导出所有数据库
若需导出整个PG集群的所有数据库,可使用pg_dumpall工具，它会导出所有数据库的全局对象（如角色、表空间）和各数据库数据。
示例：
使用COPY命令导出表数据
COPY命令可导出单表数据为文本文件（如CSV），适合数据迁移或分析。
语法：
COPY (SELECT * FROM 表名) TO '/path/to/output.csv' WITH CSV HEADER;
示例：
COPY users TO '/tmp/users.csv' WITH CSV HEADER DELIMITER ',';
数据导入方法
使用psql工具导入SQL脚本
psql是PG的交互式客户端工具,可直接执行SQL脚本文件。
语法：
示例：
psql U postgres d mydb f mydb_backup.sql
使用pg_restore导入自定义格式或目录格式文件
pg_restore用于恢复pg_dump导出的自定义格式（F c）或目录格式（F d）文件，支持选择性导入（如表、索引等）。
语法：
pg_restore [选项] [输入文件]
常用选项：
- d：指定目标数据库。
- U：指定用户名。
- v：显示详细过程。
- t：指定导入的表名（可多次使用）。
- j：并行导入任务数。
示例：
- 导出自定义格式文件： pg_restore U postgres d mydb v mydb_backup.custom
- 选择性导入特定表： pg_restore U postgres d mydb t users t orders mydb_backup.custom
使用COPY命令导入数据
COPY命令也可从文本文件（如CSV）导入数据到表，需确保表结构与文件列一致。
语法：
COPY 表名 FROM '/path/to/input.csv' WITH CSV HEADER;
示例：
COPY users FROM '/tmp/users.csv' WITH CSV HEADER DELIMITER ',';
不同场景下的操作对比
| 场景 | 导出工具 | 导入工具 | 特点 |
|---|---|---|---|
| 全库备份（中小型） | pg_dump（SQL脚本） | psql | 可读性强，适合文本编辑；恢复时需执行全部SQL，速度较慢。 |
| 全库备份（大型） | pg_dump（自定义格式） | pg_restore | 支持并行恢复、选择性导入，压缩率高，适合大型数据库。 |
| 多数据库备份 | pg_dumpall | psql（逐库执行） | 包含全局对象，适合集群迁移；恢复时需按顺序执行脚本。 |
| 单表数据迁移 | COPY命令 | COPY命令 | 直接操作文件，适合结构化数据（如CSV），需手动处理表结构。 |
注意事项
- 权限要求：执行导入导出的用户需具备数据库的CONNECT、SELECT（导出）和INSERT/CREATE（导入）权限。
- 版本兼容性：高版本PG导出的数据文件可能无法在低版本中导入，建议保持版本一致。
- 事务与锁：大数据量导入时，建议在非业务高峰期操作，避免长时间锁表影响性能。
- 数据校验：导入完成后，可通过数据行数、哈希值等方式校验数据完整性。
- 环境配置：导入前需确保目标数据库已创建，且字符集、排序规则与源库一致。
相关问答FAQs
Q1：pg_dump导出的SQL文件如何在Windows系统中导入？
A1：在Windows中，可通过以下步骤导入：
- 安装PG客户端（如StackBuilder或独立版psql）。
- 打开命令行,执行命令： psql U postgres d mydb f mydb_backup.sql
若提示“psql命令不存在”，需将PG的bin目录添加到系统PATH环境变量中。
Q2：如何解决pg_restore导入时提示“权限不足”的问题？
A2：该问题通常是由于目标用户缺乏表创建或插入权限导致，解决方法包括：
- 使用超级用户（如postgres）执行导入： pg_restore U postgres d mydb mydb_backup.custom
- 若需限制权限,可在导入后通过REVOKE/GRANT命令调整用户权限。
- 确保目标数据库已存在,且用户具备CONNECT权限。
