安装：
引入扩展：
$psql psql(9.3.4) Type"help"forhelp. postgres=#createdatabasetest; CREATEDATABASE postgres=#\ctest Youarenowconnectedtodatabase"test"asuser"postgres". test=#createextensionpg_bulkload; CREATEEXTENSION test=#\dx Listofinstalledextensions Name|Version|Schema|Description -------------+---------+------------+----------------------------------------------------------------- pg_bulkload|1.0|public|pg_bulkloadisahighspeeddataloadingutilityforPostgresql plpgsql|1.0|pg_catalog|PL/pgsqlprocedurallanguage (2rows) test=#\df Listoffunctions Schema|Name|Resultdatatype|Argum entdatatypes|Type --------+-------------+------------------+------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------+-------- public|pg_bulkload|record|optionstext[],OUTskipbigint,OUTcountbigint,OUTparse_errorsbigint,OUTduplicate_newbigint,OUTd uplicate_oldbigint,OUTsystem_timedoubleprecision,OUTuser_timedoubleprecision,OUTdurationdoubleprecision|normal (1row)
创造测试数据：
test=#createtablet1(idint,nametext); CREATETABLE test=#insertintot1selectgenerate_series(1,50000000),'HighGo'; INSERT050000000 test=#\d+ Listofrelations Schema|Name|Type|Owner|Size|Description --------+------+-------+----------+---------+------------- public|t1|table|postgres|2112MB| (1row) test=#copyt1to'/opt/pg93/data/t1.csv'with(format'csv'); COPY50000000@H_502_7@$du-sh/opt/pg93/data/t1.csv 754M/opt/pg93/data/t1.csv
copy方式载入数据：
(with logged):
test=#truncatet1; TRUNCATETABLE test=#\timing Timingison. test=# test=#copyt1from'/opt/pg93/data/t1.csv'with(format'csv'); COPY50000000 Time:139038.099ms
(without logged):
test=#truncatet1; TRUNCATETABLE 修改t1表为unlogged test=#updatepg_classsetrelpersistence='u'whererelname='t1'; UPDATE1 test=#copyt1from'/opt/pg93/data/t1.csv'with(format'csv'); COPY50000000 Time:110796.480ms
pg_blukload载入数据：
(without logged):
(with logged):
test=#truncatet1; TRUNCATETABLE 修改t1表为logged test=#updatepg_classsetrelpersistence='p'whererelname='t1'; UPDATE1@H_502_7@$pg_bulkload-i/opt/pg93/data/t1.csv-Ot1-lt1_bulkload.log-o"TYPE=CSV"-o"WRITER=PARALLEL"-dtest NOTICE:BULKLOADSTART NOTICE:BULKLOADEND 0Rowsskipped. 50000000Rowssuccessfullyloaded. 0Rowsnotloadedduetoparseerrors. 0Rowsnotloadedduetoduplicateerrors. 0Rowsreplacedwithnewrows. Runbeganon2014-06-1605:57:05.620751+08 Runendedon2014-06-1605:58:20.458029+08 cpu2.80s/33.02usecelapsed74.84sec $pg_bulkload-i/opt/pg93/data/t1.csv-Ot1-lt1_bulkload.log-o"TYPE=CSV"-o"WRITER=DIRECT"-dtest NOTICE:BULKLOADSTART NOTICE:BULKLOADEND 0Rowsskipped. 50000000Rowssuccessfullyloaded. 0Rowsnotloadedduetoparseerrors. 0Rowsnotloadedduetoduplicateerrors. 0Rowsreplacedwithnewrows. Runbeganon2014-06-1606:02:10.33344+08 Runendedon2014-06-1606:03:36.986382+08 cpu7.15s/34.93usecelapsed86.65sec
pg_bulkload执行过程中查看进程和连接：
（WRITER=PARALLEL）
postgres=#selectdatname,application_name,queryfrompg_stat_activity; datname|application_name|query ----------+------------------+------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------- postgres|psql|selectdatname,queryfrompg_stat_activity; test|pg_bulkload|SELECT*FROMpg_bulkload($1) test||SELECT*FROMpg_bulkload(ARRAY['TYPE=TUPLE','INPUT='||$1,'WRITER=DIRECT','OUTPUT='||$2,'ON_DUPLICATE_KEEP='||$3,'DUPLICATE_ERRORS='||$4,'DUPLICATE_BADFILE='||$5,'LOGFILE='||$6,'VERBOSE='||$7,'TRUNCATE='||$8]) (3rows)
（WRITER=PARALLEL）
test=#selectdatname,queryfrompg_stat_activity; datname|application_name|query ---------+------------------+-------------------------------------------------------------- test|pg_bulkload|SELECT*FROMpg_bulkload($1) test|psql|selectdatname,queryfrompg_stat_activity; (2rows)
