docs/pg_bulkload.html

<!DOCTYPE html PUBLIC "-//W3C//DTD html 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>pg_bulkload</title>
<link rel="home" title="pg_bulkload" href="index.html">
<link rel="stylesheet" TYPE="text/css"href="style.css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>

<body>
<h1 id="pg_bulkload">pg_bulkload 3.1</h1>
<div class="navigation">
  <a href="index.html">Top</a> &gt;
  <a href="pg_bulkload.html">pg_bulkload</a>
<div>
<hr />

<div class="index">
<ol>
<li><a href="#name">Name</a></li>
<li><a href="#synopsis">Synopsis</a></li>
<li><a href="#description">Description</a></li>
<li><a href="#examples">Examples</a></li>
<li><a href="#options">Options</a></li>
<li><a href="#controlfile">Control Files</a></li>
<li><a href="#environment">Envirionment</a></li>
<li><a href="#restrictions">Restrictions</a></li>
<li><a href="#details">Details</a></li>
<li><a href="#install">Installation</a></li>
<li><a href="#requirement">Requirements</a></li>
<li><a href="#releasenote">Release Notes</a></li>
<li><a href="#seealso">See Also</a></li>
</ol>
</div>

<h2 id="name">Name</h2>
<p>pg_bulkload -- it provides high-speed data loading capability to PostgreSQL users.</p>

<h2 id="synopsis">Synopsis</h2>
<p>
pg_bulkload [ OPTIONS ] [ controlfile ]
</p>

<h2 id="description">Description</h2>

<p style="color:red">
IMPORTANT NOTE: Under streaming replication environment, pg_bulkload does not work properly. See <a href="#restrictions">here</a> for details.
</p>

<p>
pg_bulkload is designed to load huge amount of data to a database.
You can choose whether database constraints are checked and how many errors are ignored during the loading.
For example, you can skip integrity checks for performance when you copy data from another database to PostgreSQL.
On the other hand, you can enable constraint checks when loading unclean data.
</p>

<p>
The original goal of pg_bulkload was an faster alternative of <code>COPY</code> command in PostgreSQL,
but version 3.0 or later has some ETL features like input data validation and data transformation with filter functions.
</p>

<p>
In version 3.1, pg_bulkload can convert the load data into the binary file
which can be used as an input file of pg_bulkload.
If you check whether the load data is valid when converting it into the binary file,
you can skip the check when loading it from the binary file to a table.
Which would reduce the load time itself.
Also in version 3.1, parallel loading works more effectively than before.
</p>

<p>
There are some bugs in old version of 3.1. Please check <a href="#releasenote">Release notes</a>, and use newer versions.
</p>

<h2 id="examples">Examples</h2>

<p>
pg_bulkload provides two programs.
</p>

<h3>postgresql script</h3>
<p>
This is a wrapper command for <code>pg_ctl</code>, which starts and stops PostgreSQL
server.  postgresql script invokes <code>pg_ctl</code> internally.   postgresql script
provides very important pg_bulkload functionality i.e. recovery.   To improve
performance, pg_bulkload bypasses some of PostgreSQL's internal functionality
such as WAL.   Therefore, pg_bulkload needs to provide separate recovery
procedure before usual PostgreSQL's recovery is performed.   postgresql script
provides this feature.
</p>
<p>
You must see below "<a href="#restrictions">Restrictions</a>",
especially if you use pg_bulkload in DIRECT or PARALLEL load modes.
It requires special database recovery processes.
Notice that DIRECT mode is <b>the default settings</b>.
</p>

<h3>pg_bulkload</h3>
<p>
This program is used to load the data.
Internally, it invokes PostgreSQL's user-defined function called pg_bulkload() and perform the loading.
pg_bulkload() function will be installed during pg_bulkload installation.
</p>

<p>
You can use pg_bulkload by the following three steps:
</p>
<ol>
<li>Edit control file "<a href="sample_csv.ctl">sample_csv.ctl</a>" or "<a href="sample_bin.ctl">sample_bin.ctl</a>" that includes settings for data loading. You can specify table name, absolute path for input file, description of the input file, and so on.

<li>Assume there is a directory <code>$PGDATA/pg_bulkload</code>, in that load status files are created.

<li>Execute command with a control file as argument. Relative path is available for the argument.
<pre>$ pg_bulkload sample_csv.ctl
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
	0 Rows skipped.
	8 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.</pre>
</ol>
</p>

<h2 id="options">Options</h2>
<p>pg_bulkload has the following command line options:</p>

<h3>Load Options</h3>
<p>Options to load data.</p>

<dl>
<dt>
-i INPUT<br />
--input=INPUT<br />
--infile=INPUT
</dt>
<dd>Source to load data from.
Same as "<a href="#INPUT">INPUT</a>" in control files.
</dd>

<dt>
-O OUTPUT<br />
--output=OUTPUT
</dt>
<dd>Destination to load data to.
Same as "<a href="#OUTPUT">OUTPUT</a>" in control files.
</dd>

<dt>
-l LOGFILE<br />
--logfile=LOGFILE
</dt>
<dd>A path to write the result log.
Same as "<a href="#LOGFILE">LOGFILE</a>" in control files.
</dd>

<dt>
-P PARSE_BADFILE<br />
--parse-badfile=PARSE_BADFILE
</dt>
<dd>A path to write bad records that cannot be parsed correctly.
Same as "<a href="#PARSE_BADFILE">PARSE_BADFILE</a>" in control files.
</dd>

<dt>
-u DUPLICATE_BADFILE<br />
--duplicate-badfile=DUPLICATE_BADFILE
</dt>
<dd>A path to write bad records that conflict with unique constraints during index rebuild.
Same as "<a href="#DUPLICATE_BADFILE">DUPLICATE_BADFILE</a>" in control files.
</dd>

<dt>
-o "key=val"<br />
--option="key=val"
</dt>
<dd>
Any options available in the <a href="#controlfile">control file</a>.
You can pass multiple options.
</dd>

</dl>

<h3>Connection Options</h3>
<p>Options to connect to servers.</p>

<dl>
<dt>
-d dbname<br />
--dbname dbname
</dt>
<dd>Specifies the name of the database to be connected.
If this is not specified, the database name is read from the environment variable PGDATABASE.
If that is not set, the user name specified for the connection is used.
</dd>

<dt>-h host<br />
--host host</dt>
<dd>Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. </dd>

<dt>-p port<br />
--port port</dt>
<dd>Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.</dd>

<dt>-U username<br />
--username username</dt>
<dd>User name to connect as. The user must be a superuser for pg_bulkload execution.</dd>

<dt>-W<br />--password</dt>
<dd>Force pg_bulkload to prompt for a password before connecting to a database.</dd>
<dd>This option is never essential, since pg_bulkload will automatically prompt for a password if the server demands password authentication. However, vacuumdb will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt. </dd>
</dl>

<h3>Generic Options</h3>
<dl>
<dt>-e<br />--echo</dt>
<dd>Echo commands sent to server.</dd>
<dt>-E<br />--elevel</dt>
<dd>Choose the output message level from DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC.
The default is INFO.</dd>
<dt>--help</dt>
<dd>Show usage of the program.</dd>
<dt>--version</dt>
<dd>Show the version number of the program.</dd>
</dl>

<h2 id="controlfile">Control Files</h2>
<p>
You can specify the following load options.
Control files can be specifed with an absolute path or a relative path.
If you specify it by a relative path, it will be relative to the current working directory executing pg_bulkload command.
If you don't specify a control file, you should pass required options through command line arguments for pg_bulkload.
</p>

<p>
Following parameters are available in control files.
Characters after "#" are ignored as comments in each line.
</p>

<h3>Common</h3>
<dl>

<dt>TYPE = CSV | BINARY | FIXED | FUNCTION </dt>
<dd>
The type of input data.
The default is CSV.
<ul>
  <li>CSV : load from a text file in CSV format</li>
  <li>BINARY | FIXED : load from a fixed binary file</li>
  <li>FUNCTION : load from a result set from a function.<br/>
      If you use it, INPUT must be an expression to call a function.</li>
</ul>
</dd>

<dt id="INPUT">INPUT | INFILE = path | stdin | [ schemaname. ] function_name (argvalue, ...)</dt>
<dd>
Source to load data from. Always required.
The value is treated as following depending on the TYPE option:
<ul>
  <li>A file in the server:
      This is a file path in server.
      If it is a relative path, it will be relative from the control file when specified in the control file,
      or will be relative from current working directory when specified in command line arguments.
      The user of PostgreSQL server must have read permission to the file.
      It is available only when "TYPE=CSV" or "TYPE=BINARY".
  </li>
  <li>Standard input to pg_bulkload command:
      "INPUT=stdin" means pg_bulkload will read data from the standard input of pg_bulkload client program through network.
      You should use this form when the input file and database is in different servers.
      It is available only when "TYPE=CSV" or "TYPE=BINARY".
      For example:
      <pre>$ pg_bulkload csv_load.ctl &lt; DATA.csv</pre></li>
  <li>A SQL function:
      Specify a SQL function with arguments that returns set of input data.
      It is available only when "TYPE=FUNCTION".
      You can use not only build-in functions but also any user-defined functions.
      The defined functions should return the type as SETOF sometype, or as RETURNS TABLE(columns).
      Note that you specify attributes types if you use sometype. Because there is a case that
      assignment cast to cstring type which pg_bulkload uses internally, doesn't be permitted.
      Note that you might need to develop those function with C language instead of PL/pgSQL
      because the function must use SFRM_ValuePerCall mode for streaming loading.
      <p>Here is an example of user-defined function.</p>
<pre>$ CREATE TYPE sample_type AS (sum integer, name char(10));
$ CREATE FUNCTION sample_function() RETURNS SETOF sample_type
    AS $$ SELECT id1 + id2, upper(name) FROM INPUT_TABLE $$
    LANGUAGE SQL;
</pre>
      <p>Here is an example of a control file.</p>
<pre>TABLE = sample_table
TYPE = FUNCTION
WRITER = DIRECT
INPUT = sample_function()          # if to use the user-defined function
#INPUT = generate_series(1, 1000)  # if to use the build-in function, which generate sequential numbers from 1 to 1000
</ul>
</dd>

<dt>WRITER | LOADER = DIRECT | BUFFERED | BINARY | PARALLEL</dt>
<dd>
The method to load data. The default is DIRECT.
<ul>
  <li>DIRECT   : Load data directly to table.
                 Bypass the shared buffers and skip WAL logging, but need the own recovery procedure.
                 This is the default, and original older version's mode.</li>
  <li>BUFFERED : Load data to table via shared buffers.
                         Use shared buffers, write WALs, and use the original PostgreSQL WAL recovery.</li>
  <li>BINARY    : Convert data into the binary file which can be used as an input file to load from.
                 Create a sample of the control file necessary to load the output binary file.
                 This sample file is created in the same directory as the binary file, and its name is &lt;binary-file-name&gt;.ctl.
  <li>PARALLEL : Same as "WRITER=DIRECT" and "MULTI_PROCESS=YES".
                 If PARALLEL is specified, <a href="#MULTI_PROCESS">MULTI_PROCESS</a> is ignored.
                 If password authentication is configured to the database to load,
                 you have to set up the password file. See <a href="#restrictions">Restrictions</a> for details.
</ul>
</dd>

<dt id="OUTPUT">OUTPUT | TABLE = { [ schema_name. ] table_name | outfile }</dt>
<dd>
Destination to load data to. Always required.
The value is treated as following depending on the WRITER (or LOADER) option:
<ul>
  <li>A table to load to:
       Specify the table to load to.
       If schema_name is omitted, the first matching table in the search_path is used.
       You can load data to a table only if WRITER is DIRECT, BUFFERED or PARALLEL.</li>
  <li>A file in the server:
       Specify the path of the output file in the server.
       If it's a relative path, it will be interpreted in the same way as <a href="#INPUT">INPUT</a> option.
       The OS user running PostgreSQL must have write permission to the parent directory of the specified file.
       You can load (convert) data to a file only if WRITER is BINARY.</li>
</ul>
</dd>

<dt>SKIP | OFFSET = n</dt>
<dd>
The number of skip input rows. The default is 0.
You must not specify both "TYPE=FUNCTION" and SKIP at the same time.
</dd>

<dt>LIMIT | LOAD = n</dt>
<dd>
The number of rows to load.
The default is INFINITE, i.e., all of data will be loaded.
This option is available even if you use TYPE=FUNCTION.
</dd>

<dt>ENCODING = encoding</dt>
<dd>
Specify the encoding of the input data.
Check whether the specified encoding is valid, and convert the input data to the database encoding if needed.
By default, the encoding of the input data is neither verified nor converted.
If you can be sure that the input data is encoded in the database encoding,
you can reduce the load time by not specifying this option, and by skipping encoding verification and conversion.
Note that client_encoding is used as the encoding of the input data by default only if INPUT is stdin.
You must not specify both "TYPE=FUNCTION" and ENCODING at the same time.
</dd>
<dd>
See <a href="http://www.postgresql.jp/document/current/html/functions-string.html#CONVERSION-NAMES">Built-in Conversions</a> for valid encoding names.
Here are option values and actual behaviors:
</dd>

<dd>
<table>
<thead>
  <tr>
    <th colspan=2 rowspan=2> </th>
    <th colspan=2>DB encoding</th>
  </tr>
  <tr>
    <th>SQL_ASCII</th>
    <th>non-SQL_ASCII</th>
  </tr>
</thead>
  <tr>
    <th rowspan=4>ENCODING</th>
    <th>not specified</th>
    <td>neither checked nor converted</td>
    <td>neither checked nor converted</td>
  </tr>
  <tr>
    <th>SQL_ASCII</th>
    <td>neither checked nor converted</td>
    <td>only checked</td>
  </tr>
  <tr>
    <th>non-SQL_ASCII, same as DB</th>
    <td>only checked</td>
    <td>only checked</td>
  </tr>
  <tr>
    <th>non-SQL_ASCII, different from DB</th>
    <td>only checked</td>
    <td>checked and converted</td>
  </tr>
</table>
</dd>

<dt>FILTER = [ schema_name. ] function_name [ (argtype, ... ) ]</dt>
<dd>
Specify the filter function to convert each row in the input file.
You can omit definitions of argtype as long as the function name is unique in the database.
If not specified, the input data are directly parsed as the load-target table.
See also <a href="#filter">How to write FILTER functions</a> to make FILTER functions.
</dd>
<dd>
You must not specify both "TYPE=FUNCTION" and FILTER at the same time.
Also, FORCE_NOT_NULL in CSV option cannot be used with FILTER option.
</dd>

<dt>CHECK_CONSTRAINTS = YES | NO</dt>
<dd>
Specify whether CHECK constraints are checked during the loading.
The default is NO.
You must not specify both "WRITER=BINARY" and CHECK_CONSTRAINTS at the same time.
</dd>

<dt>PARSE_ERRORS = n </dt>
<dd>
The number of ingored tuples that throw errors during parsing, encoding checks, encoding conversion, FILTER function, CHECK constraint checks, NOT NULL checks, or data type conversion.
Invalid input tuples are not loaded and recorded in the PARSE BADFILE.
The default is 0.
If there are equal or more parse errors than the value, <strong>already loaded data is committed and the remaining tuples are not loaded</strong>.
0 means to allow no errors, and -1 and INFINITE mean to ignore all errors.
</dd>

<dt>DUPLICATE_ERRORS = n</dt>
<dd>
The number of ingored tuples that violate unique constraints.
Conflicted tuples are removed from the table and recorded in the DUPLICATE BADFILE.
The default is 0.
If there are equal or more unique violations than the value, <strong>the whole load is rollbacked</strong>.
0 means to allow no violations, and -1 and INFINITE mean to ignore all violations.
You must not specify both "WRITER=BINARY" and DUPLICATE_ERRORS at the same time.
</dd>

<dt>ON_DUPLICATE_KEEP = NEW | OLD</dt>
<dd>Specify how to handle tuples that violate unique constraints.
The removed tuples are recorded in the BAD file.
The default is NEW.
You also need to set DUPLICATE_ERRORS to more than 0 if you enable the option.
You must not specify both "WRITER=BINARY" and ON_DUPLICATE_KEEP at the same time.
<ul>
  <li>NEW : Keep tuples in the input data, and remove corresponding existing tuples.
            When both violated tuples are in the data, keep the latter one.</li>
  <li>OLD : Keep existing tuples and remove tuples in the input data.</li>
</ul>
</dd>

<dt id="LOGFILE">LOGFILE = path</dt>
<dd>
A path to write the result log.
If specified by a relative path, it is treated as same as <a href="#INPUT">INPUT</a>.
The default is $PGDATA/pg_bulkload/&lt;<i>timestamp</i>&gt;_&lt;<i>dbname</i>&gt;_&lt;<i>schema</i>&gt;_&lt;<i>table</i>&gt;.log.
</dd>

<dt id="PARSE_BADFILE">PARSE_BADFILE = path</dt>
<dd>
A path to the BAD file logging invalid records which caused an error during parsing, encoding checks,
encoding conversion, FILTER function, CHECK constraint checks, NOT NULL checks, or data type conversion.
The format of the file is same as the input source file.
If specified by a relative path, it is treated as same as <a href="#INPUT">INPUT</a>.
The default is $PGDATA/pg_bulkload/&lt;<i>timestamp</i>&gt;_&lt;<i>dbname</i>&gt;_&lt;<i>schema</i>&gt;_&lt;<i>table</i>&gt;.bad.&lt;<i>extension-of-infile</i>&gt;.
</dd>

<dt id="DUPLICATE_BADFILE">DUPLICATE_BADFILE = path</dt>
<dd>
A path to write bad records that conflict with unique constraints during index rebuild.
The format of the file is always CSV.
If specified by a relative path, it is treated as same as <a href="#INPUT">INPUT</a>.
The default is $PGDATA/pg_bulkload/&lt;<i>timestamp</i>&gt;_&lt;<i>dbname</i>&gt;_&lt;<i>schema</i>&gt;_&lt;<i>table</i>&gt;.dup.csv.
You must not specify both "WRITER=BINARY" and DUPLICATE_BADFILE at the same time.
</dd>

<dt>TRUNCATE = YES | NO</dt>
<dd>
If YES, delete all rows from the target table with TRUNCATE command.
If NO, do nothing.
The default is NO.
You must not specify both "WRITER=BINARY" and TRUNCATE at the same time.
</dd>

<dt>VERBOSE = YES | NO</dt>
<dd>
If YES, write bad tuples also in server log.
If NO, don't write them in serverlog.
The default is NO.
</dd>

<dt id="MULTI_PROCESS">MULTI_PROCESS = YES | NO</dt>
<dd>
If YES, we do data reading, parsing and writing in parallel by using multiple threads.
If NO, we use only single thread for them instead of doing parallel processing.
The default is NO.
If WRITER is PARALLEL, MULTI_PROCESS is ignored.
If password authentication is configured to the database to load,
you have to set up the password file. See <a href="#restrictions">Restrictions</a> for details.
Please make sure when enabling MULTI_PROCESS that no other PostgreSQL backend
process is trying to modify the schema of the table, because it may cause the
schema of the table as seen by the reader and the writer processes to differ
and cause problems.
</dd>

</dl>


<h3>CSV input format</h3>
<dl>
<dt id="DELIMITER">DELIMITER = delimiter_character</dt>
<dd>
The single ASCII character that separates columns within each row (line) of the file.
The default is comma.
When you load a tab-separated format file (TSV), you can set DELIMITER to a tab character.
Then, you need to double quote the tab:
<pre>DELIMITER="	" # a double-quoted tab</pre>
You can also specify DELIMITER as a command-line -o option with <code>$'\t'</code> syntax.
<pre>$ pg_bulkload tsv.ctl -o $'DELIMITER=\t'</pre>
</dd>
<dt>QUOTE = quote_character</dt>
<dd>
Specifies the ASCII quotation character.
The default is double-quotation.
</dd>
<dt>ESCAPE = escape_character</dt>
<dd>
Specifies the ASCII character that should appear before a QUOTE data character value.
The default is double-quotation.
</dd>
<dt>NULL = null_string</dt>
<dd>
The string that represents a null value.
The default is a empty value with no quotes.
</dd>
<dt>FORCE_NOT_NULL = column</dt>
<dd>
Process each specified column as though it were not a NULL value.
Multiple columns are available as needed.
FILTER cannot be used together with this option.
</dd>

</dl>

<h3>Binary input format</h3>
<dl>
<dt>COL = type [ (size) ] [ NULLIF { 'null_string' | null_hex } ]<dt>
<dd>
Column definitions of input file from left to right.
The definitions consists of type name, offset, and length in bytes.
CHAR and VARCHAR means input data is a text.
Otherwise, it is a binary data.
If binary, endian must match between server and data file.
  <ul>
    <li>CHAR | CHARACTER : a string trimmed trailing spaces. The length is always required.</li>
    <li>VARCHAR | CHARACTER VARYING : a string keeping trailing spaces. The length is always required.</li>
    <li>SMALLINT | SHOFT : signed integer in 2 bytes.</li>
    <li>INTEGER | INT : signed integer in 2 or 4 or 8 bytes. The default is 4.</li>
    <li>BIGINT | LONG : signed integer in 8 bytes.</li>
    <li>UNSIGNED SMALLINT | SHORT : unsigned integer in 2 bytes.</li>
    <li>UNSIGNED INTEGER | INT : unsigned integer in 2 or 4 bytes. The default is 4.</li>
    <li>FLOAT | REAL : floating point number in 4 or 8 bytes. The default is 4.</li>
    <li>DOUBLE : floating point number in 8 bytes.</li>
  </ul>
The length and offset of the type can be specifed as follows:
  <ul>
    <li>TYPE : TYPE with default length follows.</li>
    <li>TYPE(L) : TYPE with L bytes follows.</li>
    <li>TYPE(S+L) : L bytes, offset S bytes from the beginning of the line</li>
    <li>TYPE(S:E) : start at S bytes and end at E bytes.</li>
  </ul>
The string expressing NULL can be specified as follows:
  <ul>
    <li>NULLIF 'null_string' : Specify the string expressing NULL when the type is CHAR or VARCHAR.
The length of the string must be the same as that of the type.</li>
    <li>NULLIF null_hex : Specify the hex value expressing NULL when the type is other than CHAR and VARCHAR.
The length of the hex value must be the same as that of the type.</li>
  </ul>
In addition, "COL N" is available, that is same as COL CHAR(N), for backward compatibility.
</dd>

<dt>PRESERVE_BLANKS = YES | NO</dt>
<dd>
YES regards following "COL N" as "COL CHAR(N)" and NO as "COL VARCHAR(N)".
Default is NO.
</dd>

<dt>STRIDE = n</dt>
<dd>
Length of one row.
Use if you want to truncate the end of row.
The default is whole of the row, which means the total of COLs.
</dd>

</dl>

<h3>Binary output format</h3>
<dl>
<dt>OUT_COL = type [ (size) ] [ NULLIF { 'null_string' | null_hex } ]<dt>
<dd>
Column definitions of output file from left to right.
The definitions consists of type name, offset, and length in bytes.
CHAR and VARCHAR means input data is a text.
Otherwise, it is a binary data.
If binary, endian must match between server and data file.
  <ul>
    <li>CHAR | CHARACTER : fixed-length string.
          The length must be specified. If the string to be stored is shorter than the declared length,
          values will be space-padded. "COL=CHAR(size)" will be output in the sample of control file.</li>
    <li>VARCHAR | CHARACTER VARYING : fixed-length string.
          The length must be specified. If the string to be stored is shorter than the declared length,
          values will be space-padded. "COL=VARCHAR(size)" will be output in the sample of control file.</li>
    <li>SMALLINT | SHOFT : signed integer in 2 bytes.</li>
    <li>INTEGER | INT : signed integer in 2 or 4 or 8 bytes. The default is 4.</li>
    <li>BIGINT | LONG : signed integer in 8 bytes.</li>
    <li>UNSIGNED SMALLINT | SHORT : unsigned integer in 2 bytes.</li>
    <li>UNSIGNED INTEGER | INT : unsigned integer in 2 or 4 bytes. The default is 4.</li>
    <li>FLOAT | REAL : floating point number in 4 or 8 bytes. The default is 4.</li>
    <li>DOUBLE : floating point number in 8 bytes.</li>
  </ul>
The string expressing NULL can be specified as follows.
If omitted but NULL is input, NULL is logged as an invalid data in PARSE_BADFILE.
  <ul>
    <li>NULLIF 'null_string' : Specify the string expressing NULL when the type is CHAR or VARCHAR.
The length of the string must be the same as that of the type.</li>
    <li>NULLIF null_hex : Specify the hex value expressing NULL when the type is other than CHAR and VARCHAR.
The length of the hex value must be the same as that of the type.</li>
  </ul>
</dd>
</dl>


<h2 id="environment">Environment</h2>
<p>The followin envionment variables affect pg_bulkload.</p>

<dl>
	<dt>
		PGDATABASE<br />
		PGHOST<br />
		PGPORT<br />
		PGUSER
	</dt>
	<dd>Default connection parameters</dd>
</dl>
<p>This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq  (see <a href="http://developer.postgresql.org/pgdocs/postgres/libpq-envars.html">Environment Variables</a>).</p>

<h2 id="restrictions">Restrictions</h2>

<h3>Support for pluggable table access methods in PostgreSQL 12</h3>
 <p>
  pg_bulkload currently only supports loading data into tables of access method
  named "heap".  Note that it is the method that core PostgreSQL has always
  used, but has been revised in PostgreSQL 12 to go through the new pluggable
  table access API.
 </p>

<h3>Exit code of pg_bulkload</h3>
<p>
pg_bulkload returns 0 when succesfully loaded.
It also returns 3 with a WARNING message when there are some parse errors or duplicate errors even if loading itself was finished.
Note that skipped rows and replaced rows (with ON_DUPLICATE_KEEP = NEW) are not considered as an error; the exit code will be 0.
</p>

<p>
When there is a non-continuable error, the loader raises an ERROR message.
The return code will be often 1 because many errors occur in the database server during loading data.
The following table shows the codes that pg_bulkload can return.
</p>

<table>
<thead>
  <tr>
    <th>Return code</th>
    <th>Description</th>
  </tr>
</thead>
  <tr>
    <td>0</td>
    <td>Success</td>
  </tr>
  <tr>
    <td>1</td>
    <td>Error occurred during running SQL in PostgreSQL</td>
  </tr>
  <tr>
    <td>2</td>
    <td>Failed to connect to PostgreSQL</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Success, but some data could not be loaded</td>
  </tr>
</table>


<h3>On direct loading</h3>
<p>
If you use direct load mode (WRITER=DIRECT or PARALLEL), you have to be aware below:
</p>

<h4>PostgreSQL startup sequence</h4>
<p>
When pg_bulkload is crashed and some .loadstatus files are remained in <code>$PGDATA/pg_bulkload</code>, database must be recovered by pg_bulkload own recovery with "<code>pg_bulkoad -r</code>" command before you invoke pg_ctl start.
You must start and stop PostgreSQL using postgresql script, which invokes "<code>pg_bulkload -r</code>" and "pg_ctl start" correctly.   We recommend not to use <code>pg_ctl</code> directly.
</p>
<p>
If you use pg_bulkload in Windows operating system, postgresql script is not included in a pg_bulkload package. So you have to invoke "<code>pg_bulkload -r</code>" manually.
</p>

<h4>PITR/Replication</h4>
<p>
Because of bypassing WAL, archive recovery by PITR is not available.
This does not mean that it can be done PITR without loaded tables data.
If you would like to use PITR, take a full backup of the
database after load via pg_bulkload.
If you are using streaming replication, you need to re-create your standby based on the backup set which is taken after pg_bulkload.
</p>

<h4>Load status file in $PGDATA/pg_bulkload</h4>
<p>
You must not remove the load status file (*.loadstatus) found in
<code>$PGDATA/pg_bulkload</code> directory.
This file is needed in pg_bulkload crash recovery.
</p>

<h4>Do not use <code>kill -9</code></h4>
<p>
Do not terminate pg_bulkload command using "<code>kill -9</code>" as much as possible.   If you did this, you
must invoke postgresql script to perform pg_bulkload recovery and restart
PostgreSQL to continue.
<p>

<h4>Authentication can fail when MULTI_PROCESS=YES</h4>
<p> When MULTI_PROCESS=YES and password is required to connect from localhost
to the database to load, the authentication will fail even if you enter
the password correctly in the prompt. To avoid this, configure either of the followings.<br/>
<ul>
<li>Use "trust" method to authenticate the connection from localhost<br/>
In UNIX environment, the connection from localhost uses UNIX-domain socket,
and in Windows, it uses TCP/IP loopback address.
In UNIX, add the following line into pg_hba.conf.<br/>
<pre>
# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD [for UNIX]
local   all             foo                                     trust<br/></pre>
In Windows, add the following line into pg_hba.conf.<br/>
<pre>
# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD [for Windows]
host    all             foo             127.0.0.1/32            trust</pre>
</li>
<li>Specify the password in .pgpass file<br/>
If you don't want to use "trust" method for security reasons, use "md5" or "password"
as an authentication method and specify the password in
<a href="http://www.postgresql.org/docs/current/static/libpq-pgpass.html">.pgpass file</a>.
Note that the .pgpass file must be in the home directory of the OS user
(typically "postgres" user) who ran PostgreSQL server. For example,
if pg_bulkload connects to the server that is running on port 5432 as
the DB user "foo" whose password is "foopass", the administrator can add
the following line to the .pgpass file:
<pre>localhost:5432:*:foo:foopass</pre>
</li>
<li>Don't use "WRITER=PARALLE"<br/>
Use the loading method other than "WRITER=PARALLEL".
</li>
</ul>
</p>

<h3>Database Constraints</h3>
<p>
Only unique constraint and not-NULL constraint are enforced during data load in default.
You can to set "CHECK_CONSTRAINTS=YES" to check CHECK constraints.
Foreign key constraints cannot be checked.
It is user's responsibility to provide valid data set.
</p>

<h2 id="details">Details</h2>
<h3 id="internal">Internal</h3>
<p>Here is an internal structure in pg_bulkload.</p>
<img src="img/internal.png" width="600" />

<p>Here are system structure to load data from a file, stdin, or a SQL function:</a>
<ul>
<li><a href="img/from-file.png">File</a></li>
<li><a href="img/from-stdin.png">Stdin</a></li>
<li><a href="img/from-func.png">SQL function</a></li>
</ul>


<h3 id="filter">How to write FILTER functions</h3>
<p>There are some notes and warnings when you write FILTER functions:</p>
<ul>
  <li>Records in the input file are passed to the FILTER function one-by-one.</li>
  <li>When an error occurs in the FILTER function, the passed record is not loaded and written into PARSE BADFILE.</li>
  <li>The FILTER function must return <i>record</i> type or some composite type.
      Also, the actual record type must match with the target table definition.</li>
  <li>If the FILTER function returns NULL, a record that has NULLs in all columns is loaded.</li>
  <li>Functions with default arguments are supported.
      If the input data has fewer columns than arguments of the function, default values will be used.</li>
  <li>VARIADIC functions are NOT supported.</li>
  <li>SETOF funtions are NOT supported.</li>
  <li>Functions that have generic types (any, anyelement etc.) are NOT supported.</li>
  <li>FILTER functions can be implemented with any languages.
      SQL, C, PLs are ok, but you should write functions as fast as possible
      because they are called many times.</li>
  <li>You can only specify one of FILTER or FORCE_NOT_NULL options.
      Please re-implement FORCE_NOT_NULL-compatible FILTER functions if you need the feature.</li>
</ul>

<p>Here is an example of FILTER function.</p>
<pre>CREATE FUNCTION sample_filter(integer, text, text, real DEFAULT 0.05) RETURNS record
    AS $$ SELECT $1 * $4, upper($3) $$
    LANGUAGE SQL;
</pre>

<h2 id="install">Installation</h2>

<p>pg_bulkload can be installed same as standard contrib modules.</p>

<h3>Requirements</h3>
<p>
pg_bulkload installation assumes the following:
</p>
<ul>
<li>PostgreSQL must have been installed in advance,</li>
<li>The database has been initialized using <code>initdb</code>.</li>
</ul>

<h3 id="build">Installation from Source Code</h3>
<p>There are some requirement libraries. Please install them before build pg_bulkload.
<ul>
<li>PostgreSQL devel package : postgresqlxx-devel(RHEL), postgresql-server-dev-x.x(Ubuntu)</li>
<li>PAM devel package : pam-devel(RHEL), libpam-devel(Ubuntu)</li>
<li>Readline devel or libedit devel package : readline-devel or libedit-devel(RHEL), libreadline-dev or libedit-dev(Ubuntu)</li>
<li>C compiler and build utility : "Development Tools" (RHEL), build-essential(Ubuntu)</li>
</ul>
<p>You can build pg_bulkload with pgxs. It is optional to specify "USE_PGXS=1" explicitly.</p>
<pre>$ cd pg_bulkload
$ make USE_PGXS=1
$ su
$ make USE_PGXS=1 install</pre>

<p>You can use CREATE EXTENSION to register functions (Must be database superuser).</p>
<pre>$ psql database_name
database_name=# CREATE EXTENSION pg_bulkload;</pre>

<h3 id="rpm">Installation from RPM package</h3>
<p>
It can be installed from RPM package.
Please check the postgresql package is installed before you install pg_bulkload.
</p>
<p>
The following command will install pg_bulkload:
</p>
<pre># rpm -ivh pg_bulkload-&lt;version&gt;.rpm</pre>

<p>You can check whether the module has been installed with the following command:</p>
<pre># rpm -qa | grep pg_bulkload</pre>

<p>You can find where files are installed:</p>
<pre># rpm -qs pg_bulkload </pre>

<p>As similar to installing from source code, You can use CREATE EXTENSION to register functions (Must be database superuser).</p>
<pre>$ psql database_name
database_name=# CREATE EXTENSION pg_bulkload;</pre>

<h2 id="upgrade">Upgrade</h2>
<p>If you are upgrading from a previous version of pg_bulkload, just drop the old version from the database and install the new version.</p>
<p>The following three steps can be used to upgrade pg_bulkload.</p>
<ol>
<li>Execute a DROP EXTENSION statement to drop the old version of pg_bulkload from the database.</li>
<pre>database_name# DROP EXTENSION pg_bulkload;
DROP EXTENSION</pre>
<li>Remove the old version of pg_bulkload.</li>
<p>If installed from source code:</p>
<pre># make USE_PGXS=1 uninstall</pre>
<p>If installed from  RPM package:</p> 
<pre># rpm -e pg_bulkload-&lt;version&gt;.rpm</pre>
<li>Follow the <a href="#install">Installation</a> instructions to install a new version of pg_bulkload.</li>
</ol>

<h2 id="requirement">Requirements</h2>
<dl>
<dt>PostgreSQL versions</dt>
<dd>PostgreSQL 13, 14, 15, 16, 17</dd>
<dt>OS</dt>
<dd>RHEL 8, 9</dd>
</dl>

<h2 id="releasenote">Release Notes</h2>

<p>
<h4>3.1.22</h4>
<ul>
<li>Added support for PostgreSQL 17</li>
</ul>
</p>

<p>
<h4>3.1.21</h4>
<ul>
<li>Added support for PostgreSQL 16</li>
<li>Fixed incorrect version display of pg_bulkload</li>
<li>Fix duplication handling for large data set</li>
</ul>
</p>

<p>
<h4>3.1.20</h4>
<ul>
<li>Added support for PostgreSQL 15</li>
</ul>
</p>

<p>
<h4>3.1.19</h4>
<ul>
<li>Added support for PostgreSQL 14</li>
</ul>
</p>

<p>
<h4>3.1.18</h4>
<ul>
<li>Add a note to the documentation about using the "-o TYPE=FUNCTION" option, which was a problem left in version 3.1.17.</li>
<li>Fix build failure on Ubuntu environment</li>
</ul>
</p>

<p>
<h4>3.1.17</h4>
<ul>
<li>Added support for PostgreSQL 13</li>
<li>Fixed a crash when merging B-tree indexes (Sincerely thank you, Haruka Takatsuka-san, for reporting the problem and suggesting a solution.)</li>
</ul>
</p>

<p>
<h4>3.1.16</h4>
<ul>
<li>Added support for PostgreSQL 12</li>
<li>Fixed documentation to warn users of some risks of using parallel/multi-process mode</li>
<li>Document restriction that pg_bulkload supports only tables of "heap" access method</li>
</ul>
</p>

<p>
<h4>3.1.15</h4>
<ul>
<li>Added support for PostgreSQL 11</li>
<li>Fixed pg_bulkload to mitigate attacks described in CVE-2018-1058</li>
</ul>
</p>

<p>
<h4>3.1.14</h4>
<ul>
<li>Added support for PostgreSQL 10</li>
<li>Changed name of RPM to standard naming convention as follows:
From pg_bulkload-VERSIONX_X_X-X.pgXX.rhelX.rpm to pg_bulkload-X.X.XX-X.pgXX.rhelX.rpm</li>
</ul>
</p>

<p>
<h4>3.1.13</h4>
<ul>
<li>Fixed a case where pg_bulkload would crash when using MULTI_PROCESS mode and the input file contained malformed record</li>
<li>Prevented CSVParserRead() from requesting too much memory.  Before this update, requesting too much memory could cause an error</li>
</ul>
</p>

<p>
<h4>3.1.12</h4>
<ul>
<li>Update the version number shown when --version is specified</li>
</ul>
</p>

<p>
<h4>3.1.11</h4>
<ul>
<li>Fixed a bug in block number calculation in direct write mode. Due to this bug, pg_bulkload calculated wrong checksum in certain cases, especially, when a segment of the relation is about to get full.</li>
</ul>
</p>

<p>
<h4>3.1.10</h4>
<ul>
<li>Supports PostgreSQL 9.6</li>
</ul>
</p>

<p>
<h4>3.1.9</h4>
<ul>
<li>Supports PostgreSQL 9.5. </li>
<li>Added a robustness fix for possible unintentional behaviors caused by interactions with other dynamically loadable modules</li>
</ul>
</p>

<p>
<h4>3.1.8</h4>
<ul>
<li>Fix bug of CHECK_CONSTRAINTS = YES: PostgreSQL server can crash when load with CHECK_CONSTRAINTS = YES on PostgreSQL 9.4.1, 9.3.6, 9.2.10, 9.1.15, 9.0.19. </li>
</ul>
</p>

<p>
<h4>3.1.7</h4>
<ul>
<li>Supports PostgreSQL 9.4. </li>
</ul>
</p>

<p>
<h4>3.1.6</h4>
<ul>
<li>Fix bug of the WRITER = PARALLEL: pg_bulkload did not handle PGHOST environment variable correctly. Thus, it did not work when unix_socket_directory, unix_socket_directories after 9.3, is changed from /tmp. </li>
<li>Fix bug of filter functions: pg_bulkload fails if it use a filter function written by not SQL. This happens in PostgreSQL 9.2.4 and after.</li>
<li>Supports PostgreSQL 9.4beta1</li>
</ul>
</p>

<p>
<h4>3.1.5</h4>
<ul>
<li>Supports PostgreSQL 9.3. </li>
<li>Fix bugs about load to UNLOGGED tables</li>
</ul>
</p>

<p>
<h4>3.1.4</h4>
<ul>
<li>Fix bugs against PostgreSQL 9.2.4 There happened a memory leak when using a FILTER function written by SQL. </li>
<li>Fix in postgresql script There was the code for now-defunct option.</li>
</ul>
</p>

<p>
<h4>3.1.3</h4>
<ul>
<li>Fix bugs of the WRITER = PARALLEL. There was a case where the load data disappear.</li>
<li>Fix bugs of the WRITER = BUFFERED. There was a case where indexes on the update tables corrupt on the streaming-replictaioned slave node.</li>
</ul>
</p>

<h2 id="seealso">See Also</h2>
<a href="http://developer.postgresql.org/pgdocs/postgres/sql-copy.html">COPY</a>

<hr />
<div class="navigation">
  <a href="index.html">Top</a> &gt;
  <a href="pg_bulkload.html">pg_bulkload</a>
<div>
<p class="footer">Copyright (c) 2007-2025, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>

<script type="text/javascript">
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
</script>
<script type="text/javascript">
try {
var pageTracker = _gat._getTracker("UA-10244036-1");
pageTracker._trackPageview();
} catch(err) {}</script>
</body>
</html>