PostgreSQL数据加载工具之pg(2)

[postgres@Postgres201 ~]$ pg_bulkload -i /home/postgres/tbl_lottu_output.txt -O tbl_lottu -l /home/postgres/tbl_lottu_output.log -P /home/postgres/tbl_lottu_bad.txt  -o "TYPE=CSV" -o "DELIMITER=|" -o "TRUNCATE=YES" -d lottu -U lottu
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@Postgres201 ~]$ psql lottu lottu -c "select count(1) from tbl_lottu;"
 count 
--------
 100000
(1 row)

3. 使用控制文件

　　新建控制文件lottu.ctl

INPUT = /home/postgres/lotu01
PARSE_BADFILE = /home/postgres/tbl_lottu_bad.txt
LOGFILE = /home/postgres/tbl_lottu_output.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 5
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
OUTPUT = lottu.tbl_lottu
MULTI_PROCESS = NO
WRITER = DIRECT
DUPLICATE_BADFILE = /home/postgres/tbl_lottu.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = YES

使用控制文件进行加载操作

pg_bulkload  /home/postgres/lottu.ctl -d lottu -U lottu
[postgres@Postgres201 ~]$ pg_bulkload  /home/postgres/lottu.ctl -d lottu -U lottu
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
 5 Rows skipped.
 95 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. 总结

　　pg_bulkload是一种用于PostgreSQL的高速数据加载工具，相比copy命令。最大的优势就是速度。优势在让我们跳过shared buffer,wal buffer。直接写文件。pg_bulkload的direct模式就是这种思路来实现的。不足的是;表字段的顺序要跟导入的文件报错一致。希望后续版本能开发。

Linux下RPM包方式安装PostgreSQL  https://www.linuxidc.com/Linux/2016-03/128906.htm
Linux下安装PostgreSQL  https://www.linuxidc.com/Linux/2016-12/138765.htm
Linux下PostgreSQL安装部署指南  https://www.linuxidc.com/Linux/2016-11/137603.htm
Linux下安装 PostgreSQL 并设置基本参数  https://www.linuxidc.com/Linux/2016-11/137324.htm
Ubuntu 16.04 下 PostgreSQL 主从复制配置  https://www.linuxidc.com/Linux/2017-08/146190.htm
Fedota 24 将数据库升级到 PostgreSQL 9.5  https://www.linuxidc.com/Linux/2016-11/137374.htm
CentOS7安装配置PostgreSQL9.6  https://www.linuxidc.com/Linux/2017-10/147536.htm
CentOS5.8_x64下离线安装PostgreSQL 9.1  https://www.linuxidc.com/Linux/2017-10/147822.htm
CentOS 6.5下PostgreSQL服务部署  https://www.linuxidc.com/Linux/2017-01/139144.htm

Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx