pg_bulkload 插件概述

type

status

date

slug

summary

1 pg_bulkload 概述

1.1 pg_bulkload介绍

pg_bulkload是一种用于PostgreSQL的高速数据加载工具，用于大量数据加载，为用户提供了高速的数据载入能力，绕过了wal buffer和shared buffer，所以在效率上比copy更高。

重要提示：在流复制环境下，pg_bulkload 无法正常工作。详情请参阅此处。

1.2 与copy的性能对比

从 COPY 和 pg_bulkload 加载数据的性能对比来看，WRITER = PARALLEL 模式下的 Pg_bulkload 的数据加载效果几乎是COPY一倍。另外没有索引的COPY并不比有索引的COPY快。因为它必须从初始开始为表的总记录创建索引。

WRITER = PARALLEL 模式是 Pg_bulkload 中的一种并行数据加载模式。它允许同时启动多个数据写入进程，从而提高数据加载的效率。通过并行化的方式，可以充分利用系统的多核 CPU 和 I/O 能力，显著加快数据导入速度。

补充说明：

1.PostgreSQL 参数 maintenance_work_mem 会影响 pg_bulkload 的性能。如果将此参数从 64 MB 更改为 1 GB，则持续时间将缩短近 15%。

在 PostgreSQL 中，maintenance_work_mem 参数用于设置维护操作期间使用的内存量。通过增加 maintenance_work_mem 的值，可以提高维护操作的速度，尤其是在处理大表时。然而，过高的值可能会导致系统的内存使用过载，因此需要根据具体的工作负载和系统资源进行调整。

2.FILTER 功能在各种操作中转换输入数据，但它不是免费的。实际测量显示，SQL 函数的加载时间增加了近 240%，而 C 函数的加载时间增加了近 140%。

在 PostgreSQL 中，FILTER 是用于聚合函数的一个功能，可以让你在执行聚合操作时对数据进行条件过滤。这个功能允许你在聚合过程中指定一个条件，仅对满足该条件的数据进行聚合计算。

2 pg_bulkload架构

2.1 架构图

pg_bulkload主要包括两个模块：reader和writer。reader负责读取文件、解析tuple，writer负责把解析出的tuple写入输出源中。pg_bulkload最初的版本功能很简单，只是加载数据。3.1版本增加了数据过滤的功能。

导入：只支持导入binary和csv文件，而copy除以上两种外，还支持text格式

导出：只支持通过function导出binary文件，不知道为什么不支持像copy一样支持schma+table的形式

Reader（读取器）：

CSV: 从CSV文件中读取数据。
Binary: 从二进制文件中读取数据。
Function: 通过函数来获取数据。

Parser（解析器）：

Encoding Check & Convert: 检查并转换编码，确保数据在后续处理过程中能够正确解读和使用。
Filter: 对数据进行过滤，去除不需要的数据或对数据进行预处理。

Constraints Checker（约束检查器）：

在数据进入下一步之前，检查数据是否满足某些特定的约束条件。这一步可以确保数据的质量和一致性。

Writer（写入器）：

Direct: 直接将数据写入目标位置。
Parallel: 并行地将数据写入多个目标位置。
Buffered: 将数据先缓存起来，然后再批量写入目标位置。

2.2 主要程序构成

pg_bulkload 加载数据时，在内部它调用PostgreSQL 的用户定义函数 pg_bulkload() 并执行加载。pg_bulkload() 函数将在 pg_bulkload 安装期间安装。

postgresql 脚本

这是包装命令pg_ctl，用于启动和停止 PostgreSQL 服务器。postgresql 脚本pg_ctl在内部调用。postgresql 脚本提供了非常重要的 pg_bulkload 功能，即恢复。为了提高性能，pg_bulkload 绕过了 PostgreSQL 的一些内部功能，例如 WAL。因此，在执行常规 PostgreSQL 恢复之前，pg_bulkload 需要提供单独的恢复过程。postgresql 脚本提供了此功能。

您必须看到下面的“限制”，特别是如果您在 DIRECT 或 PARALLEL 加载模式下使用 pg_bulkload。它需要特殊的数据库恢复过程。请注意，DIRECT 模式是默认设置。

pg_bulkload

此程序用于加载数据。在内部，它调用 PostgreSQL 的用户定义函数 pg_bulkload() 并执行加载。pg_bulkload() 函数将在 pg_bulkload 安装期间安装。

您可以通过以下三个步骤使用 pg_bulklad：

编辑控制文件 "sample_csv.ctl" 或 "sample_bin.ctl"，其中包含数据加载的设置。你可以指定表名、输入文件的绝对路径、输入文件的描述等等。

假设有一个目录 $PGDATA/pg_bulkload，在那里会创建加载状态文件。

执行命令时，将控制文件作为参数。参数可以使用相对路径。

2.3 控制文件

在使用pg_bulkload时可以指定以下加载选项。控制文件可以用绝对路径或相对路径来指定。如果您通过相对路径指定它，它将相对于执行 pg_bulkload 命令的当前工作目录。如果你没有指定控制文件，你应该通过 pg_bulkload 的命令行参数传递所需的选项。

关于parallel参数:

非并行模式下，数据串行处理，reader通过syncsource读取csv/binary文件，解析后组成一条元组发送给writer，writer写入文件。

开启并行参数后，reader会创建一个asyncsource，asyncsource有一个后台线程在读取文件。从csv/binary读到的文件放到buffer中，reader拷贝到自己的buffer中，解析一行数据，组成tuple，放到共享内存中。在创建parallel 的writer的时候，会通过writer的类型，发送一条sql(WriterSendQuery)，创建一个新的backend(input类型为tuple)，新的backend从共享内存读取reader写入的元组，进行真正的写入。

2.4 退出代码

成功加载时，pg_bulkload 返回 0。当存在一些解析错误或重复错误时，即使加载本身已完成，它也会返回 3 和 WARNING 消息。请注意，跳过的行和替换的行（ON_DUPLICATE_KEEP = NEW）不被视为错误；退出代码将为 0。

当出现不可持续的错误时，加载程序会引发 ERROR 消息。返回码通常为 1，因为在加载数据期间数据库服务器中发生了许多错误。下表显示了 pg_bulkload 可以返回的代码。

返回码	描述
0	成功
1	在 PostgreSQL 中运行 SQL 时发生错误
2	无法连接到 PostgreSQL
3	成功，但有些数据无法加载

3 pg_bulkload 安装

3.1 依赖包

要求：

必须提前安装 PostgreSQL，

数据库已使用初始化initdb。

3.2 安装pg_bulkload

直接安装

使用PGXS来构建pg_bulkload

从 RPM 包安装

在安装 pg_bulkload 之前，请检查 postgresql-server 是否已安装。

以下命令将安装 pg_bulkload：

您可以使用以下命令检查模块是否已安装：

您可以找到文件的安装位置：

最后，将函数注册到数据库。脚本 pg_bulkload.sql 位于 rpm -qs 显示的路径中。以下示例中改用 $PGSHARE：

您还可以使用 CREATE EXTENSION 来注册函数（必须是数据库超级用户）。

3.3 pg_bulkload参数

4 pg_bulkload使用

4.1 初始化数据

5.2 加载到指定表

5.3 先清空在加载

5.4 使用控制文件导入数据

新建控制文件：

可以根据之前加载时，产生的日志文件来更改，去掉里面没有值的参数

5.5 强制写wal 日志

5.6 开启并行

5 其他

5.1 注意事项

如果您使用直接加载模式（WRITER=DIRECT 或 PARALLEL），则必须注意以下几点：

1.当 MULTI_PROCESS=YES 并且需要密码才能从 localhost 连接到数据库进行加载时，即使在提示中正确输入密码，身份验证也会失败。为避免这种情况，请配置以下任一项。

a.在 UNIX 中，将以下行添加到 pg_hba.conf。

b.在 .pgpass 文件中指定密码

127.0.0.1::foo:foopass

c.不要使用“WRITER=PARALLE”

2.PITR/Replication ：由于绕过了 WAL，PITR 的归档恢复不可用。这并不意味着它可以在没有加载表数据的情况下完成 PITR。如果您想使用 PITR，请在通过 pg_bulkload 加载后对数据库进行完整备份。如果您使用流式复制，则需要根据 pg_bulkload 之后的备份集重新创建备用数据库。

3.不得删除在 $PGDATA/pg_bulkload目录中找到的加载状态文件 (.loadstatus)。在 pg_bulkload 崩溃恢复中需要这个文件。

4.尽量不要使用 " kill -9" 终止 pg_bulkload 命令。如果您这样做了，您必须调用 postgresql 脚本来执行 pg_bulkload 恢复并重新启动 PostgreSQL 以继续。

5.默认情况下，在数据加载期间仅强制执行唯一约束和非空约束。您可以设置“CHECK_CONSTRAINTS=YES”来检查 CHECK 约束。无法检查外键约束。用户有责任提供有效的数据集。

6. maintenance_work_mem会影响 pg_bulkload 的性能。如果将此参数从 64 MB 更改为 1 GB，则持续时间将缩短近 15%。

5.2 编辑过滤器函数

编写 FILTER 函数时有一些注意事项和警告：

输入文件中的记录被逐一传递给 FILTER 函数。

当FILTER函数出现错误时，传递的记录不会被加载并写入PARSE BADFILE。

FILTER 函数必须返回记录

类型或某种复合类型。此外，实际记录类型必须与目标表定义匹配。

如果 FILTER 函数返回 NULL，则会加载所有列均为 NULL 的记录。

支持带有默认参数的函数。如果输入数据的列数少于函数的参数，则将使用默认值。

不支持 VARIADIC 函数。

不支持 SETOF 函数。

不支持具有泛型类型（any、anyelement 等）的函数。

FILTER 函数可以用任何语言实现。SQL、C、PL 都可以，但是你应该尽可能快地编写函数，因为它们会被多次调用。

您只能指定 FILTER 或 FORCE_NOT_NULL 选项之一。如果您需要该功能，请重新实现与 FORCE_NOT_NULL 兼容的 FILTER 函数。

6 Polardb附加pg_bulkload插件

6.1 插件安装

根据插件修改导入脚本,该部分要注意tpch_copy.sh的数据路径问题，否则会报错（当前插件关闭WAL(Write Ahead Log)预写日志，WAL是数据库系统中常见的一种手段，用于保证数据操作的原子性和持久性。）

6.2 开启并行（试错中）

6.3 参数优化（试错中）

pg_bulkload 是一个用于高效加载大量数据到 PostgreSQL 数据库的工具。以下是一些关键参数，你可以调整这些参数以优化 pg_bulkload 的性能：

1. `b` 或 `-batch-size`

指定批量加载的数据行数。增加批量大小可以减少事务提交的次数，从而提高导入速度。

2. `o` 或 `-order-by`

指定用于排序的列。pg_bulkload 会根据这些列对数据进行排序，以优化数据的物理存储。

3. `r` 或 `-rate-limit`

限制导入速率（每秒行数）。这有助于控制导入过程中的负载，避免对系统资源的过度使用。

4. `S` 或 `-use-sort`

使用系统的排序工具（如 sort 命令）来预排序数据。这可以提高数据加载的效率，特别是对于大型数据集。

5. `T` 或 `-temp-file`

指定临时文件的目录。pg_bulkload 在导入数据前会创建临时文件，指定一个快速的存储设备可以提高性能。

6. `P` 或 `-progress`

显示进度信息。这对于监控长时间运行的导入任务非常有用。

7. `C` 或 `-commit-interval`

指定自动提交的间隔（以行数计）。这有助于平衡事务的开销和性能。

8. `R` 或 `-remote`

指定远程服务器的地址和端口，用于远程数据加载。

9. `D` 或 `-data-format`

指定数据文件的格式（如 CSV、Text 等）。

10. `K` 或 `-no-conv`

不执行任何字符集转换，这可以减少转换过程中的开销。

注意事项

在使用 pg_bulkload 之前，确保目标表已经创建，并且列与数据文件中的列相匹配。

调整参数时，需要根据具体的硬件配置、网络状况和数据特性进行测试，以找到最佳的配置。

某些参数可能需要根据 PostgreSQL 的版本进行调整或替换。

通过调整这些参数，你可以优化 pg_bulkload 的性能，以适应不同的数据加载需求和环境。

📎 参考文章

项目源码

pg_bulkload 数据加载使用及示例

pg_bulkload 3.1

pg_bulkload性能分析

pg_bulkload使用

pg_bulkload 项目使用教程

德哥也测过这个插件hh

对于项目安装问题，德哥后来也写了详细的博客，只想看使用的同学可以直接进行我上面的操作，想要了解更加详细的报错解决的同学可以参考下面两篇博客。

PolarDB 100 问 | PolarDB 11 编译 pg_bulkload 插件报错

备注：德哥这里参数补全改的是true，实际上应该为false，否则后续数据库操作会产生异常，这里他在第二篇中也给出了详细的说明。如下。

PolarDB 100 问 | PolarDB 11 使用 pg_bulkload 插件导入数据报错

💡

有关问题，欢迎您在底部评论区留言，一起交流~