PostgreSQL数据导入导出全攻略：pg_dump与COPY实战详解

PostgreSQL 的导入导出几乎是每个用 PG 的人迟早都要面对的事。不管你是要把生产库的数据抽一份到本地排查问题还是搭了个新环境需要把旧库完整搬过去又或者是日常给业务方提供数据文件都绕不开这几个工具。这里我结合自己这些年用 PG 的经验把数据导入导出这件事从头到尾捋一遍把常用的几种方式、适用场景、参数细节和踩过的坑一次性说清楚。这篇内容适合刚接触 PostgreSQL 的新手也适合已经用了一段时间但主要靠图形界面工具点来点去、没怎么碰过命令行的人。读完之后你能搞清楚在什么场景下该用哪个工具为什么这么选以及那些文档里不会写的注意事项。1. 先想清楚你要导的是什么这决定了工具选型很多人一上来就问“PG 怎么导入导出”其实这个问题没法一句话回答因为 PG 提供了好几套完全不同的方案各有各的适用场景。我在接手别人的库时经常看到有人用错工具——要么用 COPY 去搬整个库搬得无比痛苦要么用 pg_dump 导大表导到怀疑人生其实都是没在动手之前想清楚需求。1.1 三种核心需求场景整库迁移、表级备份、数据交换先看你的需求属于哪一种。整库迁移典型场景是换服务器、环境升级、从测试环境复制到生产环境。这种情况下你需要的不只是数据本身还有表结构、索引、约束、触发器、函数、视图、序列、权限等所有数据库对象。这时候唯一正确的选择是 pg_dump / pg_dumpall 配上 pg_restore 或 psql 执行恢复。它导出的不是单纯的业务数据而是整个数据库的“完整描述”。表级备份与恢复典型场景是某个业务表的数据出了问题需要回滚或者只需要把几张核心表搬到另一个库。这时可以继续用 pg_dump 加-t参数指定表也可以用 COPY 命令单独把表数据导成文件。这里要提醒的是如果只是导数据而不需要表结构COPY 是效率最高的方案如果连建表语句都要那就老老实实用 pg_dump 的表级导出。数据交换典型场景是跟其他系统对接对方要 CSV 文件或者你要把 Excel 整理好的数据灌进 PG。这时候 COPY / \copy 是绝对主力配合 CSV 格式的选项几乎是做数据导入导出的人每天都要用的东西。pg_dump 导出的文件格式并不适合直接扔给别的系统这是很多人容易混淆的地方。所以我在接到导入导出需求时第一件事永远是问三句话数据量大不大是只导数据还是连结构一起导导出来的文件是要给 PG 自己用还是给别人做数据交换这三个问题的答案基本就决定了方案。1.2 pg_dump、COPY、\copy、pg_dumpall四个工具的分工把这几个工具放在一起对比着看思路就清晰了。pg_dump 是逻辑备份的核心工具导出一个数据库的完整逻辑内容。它支持三种输出格式纯 SQL 脚本plain、自定义归档格式custom、目录格式directory。纯 SQL 脚本是最直观的用 pg_restore 或者 psql 都能恢复自定义归档格式支持压缩和并行恢复适合大库目录格式适合超大库可以配合并行导出使用。pg_dumpall 是管整个 PostgreSQL 实例的它导出的范围包括所有数据库、全局的角色、表空间等。说实话我实际生产中用得并不多因为大部分场景只需要单个数据库级别的备份但如果你是整个集群级别的迁移或者需要把用户权限也带走就得靠它。COPY 是 PostgreSQL 内置的高效导入导出命令它运行在数据库服务端直接把表数据和文件之间做读写。因为是服务端操作所以对文件路径有要求这个后面细说。COPY TO 可以导出表数据为文本格式或 CSV 格式COPY FROM 负责把文件数据灌进表里。它的执行效率是所有方案里最高的。\copy 是 psql 客户端提供的命令底层实现和 COPY 一样但它在客户端本地读写文件不需要数据库服务器上的文件系统权限。日常开发中用 \copy 的体验比 COPY 好很多因为不需要为文件路径权限折腾。但要注意\copy 只能在 psql 里用在 Navicat 这类图形工具里执行不了图形工具一般自己封装了导入导出功能。1.3 从热词看需求为什么很多人卡在“安装和版本”这一步有意思的是最近关于 PostgreSQL 的搜索热词里大量是“postgresql安装”、“postgresql下载哪个版本”、“postgresql16便携版”、“postgresql17”这类问题。这说明很多人在还没摸到导入导出功能之前就已经被环境问题拦住了一截。版本这事确实值得多说一句。PG 大版本升级后数据目录的内部格式可能有变化旧版本的数据文件不能直接被新版本读取需要先逻辑导出再导入到新库。这就是为什么每到大版本发布就有一批人要集中处理“升级 数据迁移”的组合任务而迁移的核心手段恰恰就是 pg_dump。如果你还没装 PG建议直接装 16 或 17 的正式稳定版不要追求最新的大版本也不要停留在太老的版本上。16 在逻辑复制、查询性能方面有不少改进17 则在 vacuum、内存管理方面更进一步但对于导入导出这件事来说工具的核心用法是一致的选一个稳定的版本长期用就好。装的时候记得把 psql 和 pg_dump 加到系统 PATH 里这两个命令行工具在后续的数据导入导出中会频繁用到。2. pg_dump 和 pg_restore全库迁移的最稳方案很多人对 pg_dump 的印象就是“一条命令把库导出来”但实际上它有很多参数值得细抠。用得好几秒钟就能完成一个精确到某张表的导出用不好光是一个大库导半天导不完的事情我也见过不少。2.1 一条命令看懂 pg_dump 的用法与参数最基本的用法其实就两行命令。导出时指定数据库名和输出文件pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -F c -f mydb.dump恢复时用 pg_restorepg_restore -h 127.0.0.1 -p 5432 -U postgres -d newdb -j 4 mydb.dump这里有几个参数值得展开说。-h是数据库主机地址-p是端口-U是用户-d是数据库名。这些参数很常规但我在实际工作中发现很多人会在这里犯一个低级错误用 pg_dump 导出时写的主机地址是数据库服务器内网 IP到了另一台机器上恢复时忘了改地址导致连接超时。这个看起来是小事却能卡住你十分钟。-F参数指定输出格式。c 是 custom 自定义格式d 是 directory 目录格式p 是 plain 纯 SQL 脚本。默认是 p。我的习惯是小库直接导 plain 格式因为文件就是一个 SQL 脚本能用文本编辑器直接打开看内容出问题也好排查大库用 custom 或 directory 格式因为支持压缩、支持选择性恢复、支持并行恢复功能强很多。-f指定输出文件名。如果用了-F d目录格式-f后面要跟的是一个目录名而不是文件名。不带-F参数直接导出纯 SQL 脚本的常见写法是pg_dump -U postgres -d mydb mydb.sql恢复它也不需要 pg_restore直接用 psql 执行psql -U postgres -d newdb -f mydb.sql2.2 为什么推荐使用自定义格式而不是纯 SQL 脚本纯 SQL 脚本最大的问题是恢复时缺少灵活性。一旦你要恢复的库跟导出时的库有差异比如某些对象已经存在了报错之后整个恢复流程就可能中断排查起来也麻烦。自定义格式-F c的优势在于它内部保存了每个数据库对象的元数据恢复时可以用 pg_restore 精确控制要恢复哪些对象、跳过哪些对象还能调整恢复顺序。比如我只想恢复某个表的数据而不想动其他东西可以这样pg_restore -d newdb -t public.users mydb.dump只想看看这个 dump 文件里到底装了哪些对象可以pg_restore -l mydb.dump这个-llist参数会列出归档文件中所有的对象清单带序号。拿到清单之后你可以编辑这个清单只保留需要恢复的行再配合-L参数指定清单文件来恢复。这个玩法在大型迁移时非常实用。另外自定义格式默认会压缩实际占用空间通常只有纯 SQL 脚本的五分之一到十分之一对大库来说差异非常明显。2.3 表级导出如何只迁移指定表的结构和数据只导出某几张表是日常工作中出现频率很高的需求。假设我只需要导出 public schema 下的 users 和 orders 两张表pg_dump -U postgres -d mydb -t public.users -t public.orders -F c -f tables.dump注意-t参数可以写多个每个都要单独带一个-t。表名前面要带 schema 名否则默认从 public schema 里找如果你用了自定义 schema 就会提示找不到表这是很多人踩过的坑。恢复时和全库恢复一样用 pg_restore 执行。但有个细节如果目标库中这些表已经存在恢复会报“表已存在”的错误。这时候要看你的需求——如果只是想补数据可以用--clean参数先删掉目标表再重建如果不想删表只想追加数据光靠 pg_restore 做不到得用 COPY 的方式单独导数据。--clean参数的意思是恢复前先尝试删除已存在的对象。加上它之后恢复会更“干净”但也更危险因为它会先 drop 掉目标对象如果表里有重要数据且你只是误操作那就真的没了。所以每次用--clean之前我都会再三确认目标库确实是一个空库或者允许覆盖的库。2.4 并行导出的关键参数与注意事项大库导出是个老大难问题。几十 GB 的库单线程 pg_dump 可能需要几个小时但如果用目录格式配合并行导出时间能缩短一半以上。并行导出需要满足两个条件格式必须是目录格式-F d同时指定-j参数设置并行度pg_dump -U postgres -d mydb -F d -j 4 -f /backup/mydb_dir-j 4表示同时启用 4 个导出线程。这里要特别注意并行导出和并行恢复是两回事恢复时同样需要-j参数但要配合 pg_restore 使用pg_restore -U postgres -d newdb -j 4 /backup/mydb_dir并行恢复的核心原理是把导出的归档文件拆分成多个任务同时执行。这里面有个关键限制如果导出时没有使用并行恢复时照样可以并行因为 pg_restore 是读归档里的对象清单再分发给多个 worker 执行的但如果导出时没有用目录格式或自定义格式纯 SQL 脚本就没法并行恢复这也是我一直推荐大库用-F c或-F d的另一个原因。-j参数不是拉得越大越好。我实测过 4、8、16 这三档并行度在普通机器上 4 到 8 收益最明显超过 8 之后磁盘 IO 往往先成为瓶颈。另外并行恢复时如果目标表之间有外键依赖多个 worker 同时往关联表里插入数据主键冲突的概率会上升必要时可以考虑恢复时暂时禁用触发器或用--disable-triggers参数降低风险。3. 从文件到数据COPY 命令的高效导入导出说到单表数据导入导出COPY 才是真正的核心工具。pg_dump 导出的文件如果只是为了数据交换会显得太笨重而 COPY 直接跟 CSV 文件打交道几乎所有的数据分析师和数据工程师都会用到它。3.1 COPY 与 \copy 的区别这两个命令长得像但运行机制完全不同很多人一开始容易搞混。COPY 是服务端命令。你在 psql 里执行COPY table TO /tmp/data.csv实际是在数据库服务器上执行文件读写。这意味着你写的文件路径必须从数据库服务器本地能看到而且运行数据库的操作系统用户通常是 postgres必须对这个路径有写权限。远程连接数据库时用 COPY文件是写在服务器上的不是写在你本地电脑上的——这个区别极其容易踩坑。\copy 是 psql 客户端命令。它把你本地的文件作为数据源或者把查询结果写到你本地的文件里。底层实现是通过 psql 跟数据库服务端通信把数据一条条传输到客户端来写文件但在你看来操作方式和 COPY 很像。它不需要数据库服务器的本地文件系统权限只要你能连上数据库、对表有相应的权限就够了。它们俩在语法上几乎一样唯一的区别就是 \copy 比 COPY 前面多一个反斜杠以及 COPY 后面不用分号结尾\copy 可以加分号但不强制。-- 服务端写法文件在数据库服务器上 COPY users TO /tmp/users.csv WITH CSV HEADER; -- 客户端写法文件在你本地 \copy users TO /tmp/users.csv WITH CSV HEADER我个人的经验是日常开发测试环境用 \copy因为它方便、不需要 DBA 帮你开服务器目录写权限生产环境或者需要定时任务自动跑的时候用 COPY 配合服务端脚本更稳因为它不产生客户端与服务端的逐行数据传输开销性能更好。3.2 带表头导出的实用细节CSV 格式与分隔符选择大部分人要导出 CSV 是为了给别人或者给 Excel 用所以带表头HEADER是常规操作\copy (SELECT id, name, created_at FROM users WHERE created_at 2024-01-01) TO /tmp/users_2024.csv WITH CSV HEADER注意我在 COPY 后面跟的是一个查询语句而不是表名。COPY 支持两种写法直接跟表名表示导出整个表或者跟一个 SELECT 查询表示导出查询结果。这个特性非常实用等于你在导出环节就能把数据过滤、加工好不用先建一张临时表再导。分隔符也是常见问题。默认的 CSV 分隔符是逗号但如果你的字段值里本身包含逗号比如地址、备注之类CSV 格式会通过引号转义处理大部分情况下没问题。可如果对方要求的不是标准 CSV而是用制表符或分号做分隔可以这样指定\copy users TO /tmp/users.tsv WITH DELIMITER E\t CSV HEADER这里用E\\t表示转义后的制表符。要注意的是CSV 模式下分隔符必须是单个字符而且不能是双引号、换行符之类有特殊含义的字符。3.3 导入数据时最常见的三种错误场景COPY FROM 导数据时最常见的报错有这么几类。第一种是类型转换失败比如字符串“abc”被灌进 integer 列导入就会报错并停止。此时可以用NULL参数指定哪些字符串在导入时应该被当作 NULL 处理比如很多系统导出空值时用的是空字符串而不是真正的 NULL\copy users FROM /tmp/users.csv WITH CSV HEADER NULL 第二种是编码问题。如果 CSV 文件是 GBK 编码而数据库默认是 UTF8导入时会直接报编码错误。处理方式要么在操作系统层面先转码要么在导出端就统一好编码格式。我最常用的做法是用 iconv 或 Python 脚本先转码不让数据库去处理这种脏活因为数据库层面改编码选项的能力有限。第三种是约束冲突。目标表如果有唯一索引或外键导入时重复数据或非法引用会导致整条导入中途失败。COPY 的处理方式是一旦遇到错误就停在整个表的事务里之前插入的数据也会回滚。所以大文件导入前最好先自己检查数据质量或者分批导入避免一次性一锅端。3.4 大文件导入前必须调整的两个参数COPY 导入大数据文件时还经常碰到连接中断或者速度过慢的问题。这跟 PG 的一个机制有关——它把一条 COPY 语句当成一个单事务来处理所以网络超时、磁盘空间不足这些因素都可能导致整个导入失败。遇到这种场景我一般会先调两个参数。第一个是statement_timeout它控制单条语句的最大执行时间默认值是 0不限制但有些云数据库或公司自建的 PG 默认配置里可能设置了值导致大文件导入到一半被意外中断。可以在会话级别先跑一句SET statement_timeout 0;第二个是maintenance_work_mem。这个参数在导入数据时会影响索引重建的速度调大它能显著减少建索引的时间SET maintenance_work_mem 1GB;调高maintenance_work_mem的原理是写入大量数据时如果表上有索引PostgreSQL 需要更新这些索引更大的内存能减少索引排序和合并的次数从而整体提速。服务端内存足够的话这个参数可以放心调高一些只在当前会话生效不影响全局配置。3.5 用 \copy 导入的完整示例实际工作中接 Excel 转 CSV 再导入 PG 的场景非常多这里给一套我反复在用的完整流程。假设我有一份用户的 Excel 文件准备导入到一个新表CREATE TABLE tmp_users ( id bigint, name varchar(100), email varchar(200), created_at timestamp );文件/tmp/new_users.csv的内容大概是id,name,email,created_at 1001,张三,zhangsanexample.com,2024-03-01 10:00:00 1002,李四,lisiexample.com,2024-03-02 11:30:00执行导入\copy tmp_users FROM /tmp/new_users.csv WITH CSV HEADER查看影响行数SELECT count(*) FROM tmp_users;如果数据有异常比如某些行的 email 字段本来就是空的CSV 里可能表现为连续两个逗号导入时默认会把空字符串灌进去。如果空值在业务上应该对应 NULL就需要用NULL 来显式指定。3.6 二进制格式导出小技巧解决时间和精度问题除了文本和 CSVCOPY 还支持二进制格式。这个二进制格式主要用于同一数据库版本之间的数据转移它比文本格式快很多而且不会出现浮点数精度丢失、时区转换错误这类问题。导出COPY users TO /tmp/users.bin WITH BINARY导入COPY users FROM /tmp/users.bin WITH BINARY说实话这个功能我用得不算多因为二进制文件不能被其他系统直接读取可排查性差。但如果你在同一个 PG 环境里的两个 schema 之间搬大表或者在同一版本的两个实例间转移大表数据又不想重建表结构二进制格式的体感和速度会让你惊喜。它跳过了文本格式的解析和转换开销对那些有大量浮点数的表提升尤其明显。4. 导入导出过程中的常见问题与实战排查思路写了这么多年 SQL每次帮同事处理导入导出问题最终都能归到那么几个方向。这里挑几个高频问题和对应的解决思路整理成一份可以直接对照使用的问题手册。4.1 导入时“权限不足”的真正含义错误信息 ERROR: permission denied for table xxx 在导入时太常见了。但这行字背后的原因可能有好几种。最常见的是你连接数据库用的用户对目标表没有 INSERT 权限。PostgreSQL 的权限模型细到列级别如果表 owner 是别人而操作者只是被授予了 SELECT 权限COPY FROM 就会直接拒绝。解决方案是用超级用户或表 owner 执行导入或者显式授权GRANT INSERT ON table_name TO username;另一种情况是序列权限。导入数据时如果表有自增主键即使 INSERT 权限正常如果用户没有对序列的 USAGE 权限也会因为取不到下一个序列值而报错。这类报错经常被误认为是表权限问题排查时值得多看一眼。4.2 pg_dump 版本与服务端版本不一致的坑pg_dump 这个客户端工具跟 PostgreSQL 服务端之间是有版本兼容要求的。官方规则是pg_dump 的版本必须大于或等于服务端版本而且同一个大版本内兼容性最好。用 15 的 pg_dump 去导 17 的服务端数据库有时候会出现“服务器版本不匹配”的报错提示或者导出的脚本包含旧版本不认识的新特性。这个坑在实际工作里非常普遍尤其是当你在一台机器上装了多个 PG 版本时。检查当前 pg_dump 对应的版本pg_dump --version如果发现版本不对一种方式是找到对应版本的 pg_dump 路径直接使用另一种是用容器或指定完整路径的方式确保版本匹配。我自己在 Mac 上装过 PG 16 和 PG 17 两套环境每次都要手动切换 PATH 来用对应版本的工具习惯了之后反而觉得这是在提醒你别用错工具。4.3 导入大库时磁盘空间和内存溢出的处理恢复一个几十 GB 的库除了数据库本身的容量还要额外考虑临时文件、索引构建、WAL 日志的开销。我吃过亏之后养成了一个习惯恢复前用 df 检查目标机器的磁盘空间至少要保证剩余空间是整个库体积的 1.5 到 2 倍。如果空间不足一个实用的折中方案是分步恢复。先恢复表结构只导 schema再分批导入数据。用 pg_dump 导出时可以用--schema-only和--data-only两个参数分别导出结构部分和数据部分pg_dump -U postgres -d mydb --schema-only -f schema.sql pg_dump -U postgres -d mydb --data-only -f data.sql然后先执行 schema.sql 创建好所有表结构再执行 data.sql 导入数据。这样即使数据导入中途失败表结构已经在了排查问题会方便很多。4.4 恢复时外键约束导致数据插入失败的应对大库恢复最烦人的一个问题就是外键依赖。比如一张订单表外键关联用户表如果 pg_restore 在插入订单数据时对应的用户数据还没插入就会触发外键约束冲突。虽然 pg_restore 的顺序控制通常能处理大部分场景但遇到分片导出的数据文件时这个问题就很容易冒出来。应对方案有几种。第一种是恢复时先跳过约束数据导完再重建。导出表结构时不要包含约束等所有数据导入完成再单独执行创建外键的语句。第二种是恢复时使用--disable-triggers让 PG 在数据加载期间不触发触发器数据加载完成后一次性启用。这个参数对包含大量复杂触发器的库非常有效。第三种是在会话级别临时关闭约束检查SET session_replication_role replica; -- 导入数据... SET session_replication_role origin;注意这个操作需要超级用户权限而且导入期间确实绕过了所有的外键检查和触发器。正因为如此它只适合在明确的迁移窗口内使用导入完成后一定要记得恢复origin状态否则后续的正常写入可能产生脏数据。4.5 编码问题引发的乱码与中断中文环境下编码问题几乎是必然遇到的坎。CSV 文件最常见的来源是 Excel 导出的“CSV UTF-8”或者旧系统导出的 GBK/GB18030 编码文件。PG 数据库默认通常为 UTF8直接把 GBK 文件 COPY 进去轻则乱码重则直接中断报错。我的处理流程是先用 file 命令确认文件编码格式file /tmp/users.csv如果是非 UTF8 编码先转码再导入。Linux 下用 iconviconv -f GBK -t UTF-8 /tmp/users_gbk.csv /tmp/users_utf8.csv转码之后再用 \copy 导入。这样做看起来多了一步实则最安全。我不太建议让 COPY 去硬吃非 UTF8 文件因为出了问题排查成本远高于转码这一步的时间成本。4.6 一个容易忽视的问题默认值丢失与序列漂移用 COPY 导数据时还有个常见问题如果表里有自增主键直接导入数据文件后对应的序列值并不会自动更新到最大 ID。结果就是下次应用正常插入数据时主键会从 1 开始尝试撞上已有数据的 ID报主键冲突。解决办法是导入后手动重置序列。PostgreSQL 提供了一个内置函数SELECT setval(users_id_seq, (SELECT max(id) FROM users));这里的users_id_seq是自增列的默认序列名格式是“表名_列名_seq”。如果你不确定序列名可以这样查SELECT pg_get_serial_sequence(users, id);这个坑在平时不显眼一旦遇到线上应用报主键冲突第一反应往往都是去查数据重复很少有人想到是序列没同步。我自己被这个坑过一次之后现在凡是经过 COPY 导入自增表数据的操作最后一定会加上重设序列这一步。5. 一些我长期在用的实践心得导出导入这事看着基础但做好做坏差别很大。几个我长期沿用的小习惯分享给各位。第一任何导入导出操作先确认版本“是不是同一个大版本”决定了很多参数能不能通用。跨版本迁移前我都习惯先拿一个测试库跑一遍全流程确认没有坑之后再到生产环境上操作。第二大库操作永远预留后手。导出的文件要保留导入的目标库要留回滚空间。我见过有人在生产上导入失败之后想回到旧库结果旧的备份已经覆盖掉了那种情况真的很难收拾。第三导数据的时候能过滤就不要全量。很多人习惯了SELECT *思维导出时也一导就是整个表。但实际上很多时候只需要若干字段、某个时间范围的数据。用 COPY 跟查询结合的方式导出的文件更小导入也更快对别人使用这些数据也友好得多。第四Navicat 这类图形工具适合小数据量、日常操作但一旦数据量上到千万级或者需要精确控制导出内容命令行工具的稳定性和可重复性还是更优。尤其是要写进定时任务、自动化脚本的场景命令行方案几乎是唯一选择。PostgreSQL 的数据导入导出体系说复杂也复杂说简单也简单。核心就是搞清楚自己需要的是“逻辑备份”“表数据转移”还是“明文数据交换”然后选对工具理解每个参数背后的机制剩下的事情就比较机械了。实际跑数据的时候我经常打开 htop 盯着数据库进程看着多个 worker 齐头并进地工作那种效率感非常直观。希望这篇内容能帮你少走一些我当年走过的弯路把导入导出做得又快又稳。