pg_bulkload的使用

duqk1982

于 2023-01-30 11:13:22 发布

阅读量621

点赞数

分类专栏： PostgreSQL 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/dqk1982/article/details/128799221

版权

PostgreSQL 专栏收录该内容

20 篇文章 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 安装期间安装。

pg_bulkload 安装

–安装相关依赖
yum -y install postgresqlxx-devel, pam-devel, readline-devel, libedit-devel,“Development Tools”

–root用户
source /home/postgres/.bash_profile
unzip pg_bulkload-VERSION3_1_20.zip
cd pg_bulkload-VERSION3_1_20
make
make install

–创建扩展

[postgres@duqk04 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# create extension pg_bulkload;
CREATE EXTENSION
postgres=# \dx
                                     List of installed extensions
    Name     | Version |   Schema   |                           Description                           
-------------+---------+------------+-----------------------------------------------------------------
 pg_bulkload | 1.0     | public     | pg_bulkload is a high speed data loading utility for PostgreSQL
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# \q
[postgres@duqk04 ~]$  pg_bulkload --version
pg_bulkload 3.1.20
[postgres@duqk04 ~]$

pg_bulkload 参数

[postgres@duqk04 ~]$ 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>.
[postgres@duqk04 ~]$

部份参数说明

1.TYPE = CSV | BINARY | FIXED | FUNCTION ：输入数据的类型，默认是CSV。
CSV : 从 CSV 格式的文本文件加载，默认为CSV
BINARY | FIXED：从固定的二进制文件加载
FUNCTION ：从函数的结果集中加载。如果使用它，INPUT 必须是调用函数的表达式。

2.INPUT|INFILE=path|stdin|function_name：数据源，必须指定，类型不同，它的值不一样
path：此处就是路径，可以是相对路径，pg服务器必须有读文件的权限
stdin：pg_bulkload将从标准输入读取数据。
SQL FUNCTION：指定SQL函数，用这个函数返回插入数据，可以是内建的函数，也可以是用户自定义的函数

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

4.OUTPUT|TABLE=table_name|outfile 输出源，即把数据导到哪里。
表：把数据导入到数据库的表里。
文件：指定文件的路径，把数据导入到文件里。

5.LOGFILE=path 日志文件的路径，执行过程中会记录状态。

6.MULTI_PROCESS=YES|NO 若设置了此值，会开启多线程模式，并行处理数据导入。若没设置，单线程模式，默认模式是单线程模式。

7.ON_DUPLICATE_KEEP = NEW | OLD 对表存在唯一约束是保留最新的记录还是现有的记录

8.DELIMITER = delimiter_character 文件的分隔符

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

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

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

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

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

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

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

注意事项

如果您使用直接加载模式（WRITER=DIRECT 或 PARALLEL），您必须注意以下事项：
1.当 MULTI_PROCESS=YES 并且需要密码才能从 localhost 连接到数据库进行加载时，即使在提示中正确输入密码，身份验证也会失败。为避免这种情况，请配置以下任一项。
a.在 UNIX 中，将以下行添加到 pg_hba.conf。
host all all 127.0.0.1/32 trust
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%。

pg_bulkload 使用

创建测试表和数据

--创建测试库和测试表
[postgres@duqk04 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# create table test (id int,name text);
CREATE TABLE
testdb=# create extension pg_bulkload;
CREATE EXTENSION
testdb=# \q
[postgres@duqk04 ~]$

--创建测试数据（CSV文件）
[postgres@duqk04 ~]$ seq 1000000| awk '{print $0"|test data"}' > bulk_test.txt
[postgres@duqk04 ~]$ du -sh bulk_test.txt
17M     bulk_test.txt
[postgres@duqk04 ~]$ head -1 bulk_test.txt
1|test data
[postgres@duqk04 ~]$

加载数据到指定表

[postgres@duqk04 ~]$ pg_bulkload -i /home/postgres/bulk_test.txt -O public.test  -l /home/postgres/test_output.log -P /home/postgres/test_bad.txt  -o "TYPE=CSV" -o "DELIMITER=|" -d testdb -U postgres  -h 192.168.13.34
Password: 
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        1000000 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@duqk04 ~]$ psql testdb -c "select * from public.test limit 5;"
 id |   name    
----+-----------
  1 | test data
  2 | test data
  3 | test data
  4 | test data
  5 | test data
(5 rows)

[postgres@duqk04 ~]$

--查看导入日志：
[postgres@duqk04 ~]$ cat test_output.log

pg_bulkload 3.1.20 on 2023-01-30 10:35:34.851899+08

INPUT = /home/postgres/bulk_test.txt
PARSE_BADFILE = /home/postgres/test_bad.txt
LOGFILE = /home/postgres/test_output.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
NULL = 
OUTPUT = public.test
MULTI_PROCESS = NO
VERBOSE = NO
WRITER = DIRECT
DUPLICATE_BADFILE = /data/pgdata/pg_bulkload/20230130103534_testdb_public_test.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = NO


  0 Rows skipped.
  1000000 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 2023-01-30 10:35:34.851899+08
Run ended on 2023-01-30 10:35:36.42624+08

CPU 1.40s/0.17u sec elapsed 1.57 sec
[postgres@duqk04 ~]$

先清空再加载

[postgres@duqk04 ~]$ pg_bulkload -i /home/postgres/bulk_test.txt -O public.test  -l /home/postgres/test_output.log -P /home/postgres/test_bad.txt  -o "TYPE=CSV" -o "DELIMITER=|"  -o "TRUNCATE=YES" -d testdb -U postgres  -h 192.168.13.34
Password: 
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        1000000 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@duqk04 ~]$ psql testdb -c "select count(*) from public.test;"                                          
  count  
---------
 1000000
(1 row)

[postgres@duqk04 ~]$ 
---test_output.log日志被追加

使用控制文件

[postgres@duqk04 ~]$ cat test.ctl
INPUT = /home/postgres/bulk_test.txt
PARSE_BADFILE = /home/postgres/test_bad.txt
LOGFILE = /home/postgres/test_output.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
OUTPUT = public.test
MULTI_PROCESS = NO
VERBOSE = NO
WRITER = DIRECT
DUPLICATE_BADFILE = /data/pgdata/pg_bulkload/20230130104142_testdb_public_test.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = YES
[postgres@duqk04 ~]$ 
[postgres@duqk04 ~]$ pg_bulkload  /home/postgres/test.ctl -d testdb -U postgres  -h 192.168.13.34
Password: 
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        1000000 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@duqk04 ~]$ psql testdb -c "select count(*) from public.test;"
  count  
---------
 1000000
(1 row)

[postgres@duqk04 ~]$

强制写wal 日志

pg_bulkload 默认是跳过buffer 直接写文件，但时如果有复制，或者需要基本wal日志恢复时没有wal日志是不行的，这是我们可以强制让其写wal日志，只需要加载 -o “WRITER=BUFFERED” 参数就可以了

[postgres@duqk04 ~]$ pg_bulkload -i /home/postgres/bulk_test.txt -O public.test  -l /home/postgres/test_output.log -P /home/postgres/test_bad.txt  -o "TYPE=CSV" -o "DELIMITER=|"  -o "TRUNCATE=YES" -o "WRITER=BUFFERED" -d testdb -U postgres  -h 192.168.13.34
Password: 
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        1000000 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@duqk04 ~]$
[postgres@duqk04 ~]$ psql testdb -c "select count(*) from public.test;"
  count  
---------
 1000000
(1 row)

[postgres@duqk04 ~]$