pg_bulkload

pg_bulkload Overview

pg_bulkload is a high-speed data loading tool for MogDB. It is faster than the copy command and can skip shared buffer and wal buffer to write data into files directly.

Install pg_bulkload

1. Access Download page of the MogDB official website, download the pg_bulkload extension for the version you need.

2. Unpack the package, for example:

   tar -xzvf pg_bulkload-1.0-3.1.0-01-CentOS-x86_64.tar.gz

3. Go to the directory where the extension is located and execute the make install command.

   cd pg_bulkload/
   make install

4. Connect to the database and execute the create extension pg_bulkload; to use it.

   MogDB=# create extension pg_bulkload;
   CREATE EXTENSION

Use pg_bulkload

pg_bulkload --help
gsql -p 5432 postgres -r

CREATE EXTENSION pg_bulkload;
create table test_bulkload(id int, name varchar(128));

Create a TXT file and write 100,000 lines of data.

seq 100000| awk '{print $0"|bulkload"}' > bulkload_output.txt

Using Parameters

After the file is successfully created, run the following command:

pg_bulkload -i ./bulkload_output.txt -O test_bulkload -l test_bulkload.log -p 5432 -o "TYPE=csv" -o "DELIMITER=|" -d postgres -U hlv

Connect to the database to check whether the data is imported successfully:

select count(1) from test_bulkload;

Using the Control File

Before using the control file for data import, you need to clear the imported data in the previous table.

Write a .ctl file.

INPUT=/vdb/MogDB-server/dest/bulkload_output.txt
LOGFILE = /vdb/MogDB-server/dest/test_bulkload.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 5  (This parameter sets how many lines to skip)
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
OUTPUT = test_bulkload
MULTI_PROCESS = NO
WRITER = DIRECT
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = YES

Note: The code logic identifies parameters in the .ctl file with line breaks, so the last line of the .ctl file needs to be a line break to avoid incorrect parameter identification.

Run following command：

pg_bulkload ./lottu.ctl -d postgres -U hlv