PostgreSQL数据导入导出示例代码

在日常的数据库管理工作中，数据的导入和导出几乎是绕不开的基本操作。无论是备份迁移、跨平台同步，还是做数据分析，能顺手把数据倒腾出来、塞进去，都是硬功夫。PostgreSQL 在这块儿准备得很充分，提供了多种工具和方法，从命令行到图形界面都能搞定。这篇文章就把 PostgreSQL 里数据导入导出的那些事儿从头捋一遍，配上实际命令和示例，读者直接抄作业就行。

一、数据导入

1.1 使用 COPY 命令导入数据

COPY 是 PostgreSQL 自带的“大力出奇迹”工具，专门负责大批量导入导出。它直接从文件读数据，然后一股脑塞进指定表里。基本语法长这样：

COPY table_name FROM 'file_path' WITH (FORMAT csv);

1.1.1 示例：从 CSV 文件导入数据

假设手头有一个 users.csv 文件，内容像下面这样：

username,email,created_atjohn_doe,john@example.com,2023-10-01alice,alice@example.com,2023-10-02

想把它倒进 users 表，用这条命令就行：

COPY users (username, email, created_at) FROM '/path/to/users.csv' WITH (FORMAT csv, HEADER);

注意，这里的 HEADER 参数意思就是 CSV 文件第一行是列名，别把它当数据读进去了。

1.2 使用 \copy 命令导入数据

\copy 是 psql 客户端里的“亲民版” COPY，同样能干导入的活，区别在于它是在客户端执行而不是服务器端，文件路径也是相对于客户端的。语法类似：

\copy table_name FROM 'file_path' WITH (FORMAT csv);

1.2.1 示例：使用 \copy 导入数据

\copy users (username, email, created_at) FROM '/path/to/users.csv' WITH (FORMAT csv, HEADER);

跟刚才的 COPY 用起来几乎一模一样，只是写入文件的权限规则稍有不同。

1.3 使用 pgAdmin 导入数据

如果不爱敲命令行，pgAdmin 这个图形界面工具也能帮忙。操作步骤很直观：

1. 在左侧浏览器里右键点击目标表。
2. 选择“Import/Export”菜单。
3. 在弹出的窗口里选好文件路径、格式（比如 CSV），勾上 HEADER 之类的选项。
4. 点“OK”等着就行了。

对新手而言，这种方式最不容易出错。

二、数据导出

2.1 使用 COPY 命令导出数据

有进就有出，COPY 同样能把表里的数据倒出来。语法是：

COPY table_name TO 'file_path' WITH (FORMAT csv);

2.1.1 示例：导出数据到 CSV 文件

把 users 表的数据整份导出到 CSV：

COPY users TO '/path/to/users_export.csv' WITH (FORMAT csv, HEADER);

这样导出的文件第一行就是列名，方便后续直接导入到其他系统。

2.2 使用 \copy 命令导出数据

类似地，\copy 也可以用来导出：

\copy table_name TO 'file_path' WITH (FORMAT csv);

2.2.1 示例：使用 \copy 导出数据

\copy users TO '/path/to/users_export.csv' WITH (FORMAT csv, HEADER);

同样，文件会保存在客户端所在机器的路径上。

2.3 使用 pgAdmin 导出数据

在 pgAdmin 里导出也只需几步：

1. 右键点击要导出数据的表。
2. 选择“Import/Export”选项。
3. 把方向选为“Export”，设置好文件路径、格式等参数。
4. 点“OK”。

简单省事，特别适合临时倒腾数据。

三、使用 pg_dump 进行数据备份和恢复

如果需要备份整个数据库，pg_dump 是官方推荐的命令行利器。它生成一个 SQL 脚本文件，里面包含了建表、插数据的全部命令，拿到另一个环境里一跑就能还原。

3.1 使用 pg_dump 备份数据库

基本命令：

pg_dump -U username -h hostname -d database_name -f /path/to/backup.sql

3.1.1 示例：备份数据库

比如备份名为 mydatabase 的数据库：

pg_dump -U postgres -h localhost -d mydatabase -f /path/to/backup.sql

执行后，指定的 SQL 文件就包含了该库的所有结构和数据。

3.2 使用 pg_restore 恢复数据库

恢复时用 pg_restore 命令：

pg_restore -U username -h hostname -d database_name /path/to/backup.sql

3.2.1 示例：恢复数据库

pg_restore -U postgres -h localhost -d mydatabase /path/to/backup.sql

注意目标数据库要预先创建好，或者指定一个已有的空库。

四、使用 pg_dumpall 备份所有数据库

如果手头有多个数据库需要一口气全备份，pg_dumpall 就是为此准备的。它会导出整个 PostgreSQL 实例的所有数据库，包括全局角色表空间等信息。

pg_dumpall -U username -h hostname -f /path/to/backup.sql

4.1 示例：备份所有数据库

pg_dumpall -U postgres -h localhost -f /path/to/all_backup.sql

这样做一次就能拿到一份完整的“全家桶”备份。

五、数据格式和选项

PostgreSQL 在导入导出时支持几种常见的数据格式，以及一些灵活的选项。

5.1 可用格式

• CSV：逗号分隔，跟 Excel、WPS 这些电子表格软件兼容性最好。
• TEXT：纯文本格式，简单直接，适合没有特殊要求的小批量数据传输。
• BINARY：二进制格式，读写效率通常更高，但可读性差，跨平台时要注意兼容。

5.2 选项说明

• HEADER：文件第一行是否包含列名，加上了就可以省去手动指定列名的麻烦。
• DELIMITER：自定义分隔符。默认逗号，但如果数据里本身就有逗号，可以改成制表符或分号，比如 DELIMITER ';'。
• NULL：指定 NULL 值在文件中的表示形式。例如 NULL 'NULL' 表示文件里写“NULL”字符串的单元格会被当作 SQL 的 NULL。

六、注意事项

实际操作中有几个容易踩的坑需要留意：

1. 文件权限：用 COPY 时，PostgreSQL 进程必须对目标文件有读写权限。如果遇到权限错误，可以试试用 \copy（客户端执行），或者把文件放到 PostgreSQL 用户有权限的目录下。
2. 数据一致性：在高并发环境下导入导出，要小心并发写入可能导致数据不一致。可以考虑在事务中操作，或者在低峰期进行。
3. 备份策略：别等到出事了才想起备份。定期备份、异地备份、全量加增量搭配，才是靠谱的姿势。

七、总结

PostgreSQL 在数据导入导出这块儿提供了非常完整的工具链：轻量级的 COPY/\copy 适合单表迁移，pg_dump 和 pg_dumpall 能搞定整库甚至整个实例的备份恢复，再加上图形化的 pgAdmin 辅助，无论是开发、测试还是生产运维，都能找到趁手的方法。把这些命令和技巧记熟，日常的数据管理工作就会顺滑很多。

希望这篇文章能帮你把 PostgreSQL 的数据导入导出玩明白。如果还想深入了解，不妨翻翻官方文档，那里有更详细的参数和进阶用法。祝数据库一切安好！