PostgreSQL数据加载工具之pg_bulkload
2018-08-02 05:57:58来源:博客园 阅读 ()
3.
[root@Postgres201 ~]# unzip pg_bulkload-VERSION3_1_10.zip [root@Postgres201 ~]# cd pg_bulkload-VERSION3_1_10 [root@Postgres201 pg_bulkload-VERSION3_1_10]# make [root@Postgres201 pg_bulkload-VERSION3_1_10]# make install
安装完成;要使用它需要建extension
[postgres@Postgres201 ~]$ psql lottu lottu psql (9.6.0) Type "help" for help. lottu=# create extension pg_bulkload; CREATE EXTENSION
[postgres@Postgres201 ~]$ 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
[postgres@Postgres201 ~]$ psql lottu lottu psql (9.6.0) Type "help" for help. lottu=# create table tbl_lottu(id int,name text); CREATE TABLE [postgres@Postgres201 ~]$ seq 100000| awk '{print $0"|lottu"}' > tbl_lottu_output.txt
[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=|" -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 ~]$ cat tbl_lottu_output.log pg_bulkload 3.1.9 on 2018-07-12 13:37:18.326685+08 INPUT = /home/postgres/tbl_lottu_output.txt 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 = 0 DELIMITER = | QUOTE = "\"" ESCAPE = "\"" NULL = OUTPUT = lottu.tbl_lottu MULTI_PROCESS = NO VERBOSE = NO WRITER = DIRECT DUPLICATE_BADFILE = /data/postgres/data/pg_bulkload/20180712133718_lottu_lottu_tbl_lottu.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 2018-07-12 13:37:18.326685+08 Run ended on 2018-07-12 13:37:18.594494+08 CPU 0.14s/0.07u sec elapsed 0.27 sec
[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)
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.
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- python_0基础开始_day07 2019-08-13
- Django和前端用ajax传输json等数据 2019-08-13
- 链接 Mysql 创建 数据库和创表,增加数据 2019-08-13
- Python数据基础类型-列表 2019-08-13
- JavaScript获取Django模板中指定键值的数据,使用过滤器 2019-08-13
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash