最近接了一个数据迁移的活儿，帮朋友把一个老系统的业务数据从一台服务器搬到另一台服务器。库不大，几十张表，但外键、序列、视图全都有。一开始我想省事，找图形化工具点点点，结果点了几下就发现事情没那么简单：要么编码不对，要么权限报错，要么导出来的文件回灌回去的时候外键对不上。绕了一圈，最后还是老老实实把 PostgreSQL 自带的导入导出工具吃透了，才把活干完。

这篇东西就是那次迁移的完整记录。围绕 PostgreSQL 的数据导入和导出，把我在实际操作中用的工具、踩过的坑、验证过的参数都写清楚。内容适合正在做数据库迁移、日常需要搬运表数据、或者刚接触 PostgreSQL 想知道用什么姿势导数据最稳的朋友。不夸张地说，看完这篇，你能少走至少两三个晚上排查问题的弯路。

1. 为什么我会优先选 PostgreSQL 自带的数据搬运工具

很多人一听说数据导入导出，第一反应是去装一个第三方工具，或者写一堆自定义脚本。我的建议很直接：只要目标库是 PostgreSQL，优先把自带的pg_dump、pg_restore、COPY和psql用明白。它们不是万能的，但覆盖了 90% 以上的日常场景，而且所有行为和数据库自身逻辑是一家的，排错成本最低。

1.1 一次迁移经历带来的选择

当时我对比过几条路：

• 用 Navicat 之类的图形化客户端，选中表，右键导出，再导入。看起来方便，但大表容易卡界面，字段类型在跨库迁移时也经常需要手动调整。
• 自己写脚本，一条条 SELECT 再拼 INSERT。小表可以，碰到几百万行的表就是灾难，光生成 SQL 的时间都够吃顿晚饭了。
• 用 PostgreSQL 自带的COPY和pg_dump。前者负责表级数据流动，后者负责全库或 schema 级的结构加数据备份迁移。两个结合起来，基本可以应对绝大多数场景。

那次迁移最后就是用pg_dump导出整个库，再用pg_restore导入到新库。结构对象、数据、权限一点没乱，整个过程顺利得让我自己都有点意外。

1.2 自带工具和第三方工具的核心差异

第三方工具最大的问题是“隔了一层”。它们一般通过 JDBC 或 ODBC 连接数据库，导出的本质是先从数据库查数据，再按自己的格式写成文件。这个过程中，PostgreSQL 内部很多底层逻辑是拿不到也控制不了的。

自带工具则直接在数据库内核层面干活：

• COPY由后端进程直接读写文件，不走 SQL 解析和客户端缓冲，速度比一行行 INSERT 快几个数量级。
• pg_dump能识别当前数据库的完整对象依赖关系，知道先导哪些表、后导哪些表才能不破坏外键约束。
• 自带工具对 PostgreSQL 特有类型（数组、jsonb、hstore、枚举等）的支持是原生的，第三方工具偶尔会在这里翻车。

所以我的原则是：能用自带的，就不折腾第三方的。除非你需要把数据从 PostgreSQL 搬到 Oracle、MySQL、SQL Server，那另说，那种跨库场景再考虑中间格式转换工具。

2. 动手前的环境准备：版本选择、连接授权、客户端工具

导入导出看着是命令行的活儿，但很多报错其实根源在环境没搭好。我见过太多人花一晚上排查“为什么 COPY 报权限错误”，最后发现是路径写错或者客户端版本太老。先花十分钟把环境捋顺，后面能省很多事。

2.1 PostgreSQL 版本到底怎么选

我刷到过不少人在问“postgresql 下载哪个版本”，尤其是新出的版本总会让人心痒。以我自己的观察，版本选择其实只需要考虑三件事：

1. 当前生产环境是多少就先用多少，别贸然跨大版本。比如生产库里是 14，迁移目标就不建议直接跨到 17，除非你确认过所有兼容性影响。
2. 新项目可以优先用当前主流稳定版。比如写这篇文章时，16 已经非常稳，17 也已经发布了几个月，但我个人在生产环境还是偏好“上一个版本的成熟度”，不追求最新。
3. 便携版（比如有人问的 postgresql 16 便携版）适合拿来学习、测试、快速验证，不建议直接跑正式业务。便携版本质上只是免安装，服务和数据目录的组织方式和正式版有差异，排查问题时的路径都和标准版不一样。

版本匹配还有一个很容易被忽略的坑：pg_dump和pg_restore的版本最好和数据库版本一致，或者客户端版本不低于服务端版本。低版本客户端去连高版本服务端，可能连不上去，或者导出时漏掉新版本才有的对象类型。

2.2 psql 连接授权：排错先看这里

导入导出离不开客户端连接。很多人第一步就卡在连不上数据库，而且报错五花八门。常见的连接失败基本就三类：

• 找不到数据库服务，报could not connect to server。先检查端口、IP、服务是否启动。
• 密码错误，报password authentication failed。如果确认密码没错，再查一下 pg_hba.conf 里的认证方式，有些默认配置下 md5 和 scram-sha-256 混用会出现怪问题。
• 权限不足，报permission denied for schema。这种通常不是连接层的问题，而是登录用户对该库没权限。

排查的时候别瞎猜，顺序固定：先pg_isready -h 主机 -p 端口确认网络和服务，再psql -U 用户 -h 主机 -p 端口 -d 数据库测连接，最后再查 pg_hba.conf。pg_hba.conf 改完要记得重载配置：SELECT pg_reload_conf();或者直接pg_ctl reload。

2.3 创建专用账号的检查项

如果是给别人做数据迁移，或者定期跑导出任务，我建议新建一个专用账号，别拿超级用户到处用。这个账号需要哪些权限取决于任务，但以下检查项最好过一遍：

• 数据库连接权限：CONNECT。
• 读取表数据的权限：SELECT或pg_read_all_data（PG 14 之后可用）。
• 写入数据的权限：INSERT、UPDATE，如果涉及结构变更还要CREATE、ALTER。
• 如果是恢复备份，需要CREATEDB或目标库的 owner 权限。

经验之谈，临时迁移任务可以直接用超级用户，但如果是长期定时备份脚本，一定要单独建一个最小权限账号，避免某天误操作把生产库弄乱。

3. 表级数据搬运：COPY 与 \copy 的真实差异

PostgreSQL 导出单表数据，最常用的就是COPY。但新手特别容易搞混的是COPY和\copy，这俩名字就差一个反斜杠，行为却差很多。

3.1 两者的本质区别

直接看对比表格：

我第一次用COPY导出时报错ERROR: could not open file for writing: Permission denied，当时百思不得其解，后来才明白：COPY里的路径是给数据库服务器看的，不是给你本机看的。如果你用远程工具连着一台云数据库，写/tmp/xxx.csv实际上是写在服务器那边的/tmp，本地根本找不到文件。

反过来，如果你在服务器上用 psql 连本机数据库，直接COPY最方便，因为文件就在旁边。如果是远程连库，想导到本地电脑，那就用\copy。

3.2 服务器端 COPY 的权限与路径限制

除了路径位置，COPY在权限上还有一层限制：普通的非超级用户执行COPY table TO '/tmp/file.csv'会报权限错误。这在 PostgreSQL 里是故意的安全设计，防止普通用户随便读取服务端任意文件。

解决方式有三个：

• 用超级用户执行。
• 给目标用户授权pg_write_server_files（对应导出）或pg_read_server_files（对应导入）。
• 用\copy绕开服务端文件权限问题。

生产环境我一般不会为了导单个文件去授权，临时任务直接超级用户干完就走，长期任务才规规矩矩授权给专用账号。

3.3 常用参数和进阶写法

表级导出最常用的写法：

COPY users TO '/backup/users.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

导入回来就是：

COPY users FROM '/backup/users.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

几个值得记住的参数：

• FORMAT CSV：CSV 格式，默认还有 text 和 binary 两种。text 是 PostgreSQL 原生的私有格式，跨库迁移用它不容易出兼容问题；二进制格式最快，但只能给相同版本的 PostgreSQL 用。
• HEADER true：第一行是列名。导出带上，导入时可以用HEADER true跳过，也能让 CSV 文件给 Excel、WPS 这种工具直接打开。
• DELIMITER ','：分隔符。默认是 tab，如果用逗号，记得字段里有逗号时要靠QUOTE参数解决。
• ENCODING 'UTF8'：强烈建议显式指定编码，尤其数据里有中文的时候。不指定，容易跑到数据库默认编码上，导出的文件可能在别的工具里显示乱码。

另外一个容易坑人的点：字段内容如果包含分隔符，PostgreSQL 会自动加引号包裹，但前提是FORMAT CSV。如果你用默认 text 格式，又手动指定了特殊分隔符，遇到特殊字符时处理逻辑会让人怀疑人生。所以，能用 CSV 就用 CSV，省心。

4. 全库迁移：pg_dump 和 pg_restore 组合拳

单表数据用COPY没问题，但整个库的结构加数据一起搬，pg_dump才是真正的核心工具。它导出的是一个逻辑备份，里面包含建表语句、索引、外键、视图、函数、序列、数据等等。

4.1 pg_dump 的核心参数

基础用法一句话：

pg_dump -h 源库IP -U 用户名 -d 数据库名 -f 备份文件名.dump

但实际使用中，我会按场景组合参数：

# 标准全库逻辑备份（含数据） pg_dump -h 127.0.0.1 -U postgres -d mydb -F c -f mydb.dump # 只导结构，不要数据 pg_dump -h 127.0.0.1 -U postgres -d mydb -F c --schema-only -f mydb_schema.dump # 只导数据，不要结构 pg_dump -h 127.0.0.1 -U postgres -d mydb -F c --data-only -f mydb_data.dump

几个关键参数说明一下：

• -F c：自定义格式，压缩过的，配合pg_restore用最灵活，可以恢复时选择性地恢复某些表。
• -F p：纯 SQL 文本格式，可以直接用 psql 执行，但灵活性差一些。
• --schema-only/--data-only：结构和数据分开导，这在某些场景下特别好用。
• -j：并行导出，配合-F d目录格式使用。多核 CPU 能明显加速大库导出。
• --no-owner、--no-privileges：恢复时忽略原有的 owner 和权限信息。如果目标库的用户名和源库不一样，这两个参数能省大量修改权限的工夫。

4.2 pg_restore 如何还原

用-F c生成的 dump 文件，不能用 psql 直接跑，必须用pg_restore：

pg_restore -h 目标库IP -U 用户名 -d 目标数据库名 mydb.dump

需要注意，目标数据库必须提前建好，因为 pg_restore 不会帮你创建数据库（除非你用--create参数）。

恢复时我最常用的是先恢复结构、再恢复数据的两段式：

# 先恢复结构 pg_restore -h 127.0.0.1 -U postgres -d newdb --schema-only mydb.dump # 再恢复数据 pg_restore -h 127.0.0.1 -U postgres -d newdb --data-only mydb.dump

为什么要分两步？因为如果结构和数据一起恢复，一旦表之间存在外键依赖，数据恢复的顺序错了就会报外键冲突。虽然 pg_restore 内部会处理依赖顺序，但分两步可以在遇到问题时更容易定位是结构问题还是数据问题。

4.3 外部工具导出的文件怎么进 PostgreSQL

热搜里一堆乱七八糟的“导出”关键词，比如 ArcGIS 导入 CSV 数据、华为运动健康导出数据、文华财经数据导出等等。这些工具导出的文件五花八门，但最终的归宿大多是一个 CSV 或 Excel 表格。

把这类外部数据导进 PostgreSQL，思路其实都是统一的：先把文件弄成一个干净的 CSV，再用COPY ... FROM导入。

我遇到过好几个人拿着 Excel 文件问我怎么导进数据库。实话实说，PostgreSQL 没有直接读 xlsx 的原生命令，最稳的路线是：

1. 在 Excel 或 WPS 里把文件另存为 CSV。
2. 用文本编辑器检查 CSV 的编码和分隔符。很多国产表格工具默认导出 GBK 编码 CSV，直接用可能乱码。
3. 在 PostgreSQL 里先建一张临时表，字段类型全部先用 text 顶住，导入成功后再用 SQL 转成正式类型。

另外提一句，表格软件导出 CSV 偶尔会报错，比如有朋友遇到过 WPS 导出文件时提示 0x80000008 之类的错误。这种情况通常不是数据库的问题，是表格文件本身格式异常，或者导出路径没有写权限。换个目录、重新另存一份纯 CSV，往往就好了。

5. 大数据集导出、导入的性能与稳定性问题

数据量一旦上去，导入导出就不只是“能不能跑”的问题，而是“多久能跑完”和“中途会不会挂”的问题。这里分享一下我在大数据集场景下的处理经验。

5.1 为什么大表导出会慢甚至卡住

很多人对大表导出慢的第一反应是“网络不行”或者“工具不行”，但我想说，先看几个更常见的瓶颈：

• 查询本身太慢。COPY导出本质是一个SELECT，如果源表缺少合适的索引，或者导出时还有其他大查询在跑，IO 和 CPU 抢起来，导出速度自然快不了。
• 锁等待。如果导出期间有事务在写表，COPY会被阻塞。反过来，长期运行的大导出也可能拖住其他操作。
• 客户端瓶颈。\copy模式下，数据先由服务端发给客户端，再由客户端写文件，这个链路任何一个环节慢，整体就慢。如果又是远程跨网，网络带宽就是天花板。

5.2 分批导出的具体方案

什么情况下需要分批？我的标准是：单表超过 100GB，或者导出时间超过业务允许的窗口，就必须考虑分批。

分批导出思路大概有四种：

1. 按主键区间切分。比如每 100 万行一个区间，分别导出：

COPY (SELECT * FROM big_table WHERE id BETWEEN 1 AND 1000000) TO '/tmp/big_part1.csv' WITH (FORMAT CSV, HEADER true);

2. 按时间切分。有 create_time 之类的字段时，按天或按月切，最自然。

3. 用游标在客户端分批拉取。写一个脚本，每次 fetch 一定行数写入一个文件，最后合并。

4. 用并行导出工具或插件。PostgreSQL 本身不做数据的并行COPY，需要靠外部工具。网上流传的各种“大数据集导出插件”其实大多是把 shell 的&配合多个导出任务并行跑，本质不复杂，但注意要控制并发数，避免把源库 IO 打满。

导入方向相同道理。一次性导入几千万行，可以先BEGIN;再执行多个COPY，最后一次性COMMIT，这样能减少每次提交的 fsync 开销，导入速度快很多。

5.3 大文件传输时的压缩与校验

数据导出来了，文件怎么搬到目标服务器也是一个问题。裸传 CSV 可能非常大，我的习惯是导出时直接压缩。

用 pg_dump 的话，-F c本身就带压缩，不需要额外处理。用COPY导出 CSV 的话，可以导出到管道直接压缩：

psql -h 源库IP -U postgres -d mydb -c "COPY big_table TO STDOUT WITH (FORMAT CSV, HEADER true)" | gzip > big_table.csv.gz

导入时再解压喂回去：

zcat big_table.csv.gz | psql -h 目标库IP -U postgres -d newdb -c "COPY big_table FROM STDIN WITH (FORMAT CSV, HEADER true)"

拆解一下上面这两条命令，核心在于把导出结果输出到标准输出，然后在 shell 层做压缩和传输。这种方式不落中间文件，磁盘占用小，而且大文件也能持续流式传输，不会因为磁盘爆掉而失败。

数据到达目标端之后，强烈建议做一次行数和校验值对比：

-- 源库统计 SELECT count(*), sum(take_hash) FROM big_table; -- 目标库统计 SELECT count(*), sum(take_hash) FROM big_table;

count 只能证明行数一致，sum(哈希) 能发现某一行内容是否在传输中损坏，算是成本最低的校验方案。

6. 编码、路径、权限：高频踩坑排查链路

导入导出的报错就那么多，翻来覆去其实集中在编码、路径、权限这三类。我把完整的排查链路写出来，遇到问题照着走一遍，基本能解决 80% 的情况。

6.1 编码问题：从“乱码”逆推“编码”

乱码的经典表现是：导入后中文全变成“鍧愰噴”或者“???”，导出后 Excel 打开全是乱码。

这种问题的根因是编码不一致。PostgreSQL 数据库安装时常选 UTF8 编码，而外部 CSV 文件可能是 GBK、GB2312 或者带 BOM 的 UTF8。

排查链路：

1. 先确定数据库编码：SHOW server_encoding;
2. 再确定文件的真实编码。Linux 下可以用file命令：

file -i data.csv

Windows 下可以用 Notepad++ 或 VS Code 看底部编码提示。

3. 导入时显式指定编码：

COPY big_table FROM '/path/data.csv' WITH (FORMAT CSV, HEADER true, ENCODING 'GBK');

4. 导出时也显式指定：

COPY big_table TO '/path/data.csv' WITH (FORMAT CSV, HEADER true, ENCODING 'UTF8');

别偷懒不写 ENCODING，数据库默认编码和文件实际编码常常是两回事，显式声明是最保险的。

6.2 路径问题：为什么 COPY 报无法打开文件

COPY报could not open file for reading/writing时，第一步不是改文件权限，而是确认你写的路径是服务端的路径还是客户端的路径。

简单判断法：如果你用 psql 在服务器本机执行，那路径就是服务器的绝对路径；如果你用 DBeaver、Navicat 这类远程工具执行COPY，路径还是服务器的路径，和你的笔记本没有任何关系。

想导出到本地，两条路：

• 用\copy，直接从客户端写到本地。
• 用COPY ... TO STDOUT加 shell 重定向，把输出流接管到本地文件。

另外，服务端的路径还有个隐藏问题：PostgreSQL 默认对COPY能读写的目录有限制。即使你是超级用户，如果数据库是 Docker 容器跑的，容器里和宿主机是两个文件系统，写出来的文件在容器里，不是你以为的宿主机位置。遇到这种场景，要么用 docker cp 拷文件，要么用\copy直接绕开。

6.3 权限和对象还原：序列、外键、owner 的坑

整库恢复后业务报错的经典场景是：表都在，数据也在，但插入数据时主键冲突。原因是序列没有跟着数据重置。pg_dump导出的 dump 文件里有时包含setval，有时不包含，取决于导出时的参数和数据状态。恢复完如果发现自增主键有问题，手动重置一下序列：

SELECT setval('users_id_seq', (SELECT max(id) FROM users));

外键的顺序问题则更隐蔽。如果你只导了数据（--data-only），恢复时先导子表、后导父表，就会撞上外键约束。两个解决思路：

• 恢复数据前先禁用外键约束检查：SET session_referential_integrity = off;或ALTER TABLE ... DISABLE TRIGGER ALL;，恢复完再打开。
• 或者用pg_restore恢复自定义格式的 dump，它会根据依赖关系自动排序，这也是我一直推荐-F c的原因。

至于 owner 的问题，常见于跨账号迁移。原来表属于old_user，新库只有new_user。如果 dump 文件里带了OWNER TO old_user，恢复的时候会报错说角色不存在。解决办法就是前面提到的：

pg_dump ... --no-owner --no-privileges

恢复完成后再用ALTER TABLE ... OWNER TO new_user;批量处理即可。

这里我提供一个执行 ALL 语句改 owner 的 SQL，方便批量处理：

SELECT 'ALTER TABLE ' || tablename || ' OWNER TO new_user;' FROM pg_tables WHERE schemaname = 'public';

把所有生成的语句复制出来跑一遍就行。视图、序列、函数也要同样处理，只看表是不够的。

7. 把导入导出变成稳定流程的几个小习惯

迁移这条路我走过几轮，踩坑无数之后，反倒觉得最重要的不是哪条命令多精妙，而是每次操作前有没有一套稳定的流程。这里分享几个我自己的习惯，供你参考。

• 导出前先做一次VACUUM ANALYZE，至少对要导的核心表做一次。目的是让统计信息更新，让COPY的查询计划和文件读取更稳定。注意，VACUUM在大库上可能耗时较长，要安排在维护窗口内。
• 任何一次重要导出，导完先看日志，再快速做抽样验证。比如导出一个 CSV，先wc -l看行数，再head -n 5看前五行，基本能确认导出的文件是否正常。
• 如果是在生产环境恢复数据，恢复前一定要记录好当前生产库的版本、插件列表和数据库参数。比如源库装了 postgis 之类的扩展，目标库没装，恢复时就会因为找不到扩展而失败。提前装好同样版本的扩展，比你恢复时再去一个个补要省心太多。
• 大文件传输后务必校验。除了 count 加哈希，还可以用pg_dump的--strict-checksums参数配合校验和，但那更多用于物理复制，逻辑备份场景下做一次 count 加哈希就够了。

最后说一个我自己用了很久的收尾动作：数据导入完成后，不要急着跑业务让用户试，先自己写几个查询验证外键关联，比如订单表对用户表、明细表对订单表各跑一个LEFT JOIN看看有没有孤儿记录。所谓“导入成功”，不是导入工具不报错，而是业务查询的结果和源库一致。这个习惯帮我挡下过至少三次看起来成功、实际上有损耗的迁移事故。