type
status
date
slug
summary
tags
category
icon
password
能力模型
能力分类
省赛
全球总决赛
学习进度
中国总决赛
pg_bulkload is a high speed data loading utility for PostgreSQL.
1 pg_bulkload 概述
1.1 pg_bulkload介绍
pg_bulkload是一种用于PostgreSQL的高速数据加载工具，用于大量数据加载，为用户提供了高速的数据载入能力，绕过了wal buffer和shared buffer，所以在效率上比copy更高。
重要提示：在流复制环境下，pg_bulkload 无法正常工作。详情 请参阅此处。
相关描述：
- pg_bulkload 旨在将大量数据加载到数据库中。您可以选择是否检查数据库约束以及在加载过程中忽略多少错误。例如，当您将数据从另一个数据库复制到 PostgreSQL 时，您可以跳过完整性检查以提高性能。另一方面，您可以在加载不干净的数据时启用约束检查。
- pg_bulkload 最初的目标是成为
COPYPostgreSQL 中命令的更快替代者，但 3.0 或更高版本具有一些 ETL 功能，例如输入数据验证和带有过滤功能的数据转换。
- 在 3.1 版本中，pg_bulkload 可以将加载数据转换为二进制文件，该文件可用作 pg_bulkload 的输入文件。如果在将加载数据转换为二进制文件时检查其是否有效，则可以在将其从二进制文件加载到表时跳过检查。这将减少加载时间本身。此外，在 3.1 版本中，并行加载比以前更有效。
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 的性能，以适应不同的数据加载需求和环境。📎 参考文章
对于项目安装问题，德哥后来也写了详细的博客，只想看使用的同学可以直接进行我上面的操作，想要了解更加详细的报错解决的同学可以参考下面两篇博客。
备注：德哥这里参数补全改的是true，实际上应该为false，否则后续数据库操作会产生异常，这里他在第二篇中也给出了详细的说明。如下。
有关问题，欢迎您在底部评论区留言，一起交流~
- Author:Koreyoshi
- URL:https://Koreyoshi1216.com/article/129c7b13-c6a7-8005-84f7-cc44b9b1c54b
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!