PostgreSQL COPY 命令：快速数据传输完全指南

PostgreSQL COPY 命令是一种高性能工具，用于在数据库表和文件之间传输数据。与逐条执行 INSERT 语句相比，它显著加快了批量导入和导出的速度，使其成为数据迁移、备份和报告的常用选择。

本指南涵盖 PostgreSQL COPY 命令语法、导入导出数据的实用示例、COPY 与 \copy 的区别以及常见错误的解决方案。

PostgreSQL 中的 COPY 命令是什么？

PostgreSQL COPY 命令是一种高性能工具，用于在数据库表和文件或流之间传输大量数据。与标准的 INSERT 语句不同，它专为批量数据操作而设计，显著降低了逐行处理的开销。

COPY 直接在 PostgreSQL 表和服务器文件系统上的文件或活动客户端流之间移动结构化数据。通过最大限度地减少查询解析和网络开销，它导入或导出数百万行的速度比执行逐条 INSERT 语句快得多。

数据传输的方向取决于您使用的选项：

• COPY ... FROM 将数据从外部文件导入到现有的 PostgreSQL 表中。目标表必须已存在，因为 COPY 不会创建表。
• COPY ... TO 将数据从 PostgreSQL 表或 SQL 查询结果导出到外部文件。它通常用于创建 CSV 文件、生成报告或备份表数据。

PostgreSQL COPY 命令语法

在导入或导出数据之前，了解基本的 PostgreSQL COPY 命令语法和可用选项很重要。了解这些组件的功能有助于防止格式错误并确保数据传输成功。

基本语法

使用以下语法将数据从文件导入到现有的 PostgreSQL 表中：

COPY table_name (column1, column2, column3)
    FROM '/path/to/file.csv'
    [WITH (option [, ...])];    

要将数据从表导出到文件，请将 FROM 替换为 TO：

COPY table_name (column1, column2, column3)
    TO '/path/to/file.csv'
    [WITH (option [, ...])];    

列列表是可选的。如果省略，PostgreSQL 将使用表的默认列顺序将文件数据映射到目标表的每一列。

常用 COPY 选项

您可以通过在 WITH 子句中指定选项来控制 PostgreSQL 导入或导出数据的方式。

• FORMAT：指定文件格式。支持的值有 text、csv 和 binary。默认值为 text。
• HEADER：指示文件包含标题行。导入时，PostgreSQL 跳过第一行。导出时，它将列名写入为第一行。此选项仅适用于 csv 格式。
• DELIMITER：定义用于分隔列的字符。text 格式的默认分隔符是制表符，csv 格式的默认分隔符是逗号。
• NULL：指定表示 NULL 值的字符串。text 格式的默认值为 \N，csv 格式的默认值为不带引号的空字符串。

• QUOTE：指定用于包含分隔符的字段的引号字符。默认值为双引号（”）。此选项仅适用于 csv 格式。

• ESCAPE：指定用于转义特殊字符（包括引号字符）的字符。默认情况下，PostgreSQL 使用与 QUOTE 定义的相同字符。
• ENCODING：指定输入或输出文件的字符编码，如 UTF8 或 LATIN1。如果文件编码与数据库编码不同，PostgreSQL 会自动转换数据。

如何使用 COPY 导出和导入数据

在数据库表和本地文件之间传输数据是数据库管理中最常见的管理任务之一。以下是两个方向的实用示例。

如何使用 COPY 导出数据

使用 COPY 导出允许您将表数据或查询结果写入文件。导出为 CSV 是最常见的用例之一，因为大多数电子表格工具和数据管道都直接接受此格式。

将整个表导出为 CSV

要将表中的所有行导出为带表头的 CSV 文件，请使用 TO 子句并指定 csv 格式。

COPY employees TO '/tmp/employees.csv' WITH (FORMAT csv, HEADER);

导出查询结果

您还可以通过在括号内传递查询来仅导出数据子集。

COPY (SELECT id, name, department FROM employees WHERE salary > 50000) TO '/tmp/high_earners.csv' WITH (FORMAT csv, HEADER);

导出选定列

如果您只需要表中的几个特定字段，请在表名后的括号中列出它们。

COPY employees (id, email) TO '/tmp/emails.csv' WITH (FORMAT csv, HEADER);

导出时不带表头

要导出不带表头行的数据，只需从配置参数中省略 HEADER 选项即可。

COPY employees TO '/tmp/employees_no_header.csv' WITH (FORMAT csv);

如何使用 COPY 导入数据

与客户端工具不同，COPY 要求源文件位于 PostgreSQL 服务器的同一台机器上，因为该命令在服务端运行。目标表的布局应与输入文件的结构对齐。

将 CSV 文件导入到表中

要加载包含标题行的标准逗号分隔文件，请使用 FROM 关键字并启用 header 选项。

COPY employees FROM '/tmp/employees.csv' WITH (FORMAT csv, HEADER);

导入特定列

如果源 CSV 的字段数少于目标表，请显式映射目标列，以便引擎正确匹配数据。

COPY employees (name, department, salary) FROM '/tmp/new_hires.csv' WITH (FORMAT csv, HEADER);

处理 NULL 值

如果您的源文件使用特定的词或短语表示缺失数据，请使用 NULL 选项定义该值。

COPY employees FROM '/tmp/employees_nulls.csv' WITH (FORMAT csv, HEADER, NULL 'N/A');

导入使用不同分隔符的文件

当处理非标准文件（例如使用分号而不是逗号分隔的文件）时，请调整 DELIMITER 参数。

COPY employees FROM '/tmp/employees_semicolon.csv' WITH (FORMAT csv, HEADER, DELIMITER ';');

COPY vs \copy：应该使用哪个？

虽然 COPY 和 \copy 执行类似的任务，但它们在不同的环境中工作。了解服务端执行和客户端执行之间的区别有助于您选择合适的命令并避免常见的文件访问错误。

SQL COPY 命令在 PostgreSQL 服务器上运行，这意味着服务器在其自己的文件系统上读取或写入文件。要使用 COPY 处理服务器文件，您的数据库账户通常需要超级用户权限或 pg_read_server_files 或 pg_write_server_files 等角色。

相比之下，\copy 是 psql 客户端提供的元命令。它在您的本地机器上读取或写入文件，然后将数据流式传输到 PostgreSQL 服务器或从服务器流式传输。由于文件访问由客户端处理，\copy 通常无需提升服务端权限即可工作。

下表总结了两个命令之间的主要区别。

日常任务的 PostgreSQL COPY 命令示例

这些 COPY 命令示例涵盖了数据库管理员和开发人员经常遇到的常见日常场景。

将表备份为 CSV

在运行有风险的更新语句之前，创建特定表的快速备份是一个有用的步骤。此示例将 customers 表保存到服务器上的临时目录中，并包含表头。

COPY customers TO '/tmp/customers_backup.csv' WITH (FORMAT csv, HEADER);

从 CSV 恢复数据

从先前导出的文件恢复表时，先清空目标表有助于避免主键冲突。使用与原始导出操作匹配的选项来正确读取文件。

TRUNCATE TABLE customers;

TRUNCATE TABLE customers;
    COPY customers FROM '/tmp/customers_backup.csv' WITH (FORMAT csv, HEADER);    

在 PostgreSQL 数据库之间移动数据

您可以使用命令行通过 psql 和客户端流式传输，将数据从一个数据库直接传输到另一个数据库。此方法避免将任何中间文件写入磁盘，节省空间和时间。如果两个数据库在不同的服务器上，请添加 -h 标志以指定每个主机。

psql -d source_db -c "\copy orders TO STDOUT" | psql -d target_db -c "\copy orders FROM STDIN"

从 SQL 查询生成报告

数据库报告通常需要筛选数据，而不是导出整个表。您可以将复杂查询包装在括号中，为报告工具导出目标数据集。

COPY (
    SELECT department, COUNT(*), AVG(salary) 
    FROM employees 
    GROUP BY department
) TO '/tmp/department_report.csv' WITH (FORMAT csv, HEADER);

常见 COPY 错误及其修复方法

批量导入和导出可能会因文件访问限制或模式不匹配而遇到意外问题。以下是开发人员最常遇到的问题及其解决步骤。

1. 权限被拒绝

当运行 PostgreSQL 服务的操作系统用户（通常是 postgres 系统用户）对包含文件的目录没有读取或写入权限时，会发生此错误。即使您的个人系统登录具有完全管理权限，数据库进程本身也无法访问该文件。

步骤 1. 将目标文件移动到公共目录，如 /tmp 或 /var/tmp，以便系统用户可以访问它。

步骤 2. 通过调整文件和目录权限来授予数据库引擎读取权限，例如在文件上运行 chmod 644 filename.csv。确保包含的目录也可被 postgres 用户访问。

2. 无法打开文件

当使用服务端 COPY 而不是客户端 \copy 处理本地文件时，通常会出现此数据库消息。如果指定的路径正确但文件位于客户端机器上，服务器无法访问它。

要解决此问题，请将命令从 SQL COPY 切换到 psql 界面中的客户端 \copy 元命令。与 COPY 不同，\copy 在运行 psql 的机器上读取和写入文件，因此它可以访问数据库服务器本身无法看到的文件。

3. 无效输入语法

当输入文件包含的数据不符合数据库列定义时，数据类型不匹配会导致此错误。例如，尝试将文本字符串插入整数列会触发此问题。

检查文件标题以确保列与数据库表布局对齐。如果文件包含空字符串而不是数字，请在导入语句中定义 NULL 选项，以便数据库引擎可以将其转换为空值。

4. 最后预期列后有额外数据

当输入文件中的某行包含的分隔符字段数多于数据库表的列数时，会发生此错误。它通常指向分隔符不匹配，例如当您的文本在标准 CSV 文件中包含原始逗号时。

将源文件中有问题的文本字段用引号括起来。您还应确保明确指定 FORMAT csv 选项，以便引擎尊重引号字符。

5. 编码错误

当输入文件的文本编码与数据库期望不匹配时，会发生编码错误。在设置为 SQL_ASCII 或 LATIN1 的数据库中尝试解析 UTF-8 字符通常会引发此警告。

在导入期间使用 ENCODING 选项声明正确的源文件编码。例如，在命令的选项块中添加 ENCODING 'UTF8'。

使用 i2Backup 自动化 PostgreSQL 备份

手动运行 COPY 适用于一次性导出，但它不能作为长期备份策略扩展。仍然需要有人记住运行命令、将文件安全存储，并在磁盘空间耗尽之前清理旧导出文件。

这就是像 i2Backup 这样的专用备份解决方案发挥作用的地方。i2Backup 不依赖手动 COPY 命令和 cron 作业，而是按计划处理 PostgreSQL 备份，并提供内置的保留和恢复选项。

i2Backup 关键功能

• 实时和计划数据库备份： i2Backup 与其他主流数据库一起保护 PostgreSQL，支持单实例和集群环境。这消除了每次需要备份表时手动触发 COPY 导出的需要。
• 智能清理和保留策略： 自定义保留规则自动删除过时的备份。这解决了手动生成的 CSV 文件在临时目录中堆积的磁盘空间问题。
• 文件级恢复： 可以恢复特定文件或数据库条目，而无需恢复整个数据集。这为您提供了单个 COPY FROM 通常无法实现的目标恢复能力，尤其是当您只需要部分数据时。
• 恢复到任意位置： 备份可以恢复到原始位置或新的数据库主机，并支持跨平台。这在环境之间移动数据时很有帮助，否则该任务需要结合 COPY 和手动格式调整。

对于与其他数据库或虚拟化基础设施一起运行的 PostgreSQL 环境，i2Backup 从单个控制台管理所有内容，而不是为每个系统需要单独的脚本。

除了计划备份之外，具有更严格恢复点要求的企业可以将 i2Backup 与 i2CDP 结合使用，后者以字节级复制变化数据，将 RPO 降低到接近零。对于跨多个服务器管理 PostgreSQL 复制的团队，i2Stream 提供实时数据库和大数据复制作为补充选项。

结论

COPY 命令仍然是进出 PostgreSQL 的最快数据移动方式之一，无论您是为报告导出表还是在模式更改后恢复数据。了解服务端 COPY 和客户端 \copy 之间的区别有助于您避免本指南中涵盖的最常见文件访问错误。

对于常规备份，将手动 COPY 导出与像英方软件的 i2Backup 这样的自动化解决方案相结合，可降低错过备份的风险，并在出现问题时为您提供更快、更有针对性的恢复选项。