4.pg_bulkload 数据加载使用及示例

1.pg_bulkload 概述

1.1 pg_bulkload 介绍

pg_bulkload是一种用于PostgreSQL的高速数据加载工具，相比copy命令。最大的优势就是速度。优势在让我们跳过shared buffer,wal buffer。直接写文件。pg_bulkload的direct模式就是这种思路来实现的，它还包含了数据恢复功能，即导入失败的话，需要恢复。
pg_bulkload 旨在将大量数据加载到数据库中。您可以选择是否检查数据库约束以及在加载期间忽略多少错误。例如，当您将数据从另一个数据库复制到 PostgreSQL 时，您可以跳过性能完整性检查。另一方面，您可以在加载不干净的数据时启用约束检查。

pg_bulkload 的最初目标是COPY在 PostgreSQL 中更快地替代命令，但 3.0 或更高版本具有一些 ETL 功能，例如输入数据验证和具有过滤功能的数据转换。
在 3.1 版本中，pg_bulkload 可以将加载数据转换成二进制文件，作为 pg_bulkload 的输入文件。如果在将加载数据转换成二进制文件时检查加载数据是否有效，从二进制文件加载到表时可以跳过检查。这将减少加载时间本身。同样在 3.1 版中，并行加载比以前更有效。

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

1.2 性能对比

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

另外:
1.PostgreSQL 参数 maintenance_work_mem 会影响 pg_bulkload 的性能。如果将此参数从 64 MB 更改为 1 GB，则持续时间将缩短近 15%。
2.FILTER 功能在各种操作中转换输入数据，但它不是免费的。实际测量显示，SQL 函数的加载时间增加了近 240%，而 C 函数的加载时间增加了近 140%。

2.pg_bulkload架构图

2.1 架构图

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

2.2 控制文件参数

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

部份参数说明
#TYPE = CSV | BINARY | FIXED | FUNCTION  
CSV : 从 CSV 格式的文本文件加载 ， 默认为CSV 
BINARY | FIXED：从固定的二进制文件加载
FUNCTION ：从函数的结果集中加载。如果使用它，INPUT 必须是调用函数的表达式。   

# WRITER | LOADER = DIRECT | BUFFERED | BINARY | PARALLEL
DIRECT ：将数据直接加载到表中。绕过共享缓冲区并跳过 WAL 日志记录，但需要自己的恢复过程。这是默认的，也是原始旧版本的模式。
BUFFERED：通过共享缓冲区将数据加载到表中。使用共享缓冲区，写入WAL，并使用原始 PostgreSQL WAL 恢复。
BINARY ：将数据转换为二进制文件，该文件可用作要从中加载的输入文件。创建加载输出二进制文件所需的控制文件样本。此示例文件创建在与二进制文件相同的目录中，其名称为 <binary-file-name>.ctl。
PARALLEL：与“WRITER=DIRECT”和“MULTI_PROCESS=YES”相同。如果指定了 PARALLEL，则忽略MULTI_PROCESS。如果为要加载的数据库配置了密码验证，则必须设置密码文件。

# TRUNCATE = YES | NO
         如果YES，则使用 TRUNCATE 命令从目标表中删除所有行。如果NO，什么也不做。默认为NO。您不能同时指定“WRITER=BINARY”和 TRUNCATE。

# CHECK_CONSTRAINTS = YES | NO
        指定在加载期间是否检查 CHECK 约束。默认为否。您不能同时指定“WRITER=BINARY”和 CHECK_CONSTRAINTS。

# PARSE_ERRORS = n
        在解析、编码检查、编码转换、FILTER 函数、CHECK 约束检查、NOT NULL 检查或数据类型转换期间引发错误的 ingored 元组的数量。无效的输入元组不会加载并记录在 PARSE BADFILE 中。默认值为 0。如果解析错误数等于或多于该值，则提交已加载的数据并且不加载剩余的元组。0 表示不允许错误，-1 和 INFINITE 表示忽略所有错误。

# SKIP | OFFSET = n
跳过输入行的数量。默认值为 0。您不能同时指定“TYPE=FUNCTION”和 SKIP。

# LIMIT | LOAD = n
要加载的行数。默认值为INFINITE，即将加载所有数据。即使您使用 TYPE=FUNCTION，此选项也可用。

#ENCODING = encoding
    指定输入数据的编码。检查指定的编码是否有效，如果需要，将输入数据转换为数据库编码。默认情况下，输入数据的编码既不验证也不转换。

#FILTER = [ schema_name. ] function_name [ (argtype, ... ) ]
    指定过滤函数以转换输入文件中的每一行。只要函数名在数据库中是唯一的，就可以省略 argtype 的定义。如果未指定，则直接将输入数据解析为加载目标表。另请参阅如何编写 FILTER 函数以生成 FILTER 函数。
    不能同时指定“TYPE=FUNCTION”和 FILTER。此外，CSV 选项中的 FORCE_NOT_NULL 不能与 FILTER 选项一起使用。

2.3 pg_bulkload状态结果

成功加载时，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 依赖包

安装pg_bulkload  需要以下依赖包，一般在安装好PG数据库的环境中，已安装好相关包，所以我们可以直接安装就可以。
PostgreSQL devel package : postgresqlxx-devel(RHEL), postgresql-server-dev-x.x(Ubuntu)
PAM devel package : pam-devel(RHEL), libpam-devel(Ubuntu)
Readline devel or libedit devel package : readline-devel or libedit-devel(RHEL), libreadline-dev or libedit-dev(Ubuntu)
C compiler and build utility : "Development Tools" (RHEL), build-essential(Ubuntu)

3.2 安装pg_bulkload

# 下载连接 <a href="https://github.com/ossc-db/pg_bulkload" style="text-decoration-skip-ink: none; font-family: &quot;Microsoft YaHei&quot;, Lato, &quot;PingFang SC&quot;, sans-serif;">https://github.com/ossc-db/pg_bulkload</a> 
postgres@s2ahumysqlpg01-> unzip pg_bulkload-VERSION3_1_19.zip 
postgres@s2ahumysqlpg01-> cd pg_bulkload-VERSION3_1_19
postgres@s2ahumysqlpg01-> make
postgres@s2ahumysqlpg01-> make install

# 创建扩展 ，要使用它需要建extension
postgres@s2ahumysqlpg01-> psql -h 127.0.0.1 
psql (12.4)
Type "help" for help.

lottu=# create extension pg_bulkload;
CREATE EXTENSION

#检查已安装插件
postgres=# \dx

4. pg_bulkload 参数

postgres@s2ahumysqlpg01-> pg_bulkload --help
pg_bulkload is a bulk data loading tool for PostgreSQL

Usage:
  Dataload: pg_bulkload [dataload options] control_file_path
  Recovery: pg_bulkload -r [-D DATADIR]

Dataload options:
  -i, --input=INPUT                INPUT path or function
  -O, --output=OUTPUT        OUTPUT path or table
  -l, --logfile=LOGFILE           LOGFILE path
  -P, --parse-badfile=*          PARSE_BADFILE path
  -u, --duplicate-badfile=*    DUPLICATE_BADFILE path
  -o, --option="key=val"        additional option

Recovery options:
  -r, --recovery            execute recovery
  -D, --pgdata=DATADIR      database directory

Connection options:
  -d, --dbname=DBNAME         database to connect
  -h, --host=HOSTNAME           database server host or socket directory
  -p, --port=PORT                      database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password                   never prompt for password
  -W, --password                        force password prompt

Generic options:
  -e, --echo                     echo queries
  -E, --elevel=LEVEL        set output message level
  --help                       show this help, then exit
  --version                 output version information, then exit

Read the website for details. <http://github.com/ossc-db/pg_bulkload>
Report bugs to <http://github.com/ossc-db/pg_bulkload/issues>.

5. pg_bulkload 使用

5.1 初始化数据

postgres@s2ahumysqlpg01-> psql -h 127.0.0.1
psql (12.4)
Type "help" for help.

postgres=# create database testdb ;
CREATE DATABASE
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# 
testdb=# create table tb_asher (id int,name text);
CREATE TABLE
testdb=# \d 
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | tb_asher | table | postgres
(1 row)

testdb=# create extension pg_bulkload;  #如果连接指定到单个库时，需要创建扩展以生成 pgbulkload.pg_bulkload() 函数
CREATE EXTENSION

test=# quit

# 模拟CSV 文件 
postgres@s2ahumysqlpg01-> seq 100000| awk '{print $0"|asher"}' > bulk_asher.txt
postgres@s2ahumysqlpg01-> more   bulk_asher.txt
1|asher
2|asher
3|asher
4|asher
5|asher
6|asher

5.2 加载到指定表

# 将bulk_asher.txt里的数据加载到testdb 库下的 tb_asher表中
postgres@s2ahumysqlpg01->  pg_bulkload -i /home/postgres/bulk_asher.txt -O tb_asher  -l /home/postgres/tb_asher_output.log -P /home/postgres/tb_asher_bad.txt  -o "TYPE=CSV" -o "DELIMITER=|" -d testdb -U postgres  -h 127.0.0.1
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        100000 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

# 查看导入日志：
postgres@s2ahumysqlpg01-> cat /home/postgres/tb_asher_output.log 

pg_bulkload 3.1.19 on 2022-02-20 16:32:28.642071+08

INPUT = /home/postgres/bulk_asher.txt
PARSE_BADFILE = /home/postgres/tb_asher_bad.txt
LOGFILE = /home/postgres/tb_asher_output.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
NULL = 
OUTPUT = public.tb_asher
MULTI_PROCESS = NO
VERBOSE = NO
WRITER = DIRECT
DUPLICATE_BADFILE = /u01/postgresql/data_bak/pg_bulkload/20220220163228_testdb_public_tb_asher.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = NO


  0 Rows skipped.
  100000 Rows successfully loaded.
  0 Rows not loaded due to parse errors.
  0 Rows not loaded due to duplicate errors.
  0 Rows replaced with new rows.

Run began on 2022-02-20 16:32:28.642071+08
Run ended on 2022-02-20 16:32:28.743741+08

CPU 0.02s/0.04u sec elapsed 0.10 sec

5.3 先清空在加载

#增加了 -o "TRUNCATE=YES" 参数
postgres@s2ahumysqlpg01-> pg_bulkload -i /home/postgres/bulk_asher.txt -O tb_asher  -l /home/postgres/tb_asher_output.log -P /home/postgres/tb_asher_bad.txt  -o "TYPE=CSV" -o "DELIMITER=|" -o "TRUNCATE=YES"  -d testdb -U postgres -h 127.0.0.1
4NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        100000 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

# 数据查询 
postgres@s2ahumysqlpg01-> psql -h127.0.0.1 -d testdb  -c "select count(1) from tb_asher ;"
 count  
--------
 100000
(1 row)

5.4 使用控制文件

# 新建控制文件 ，可以根据之前加载时，产生的日志文件tb_asher_output.log来更改，去掉里面没有值的参数  NULL = 
vi  asher.ctl 
INPUT = /home/postgres/bulk_asher.txt
PARSE_BADFILE = /home/postgres/tb_asher_bad.txt
LOGFILE = /home/postgres/tb_asher_output.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
OUTPUT = public.tb_asher
MULTI_PROCESS = NO
VERBOSE = NO
WRITER = DIRECT
DUPLICATE_BADFILE = /u01/postgresql/data_bak/pg_bulkload/20220220164822_testdb_public_tb_asher.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = YES

# 使用控制文件来加载
postgres@s2ahumysqlpg01-> pg_bulkload  /home/postgres/asher.ctl -d testdb -U postgres -h 127.0.0.1
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        100000 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

# 数据查询 
postgres@s2ahumysqlpg01-> psql -h127.0.0.1 -d testdb  -c "select count(1) from tb_asher ;"
 count  
--------
 100000
(1 row)

5.5 强制写wal 日志

# pg_bulkload 默认是跳过buffer 直接写文件 ，但时如果有复制 ，或者需要基本wal日志恢复时没有wal日志是不行的，这是我们可以强制让其写wal日志 ，只需要加载 -o "WRITER=BUFFERED" 参数就可以了
 pg_bulkload -i /home/postgres/bulk_asher.txt -O tb_asher  -l /home/postgres/tb_asher_output.log -P /home/postgres/tb_asher_bad.txt  -o "TYPE=CSV" -o "DELIMITER=|" -o "TRUNCATE=YES" -o "WRITER=BUFFERED"  -d testdb -U postgres -h 127.0.0.1

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        100000 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

6.其它

6.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%。

6.2 编辑过滤器函数

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

1.输入文件中的记录被一一传递给 FILTER 函数。
2.当 FILTER 函数发生错误时，传递的记录不会加载并写入 PARSE BADFILE。
3.FILTER 函数必须返回记录类型或某种复合类型。此外，实际记录类型必须与目标表定义匹配。
4.如果 FILTER 函数返回 NULL，则加载所有列中都有 NULL 的记录。
5.支持具有默认参数的函数。如果输入数据的列数少于函数的参数，则将使用默认值。
6.不支持 VARIADIC 函数。
7.不支持 SETOF 函数。
8.不支持具有泛型类型（any、anyelement 等）的函数。
9.FILTER 函数可以用任何语言实现。SQL、C、PL 都可以，但你应该尽可能快地编写函数，因为它们会被多次调用。
10.您只能指定 FILTER 或 FORCE_NOT_NULL 选项之一。如果您需要该功能，请重新实现与 FORCE_NOT_NULL 兼容的 FILTER 功能。

# Filter 示例：
CREATE FUNCTION sample_filter(integer, text, text, real DEFAULT 0.05) RETURNS record
    AS $$ SELECT $1 * $4, upper($3) $$
    LANGUAGE SQL;

7.参考连接

https://github.com/ossc-db/pg_bulkload
http://ossc-db.github.io/pg_bulkload/index.html
http://ossc-db.github.io/pg_bulkload/pg_bulkload.html

来自为知笔记(Wiz)