数据加载工具pg_bulkload插件的介绍

瀚高数据库
目录
环境
文档用途
详细信息

环境
系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：12
文档用途
本文档主要介绍pg_bulkload插件的安装与使用。

详细信息
研发公司：NTT OSS Center DBMS Development and Support Team，与pg_rman出自相同开发者

license协议：BSD

一、pg_bulkload安装

有从源码安装和rpm安装两种方式，这里仅采用README.md中给出的源码安装。

1.安装前提：提前安装好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)

2.安装步骤：

1）下载安装包：

git clone https://github.com/ossc-db/pg_bulkload.git[postgres@pg-bulkload opt]$ lltotal 19896drwxr-xr-x 11 postgres postgres     4096 Oct 27 15:28 pg_bulkload

2）进入目录并编译安装：

cd pg_bulkloadmakemake install 

注意：编译时需要读取pg_config来获取pg的环境变量，使用root编译时需要环境变量里配上能找到pg_config的路径，也可以将目录的所有者更改为postgres再进行安装。

3）创建extension：

[postgres@pg-bulkload pg_bulkload]$ psql
psql (12.2)Type "help" for help.postgres=# CREATE EXTENSION pg_bulkload;CREATE EXTENSION

二、pg_bulkload使用

pg_bulkload有两种导入数据的方法，通过参数导入和通过控制文件导入。

创建要导入的测试数据

seq 100000| awk '{print $0"|postgres"}' > tbl_test_output.txt

文件内容

[postgres@pg-bulkload pg_bulkload]$ cat tbl_test_output.txt1|postgres2|postgres3|postgres4|postgres......99998|postgres99999|postgres100000|postgres

1.使用参数方式导入

1）创建测试表

postgres=# create table tbl_test(id int,name text);CREATE TABLE

2）导入命令语法

pg_bulkload -i [源数据] -O [目标表] -l [输出日志] -o [控制文件中的选项] -o [控制文件中的选项]-i 输入从中加载数据的源。与控制文件中的“ INPUT ”相同。-O 输出将数据加载到的目标。与控制文件中的“ OUTPUT ”相同。-l日志文件写入结果日志的路径。与控制文件中的“ LOGFILE ”相同。-P PARSE_BADFILE写入无法正确解析的坏记录的路径。与控制文件中的“ PARSE_BADFILE ”相同。-u DUPLICATE_BADFILE在索引重建期间写入与唯一约束冲突的坏记录的路径。与控制文件中的“ DUPLICATE_BADFILE ”相同。-o "key=val"控制文件中可用的任何选项，可传递多个选项。-d 数据库名指定要连接的数据库的名称。如果未指定，则从环境变量PGDATABASE中读取数据库名称。如果未设置，则使用为连接指定的用户名。-h 主机指定运行服务器的机器的主机名。如果该值以斜杠开头，则它用作Unix域套接字的目录。-p 端口指定服务器正在侦听连接的TCP端口或本地Unix域套接字文件扩展名。-U 用户名要连接的用户名。-W 密码强制pg_bulkload在连接到数据库之前提示输入密码。这个选项不是必需的，因为如果服务器要求密码认证，pg_bulkload会自动提示输入密码。但是，vacuumdb会浪费一次连接尝试服务器是否需要密码。在某些情况下，输入-W可以避免额外的连接尝试。-e发送到服务器的回显命令。-E从 DEBUG、INFO、NOTICE、WARNING、ERROR、LOG、FATAL 和PANIC中选择输出消息级别。默认值为INFO。

3）执行导入命令

[postgres@pg-bulkload pg_bulkload]$ pg_bulkload -i /usr/local/pgsql/data/pg_bulkload/tbl_test_output.txt -O tbl_test -l /usr/local/pgsql/data/pg_bulkload/tbl_test_output.log -o "TYPE=CSV" -o "DELIMITER=|"NOTICE: BULK LOAD STARTNOTICE: BULK LOAD END0 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.

4）查看日志文件

[postgres@pg-bulkload pg_bulkload]$ cat tbl_test_output.logpg_bulkload 3.1.19 on 2021-10-27 16:25:42.045267+08INPUT = /usr/local/pgsql/data/pg_bulkload/tbl_test_output.txtPARSE_BADFILE = /usr/local/pgsql/data/pg_bulkload/20211027162542_postgres_public_tbl_test.prs.txtLOGFILE = /usr/local/pgsql/data/pg_bulkload/tbl_test_output.logLIMIT = INFINITEPARSE_ERRORS = 0CHECK_CONSTRAINTS = NOTYPE = CSVSKIP = 0DELIMITER = |QUOTE = "\""ESCAPE = "\""NULL =OUTPUT = public.tbl_testMULTI_PROCESS = NOVERBOSE = NOWRITER = DIRECTDUPLICATE_BADFILE = /usr/local/pgsql/data/pg_bulkload/20211027162542_postgres_public_tbl_test.dup.csvDUPLICATE_ERRORS = 0ON_DUPLICATE_KEEP = NEWTRUNCATE = NO0 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 2021-10-27 16:25:42.045267+08Run ended on 2021-10-27 16:25:42.098395+08CPU 0.00s/0.04u sec elapsed 0.05 sec

2.使用控制文件方式导入（csv格式）

1）创建控制文件

[postgres@pg-bulkload pg_bulkload]$ touch test1.ctl

2）控制文件内容

INPUT = /usr/local/pgsql/data/pg_bulkload/tbl_test_output.txt#要从中加载数据的源PARSE_BADFILE = /usr/local/pgsql/data/pg_bulkload/test1_bad.txt#写入无法正确解析的坏记录的路径LOGFILE = /usr/local/pgsql/data/pg_bulkload/test1_output.log#写入结果日志的路径LIMIT = INFINITE#要加载的行数PARSE_ERRORS = 0#在分析、编码检查、编码转换、筛选函数、检查约束检查、非空检查或数据类型转换期间引发错误的传入元组数。CHECK_CONSTRAINTS = NO#指定在加载过程中是否检查CHECK约束TYPE = CSV#输入数据的类型SKIP = 1000#跳过的输入行数DELIMITER = |#分隔文件每行中的列的单ASCII字符QUOTE = "\""#指定ASCII引号字符ESCAPE = "\""#指定应出现在QUOTE数据字符值之前的ASCII字符OUTPUT = public.tbl_test1#要将数据加载到的目标MULTI_PROCESS = NO#是否使用多线程并行对数据进行读取、解析和写入WRITER = DIRECT#加载数据的方法DUPLICATE_BADFILE = /usr/local/pgsql/data/pg_bulkload/test1.dup.csv#在索引重建期间写入与唯一约束冲突的坏记录的路径DUPLICATE_ERRORS = 0#违反唯一约束的传入元组数ON_DUPLICATE_KEEP = NEW#指定如何处理违反唯一约束的元组TRUNCATE = YES#指定是否清除目标表中的所有行

3）导入命令语法

pg_bulkload + 控制文件名称

4）新建表并插入一条数据

[postgres@pg-bulkload pg_bulkload]$ psql
psql (12.2)Type "help" for help.postgres=# create table tbl_test1(id int,name text);CREATE TABLEpostgres=# insert into tbl_test1 values (2021,'test');INSERT 0 1postgres=# select * from tbl_test1 ;id  | name------+------2021 | test(1 row)

5）执行控制文件

[postgres@pg-bulkload pg_bulkload]$ vim test1.ctl
[postgres@pg-bulkload pg_bulkload]$ pg_bulkload ./test1.ctlNOTICE: BULK LOAD STARTNOTICE: BULK LOAD END1000 Rows skipped.99000 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）查询验证

postgres=# select * from tbl_test1 ;id   |   name--------+----------1001 | postgres1002 | postgres1003 | postgres1004 | postgres1005 | postgres1006 | postgres......postgres=# select * from tbl_test1 where id = 2021;id  |   name------+----------2021 | postgres(1 row)

通过上述结果可以看到，在控制文件中配置参数跳过前1000行并对表先进行truncate操作，导入数据时执行了文件中的配置。

有关具体的参数信息和控制文件可配内容选项，查阅官方文档

http://ossc-db.github.io/pg_bulkload/pg_bulkload.html