'\" t
.\"     Title: pgloader
.\"    Author: [see the "AUTHORS" section]
.\" Generator: DocBook XSL Stylesheets v1.76.1 <http://docbook.sf.net/>
.\"      Date: 01/06/2012
.\"    Manual: \ \&
.\"    Source: \ \&
.\"  Language: English
.\"
.TH "PGLOADER" "1" "01/06/2012" "\ \&" "\ \&"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
pgloader \- Import CSV data and Large Object to PostgreSQL
.SH "SYNOPSIS"
.sp
pgloader [\-\-version] [\-c configuration file] [\-p pedantic] [\-d debug] [\-v verbose] [\-q quiet] [\-s summary] [\-l loglevel] [\-L logfile] [\-n dryrun] [\-Cn count] [\-Fn from] [\-In from id] [\-E input files encoding] [\-R reformat:path] [Section|Filename \&...]
.SH "DESCRIPTION"
.sp
pgloader imports data from a flat file and insert it into a database table\&. It uses a flat file per database table, and you can configure as many Sections as you want, each one associating a table name and a data file\&.
.sp
Data are parsed and rewritten, then given to PostgreSQL COPY command\&. Parsing is necessary for dealing with end of lines and eventual trailing separator characters, and for column reordering: your flat data file may not have the same column order as the database table has\&.
.sp
pgloader is also able to load some large objects data into PostgreSQL, as of now only Informix UNLOAD data files are supported\&. This command gives large objects data location information into the main data file\&. pgloader parse it add the text or bytea content properly escaped to the COPY data\&.
.sp
pgloader issue some timing statistics every commit_every commits (see Configuration for this setting)\&. At the end of each section processing, a summary of overall operations, numbers of rows copied and commits, time it took in seconds, errors logged and database errors is issued\&.
.sp
pgloader is available from pgfoundry at \m[blue]\fBhttp://pgfoundry\&.org/projects/pgloader/\fR\m[], where you\(cqll find a debian package, a source package and an anonymous CVS\&.
.SH "ARGUMENTS"
.sp
pgloader as of version 2\&.3\&.3 accepts two kinds of arguments, either section names of file names\&. If both a section and a file exist with the same name, preference is given to the section, where you can edit your settings rather than using default ones\&.
.PP
Section
.RS 4
is the name of a configured Section describing some data to load
.sp
Section arguments are optional, if no section is given all configured sections are processed\&.
.RE
.PP
Filename
.RS 4
The name of a file containing the data to load\&.
pgloader
will internally setup a
Section
for this filename, with the default field separator or the given
\-\-field\-separator
and the
columns
parameter set to
\fI*\fR, and more importantly the format set to
CSV\&. It\(cqs the only supported format with sane enough defaults to apply here\&.
.RE
.SH "OPTIONS"
.sp
In order for pgloader to run, you have to edit a configuration file (see Configuration) consisting of Section definitions\&. Each section refers to a PostgreSQL table into which some data is to be loaded\&.
.PP
\-\-version
.RS 4
print out pgloader version, then quit\&.
.RE
.PP
\-c, \-\-config
.RS 4
specifies the configuration file to use\&. The default file name is pgloader\&.conf, searched into current working directory\&.
.RE
.PP
\-p, \-\-pedantic
.RS 4
activates the pedantic mode, where any warning is considered as a fatal error, thus stopping the processing of the input file\&.
.RE
.PP
\-d, \-\-debug
.RS 4
makes pgloader say it all about what it does\&. debug implies verbose\&.
.RE
.PP
\-v, \-\-verbose
.RS 4
makes pgloader very verbose about what it does\&.
.RE
.PP
\-q, \-\-quiet
.RS 4
makes pgloader very quiet about what it does: only output errors\&.
.RE
.PP
\-l, \-\-loglevel
.RS 4
log level to use when reporting to the console, see
client_min_messages\&.
.RE
.PP
\-L, \-\-logfile
.RS 4
file where to log messages, see
log_min_messages\&.
.RE
.PP
\-r, \-\-reject\-log
.RS 4
Filename, with a single "%s" placeholder, where to store the bad data logs (that\(cqs the error messages given by PostgreSQL)\&. If you want to put a percent in the file name, write it
\fI%%\fR\&.
.RE
.PP
\-j, \-\-reject\-data
.RS 4
Filename, with a single "%s" placeholder, where to store the bad data (the exact lines that didn\(cqt make it from your input file)\&. If you want to put a percent in the file name, write it
\fI%%\fR\&.
.RE
.PP
\-s, \-\-summary
.RS 4
makes pgloader print a
\fInice\fR
summary at the end of operations\&.
.RE
.PP
\-n, \-\-dry\-run
.RS 4
makes pgloader simulate operations, that implies no database connection and no data extraction from blob files\&.
.RE
.PP
\-D, \-\-disable\-triggers
.RS 4
makes
pgloader
issue a
ALTER TABLE <table> DISABLE TRIGGER ALL
before loading the data, and
ENABLE
them again once data is loaded\&.
.RE
.PP
\-T, \-\-truncate
.RS 4
makes
pgloader
issue a
TRUNCATE <table>
SQL command before importing data\&.
.RE
.PP
\-V, \-\-vacuum
.RS 4
makes
pgloader
issue a
VACUUM ANALYZE <table>
SQL command after data loading\&.
.RE
.PP
\-C, \-\-count
.RS 4
Number of input lines to process, default is to process all the input lines\&.
.RE
.PP
\-F, \-\-from
.RS 4
Input line number from which we begin to process (and count)\&. pgloader will skip all preceding lines\&.
.sp
You can\(cqt use both \-F and \-I at the same time\&.
.RE
.PP
\-I, \-\-from\-id
.RS 4
From which id do we begin to process (and count) input lines\&.
.sp
When a composite key is used, you have to give each column of the key separated by comma, on the form col_name=value\&.
.sp
Please notice using the \-\-from\-id option implies pgloader will try to get row id of each row, it being on the interval processed or not\&. This could have some performance impact, and you may end up preferring to use \-\-from instead\&.
.sp
.if n \{\
.RS 4
.\}
.nf
Example: pgloader \-I col1:val1,col2:val2
.fi
.if n \{\
.RE
.\}
.sp
You can\(cqt use both \-F and \-I at the same time\&.
.RE
.PP
\-f, \-\-field\-sep
.RS 4
Default field separator to use, when not set
pgloader
will use
\fI|\fR\&. Useful when using
filename
arguments rather than
section
ones\&.
.RE
.PP
\-E, \-\-encoding
.RS 4
Input data files encoding\&. Defaults to
\fIlatin9\fR\&.
.RE
.PP
\-o, \-\-pg\-options
.RS 4
Any option to give to the PostgreSQL server by mean of the
SET
command\&. You can use this argument more than once to set more than one option\&.
.sp
Example: \-o standard_conforming_strings=on \-o client_encoding=utf8
.RE
.PP
\-t, \-\-section\-threads
.RS 4
How many threads per section to use, defaults to 1\&. The command line value override the configuration file one\&.
.RE
.PP
\-m, \-\-max\-parallel\-sections
.RS 4
How many sections to load in parallel, defaults to 1\&. The command line value override the configuration file one\&. That\(cqs a max value because you will end up having less sections to load than this number\&.
.RE
.PP
\-R, \-\-reformat_path
.RS 4
PATH where to find reformat python modules, defaults to
/usr/share/pgloader/reformat\&. See
reformat_path
option for syntax and default value\&.
.RE
.PP
\-1, \-\-psycopg1
.RS 4
Force usage of psycopg version 1\&.
.RE
.PP
\-2, \-\-psycopg2
.RS 4
Force usage of psycopg version 2\&.
.RE
.PP
\-\-psycopg\-version
.RS 4
Force
pgloader
to use given version of psycopg, either
1
or
2\&.
.RE
.SH "INTERNAL USAGE OPTIONS"
.sp
Those have been developped for internal pgloader usage only, but still need to be documented\&. Also, they are maintained and you could find an usage for them\&.
.PP
\-\-load\-from\-stdin
.RS 4
Consider standard input as the data file\&. When using this function, either give a section name from which to apply all the setup except for the
filename
to load from, or use
\-\-load\-to\-table\&.
.RE
.PP
\-\-load\-to\-table
.RS 4
This option\(cqs argument must be the name of the PostgreSQL table you\(cqre loading the data to, it\(cqs useful when you want to load from
stdin
and avoid editing a full configuration section\&.
.RE
.PP
\-\-boundaries
.RS 4
Allow for limiting the range of bytes to read and process, must be given in the X\&.\&.Y format, with X and Y integers\&.
.RE
.SH "GLOBAL CONFIGURATION SECTION"
.sp
The configuration file has a \&.ini file syntax, its first section has to be the pgsql one, defining how to access to the PostgreSQL database server where to load data\&. Then you may define any number of sections, each one describing a data loading task to be performed by pgloader\&.
.sp
The [pgsql] section has the following options, which all must be set\&.
.PP
host
.RS 4
PostgreSQL database server name, for example
localhost\&. For Unix Domain connection, give the directory where to find the Unix Socket, e\&.g\&.
/tmp\&. The
port
will then get used to locate the Unix Socket filename\&.
.RE
.PP
port
.RS 4
PostgreSQL database server listening port, 5432\&. You have to fill this entry\&.
.RE
.PP
base
.RS 4
The name of the database you want to load data into\&.
.RE
.PP
user
.RS 4
Connecting PostgreSQL user name\&.
.RE
.PP
pass
.RS 4
The password of the user\&. The better is to grant a trust access privilege in PostgreSQL
pg_hba\&.conf\&. Then you can set this entry to whatever value you want to\&.
.RE
.PP
client_encoding
.RS 4
Set this parameter to have
pgloader
connects to PostgreSQL using this encoding\&.
.sp
This parameter is optional and defaults to
\fIlatin9\fR\&.
.sp
As of
pgloader 2\&.3\&.3
you can also use
pg_option_client_encoding
which is the more general approach\&.
.RE
.PP
datestyle
.RS 4
Set this parameter to have
pgloader
connects to PostgreSQL using this datestyle setting\&.
.sp
This parameter is optional and has no default value, thus pgloader will use whatever your PostgreSQL is configured to as default\&.
.sp
As of
pgloader 2\&.3\&.3
you can also use
pg_option_datestyle
which is the more general approach\&.
.RE
.PP
pg_option_<foo>
.RS 4
Replace <foo> with any option you\(cqre allowed to setup for the session only with the
SET
command, and
pgloader
will do just that for you\&. Consider for example
pg_option_standard_conforming_strings = on\&.
.RE
.PP
copy_every
.RS 4
When issuing
COPY
PostgreSQL commands, pgloader will not make a single big
COPY
attempt, but copy copy_every lines at a time\&.
.sp
This parameter is optional and defaults to 10000\&.
.RE
.PP
copy_delimiter
.RS 4
The field separator to use in
COPY FROM
produced statements\&. If you don\(cqt specify this, the same separator as the one given in
field_sep
parameter will be used\&.
.sp
Please note PostgreSQL requires a single char properly encoded (see your
client_encoding
parameter), or it abort in error and even may crash\&.
.sp
This parameter is optional and defaults to
field_sep\&.
.RE
.PP
newline_escapes
.RS 4
For parameter effect description, see below (same name, table local setting)\&.
.sp
You can setup here a global escape character, to be considered on each and every column of each and every text\-format table defined thereafter\&.
.RE
.PP
null
.RS 4
You can configure here how null value is represented into your flat data file\&.
.sp
This parameter is optional and defaults to
\*(Aq\*(Aq
(that is
empty string)\&.
.RE
.PP
empty_string
.RS 4
You can configure here how empty values are represented into your flat data file\&.
.sp
This parameter is optional and defaults to
\*(Aq\e \*(Aq
(that is backslash followed by space)\&.
.RE
.PP
reformat_path
.RS 4
When using
reformat
option, provide here a colon separated path list where to look for reformatting module\&.
.sp
.if n \{\
.RS 4
.\}
.nf
reformat_path = \&.:/home/dim/PostgreSQL/pgfoundry/pgloader/reformat
.fi
.if n \{\
.RE
.\}
.sp
The directories given here should exist and contain a
__init__\&.py
file (for python to consider them as packages), the only modules and functions used in the package will be the one you configure with
reformat
section specific option\&.
.sp
Default value is
/usr/share/pgloader/reformat, which is where the provided
debian
package of
pgloader
installs the
reformat
modules\&.
.sp
If the
\-R
or
\-\-reformat_path
command line option is used, it will have precedence over configuration file setting\&.
.RE
.PP
client_min_messages
.RS 4
Minimum level of messages to print to the console while running\&. Defined levels are
DEBUG,
INFO,
WARNING,
ERROR,
CRITICAL, from min to max\&.
.RE
.PP
log_min_messages
.RS 4
Minimum level of messages to print out to the log file, which defaults to
/tmp/pgloader\&.log\&. See
client_min_messages
for available levels\&.
.RE
.PP
log_file
.RS 4
Relative or absolute path to the
log_file
where to log messages of level of at least
log_min_messages
level\&. The
\fIdirname\fR
of the given
log_file, if it doesn\(cqt exists, will be created by
pgloader\&. If any error prevents
pgloader
to use the
log_file, it will default to using
/tmp/pgloader\&.log
and say so\&.
.RE
.PP
lc_messages
.RS 4
The PostgreSQL session will use this
LC_MESSAGES
setting if given, defaults to server configuration by not issuing anything with respect to this setting when not set\&.
.RE
.PP
max_parallel_sections
.RS 4
Number of sections to load at the same time, each in its own thread\&. Default to
1, which is the legacy behaviour and the more common wanted one\&.
.RE
.SH "COMMON FORMAT CONFIGURATION PARAMETERS"
.sp
You then can define any number of data section, and give them an arbitrary name\&. Some options are required, some are actually optional, in which case it is said so thereafter\&.
.sp
First, we\(cqll go through common parameters, applicable whichever format of data you\(cqre referring to\&. Then text\-format only parameters will be presented, followed by csv\-only parameters\&.
.PP
template
.RS 4
When this option is set, current section is to be considered a template, that is only read from section(s) using it as so (see
use_template
below)\&.
.sp
The value given to the option is not taken into account by
pgloader, only the fact that it exists has meaning\&. But
ConfigParser
requires a value to be affected to consider the option set\&. Use
True
as a value, for example\&.
.RE
.PP
use_template
.RS 4
This option setting have to be the name of a template section, which can define the exact same options as a normal section\&. If the actual section and the
use_template
template section both define the same option, the former is used: actual setting overrides template\(cqs one\&.
.RE
.PP
table
.RS 4
The table name of the database where to load data\&.
.RE
.PP
format
.RS 4
The format data are to be found, either
text,
csv
or
fixed\&.
.sp
See next sections for format specific options\&.
.RE
.PP
filename
.RS 4
The absolute path to the input data file\&. The large object files are to be found into the same directory\&. Their name can be in the used by
pgloader\&.
.RE
.PP
input_encoding
.RS 4
The encoding of the configured
filename\&.
.RE
.PP
reject_log
.RS 4
In case of errors processing input data, a human readable log per rejected input data line is produced into the
reject_log
file\&.
.RE
.PP
reject_data
.RS 4
In case of errors processing input data, the rejected input line is appended to the
reject_data
file\&.
.RE
.PP
field_sep
.RS 4
The field separator used into the data file\&. The same separator will be used by the generated
COPY
commands, thus
pgloader
does not have to deal with escaping the delimiter it uses (input data has to have escaped it)\&.
.sp
This parameter is optional and defaults to pipe char
\*(Aq|\*(Aq\&.
.RE
.PP
client_encoding
.RS 4
Set this parameter to have
pgloader
connects to PostgreSQL using this encoding\&.
.sp
This parameter is optional and defaults to
\fIlatin9\fR\&.
.sp
As of
pgloader 2\&.3\&.3
you can also use
pg_option_client_encoding
which is the more general approach\&.
.RE
.PP
datestyle
.RS 4
Set this parameter to have
pgloader
connects to PostgreSQL using this datestyle setting\&.
.sp
This parameter is optional and has no default value, thus pgloader will use whatever your PostgreSQL is configured to as default\&.
.sp
As of
pgloader 2\&.3\&.3
you can also use
pg_option_datestyle
which is the more general approach\&.
.RE
.PP
pg_option_<foo>
.RS 4
Replace <foo> with any option you\(cqre allowed to setup for the session only with the
SET
command, and
pgloader
will do just that for you\&. Consider for example
pg_option_standard_conforming_strings = on\&.
.RE
.PP
null
.RS 4
You can configure here how null value is represented into your flat data file\&.
.sp
This parameter is optional and defaults to
\*(Aq\*(Aq
(that is empty string)\&. If defined on a table level, this local value will overwrite the global one\&.
.RE
.PP
empty_string
.RS 4
You can configure here how empty values are represented into your flat data file\&.
.sp
This parameter is optional and defaults to \*(Aq\e \*(Aq (that is backslash followed by space)\&. If defined on a table level, this local value will overwrite the global one\&.
.RE
.PP
skip_head_lines
.RS 4
Skip the
n
first lines of the given files (headers)
.RE
.PP
columns
.RS 4
You can define here table columns, by giving their names and optionally column number (as found into your data file, and counting from 1) separated by a colon\&.
.sp
.if n \{\
.RS 4
.\}
.nf
columns = x, y, a, b, d:6, c:5
.fi
.if n \{\
.RE
.\}
.sp
Note you\(cqll have to define here all the columns to be found in data file, whether you want to use them all or not\&. When not using them all, use the
only_cols
parameter to restrict\&.
.sp
As of
pgloader 2\&.2
the column list used might not be the same as the table columns definition\&.
.sp
As of
pgloader 2\&.2\&.1
you can omit column numbering if you want to, a counter is then maintained for you, starting from 1 and set to
last value + 1
on each column, where
last value
was either computed or given in the config\&. So you can even omit only
\fIsome\fR
columns in there\&.
.sp
In case you have a lot a columns per table, you will want to use multiple lines for this parameter value\&. Python ConfigParser module knows how to read multi\-line parameters, you don\(cqt have to escape anything\&.
.sp
An easy way to get the list of attributes (columns) of your tables (say
a,
b
and
c) is by the following query:
.sp
.if n \{\
.RS 4
.\}
.nf
BEGIN;
CREATE AGGREGATE array_acc(anyelement) (
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = \*(Aq{}\*(Aq
);
.fi
.if n \{\
.RE
.\}
.sp
.if n \{\
.RS 4
.\}
.nf
  SELECT relname, array_acc(attname)
    FROM pg_attribute a join pg_class c on a\&.attrelid = c\&.oid
   WHERE relname in (\*(Aqa\*(Aq, \*(Aqb\*(Aq, \*(Aqc\*(Aq)
         and attname not in (\*(Aqtableoid\*(Aq,\*(Aqcmax\*(Aq,\*(Aqxmax\*(Aq,\*(Aqcmin\*(Aq,\*(Aqxmin\*(Aq,\*(Aqctid\*(Aq)
GROUP BY relname;
.fi
.if n \{\
.RE
.\}
.sp
.if n \{\
.RS 4
.\}
.nf
ROLLBACK;
.fi
.if n \{\
.RE
.\}
.sp
As of
pgloader 2\&.3\&.0
you can simply set
columns = *
and
pgloader
will issue the needed SQL for you\&. This only works if your data file and your table definition both present the columns in the exact same order, obviously\&.
.sp
Internally,
pgloader
will issue a
COPY
statement without the column names if possible, meaning when
only_cols
is not used at the same time as
columns = *
is used\&.
.RE
.PP
user_defined_columns
.RS 4
Those are special columns not found in the data file but which you want to load into the database\&. The configuration options beginning with
udc_
are taken as column names with constant values\&. The following example define the column
c
as having the value
constant value
for each and every row of the input data file\&.
.sp
.if n \{\
.RS 4
.\}
.nf
udc_c = constant value
.fi
.if n \{\
.RE
.\}
.sp
The option
copy_columns
is used to define the exact
columnsList
given to
COPY\&.
.sp
A simple use case is the loading into the same database table of data coming from more than one file\&. If you need to keep track of the data origin, add a column to the table model and define a
\fIudc_\fR
for
pgloader
to add a constant value in the database\&.
.sp
Using user\-defined columns require defining
copy_columns
and is not compatible with
only_cols
usage\&.
.RE
.PP
copy_columns
.RS 4
This options defines the columns to load from the input data file and the user defined columns, and in which order to do this\&. Place here the column names separated by commas\&.
.sp
.if n \{\
.RS 4
.\}
.nf
copy_columns = b, c, d
.fi
.if n \{\
.RE
.\}
.sp
This option is required if any user column is defined, and conflicts with the
only_cols
option\&. It won\(cqt have any effect when used in a section where no user column is defined\&.
.RE
.PP
only_cols
.RS 4
If you want to only load a part of the columns you have into the data file, this option let you define which columns you\(cqre interested in\&.
only_col
is a comma separated list of ranges or values, as in following example\&.
.sp
.if n \{\
.RS 4
.\}
.nf
only_cols = 1\-3, 5
.fi
.if n \{\
.RE
.\}
.sp
This parameter is optional and defaults to the list of all columns given on the columns parameter list, in the colname order\&.
.sp
This option conflicts with user defined columns and
copy_columns
option\&.
.RE
.PP
blob_columns
.RS 4
The definition of the columns where to find some blob or clob reference\&. This definition is composed by a table column name, a column number (counting from one) reference into the Informix
UNLOAD
data file, and a large object type, separated by a colon\&. You can have several columns in this field, separated by a comma\&.
.sp
Supported large objects type are Informix blob and clob, the awaited configuration string are respectively
ifx_blob
for binary (bytea) content type and
ifx_clob
for text type values\&.
.sp
Here\(cqs an example:
.sp
.if n \{\
.RS 4
.\}
.nf
blob_type = clob_column:3:ifx_blob, other_clob_column:5:ifx_clob
.fi
.if n \{\
.RE
.\}
.RE
.PP
reformat
.RS 4
Use this option when you need to preprocess some column data with
pgloader
reformatting modules, or your own\&. The value of this option is a comma separated list of columns to rewrite, which are a colon separated list of column name, reformat module name, reformat function name\&. Here\(cqs an example to reformat column
dt_cx
with the
mysql\&.timestamp()
reformatting function:
.sp
.if n \{\
.RS 4
.\}
.nf
reformat = dt_cx:mysql:timestamp
.fi
.if n \{\
.RE
.\}
.sp
See global setting option
reformat_path
for configuring where
pgloader
will look for reformat packages and modules\&.
.sp
If you want to write a new formating function, provide a python package called
reformat
(a directory of this name containing an empty
__init__\&.py
file will do) and place in there arbitrary named modules (foo\&.py
files) containing functions with the following signature:
.sp
.if n \{\
.RS 4
.\}
.nf
def bar(reject, input)
.fi
.if n \{\
.RE
.\}
.sp
The reject object has a
log(self, messages, data = None)
method for you to log errors into
section\&.rej\&.log
and
section\&.rej
files\&.
.RE
.SH "PARALLEL LOADING"
.sp
This section is about loading a single given section by multiple threads\&. To load several sections at once in a parallel fashion, please refer to max_parallel_sections global option\&.
.PP
section_threads
.RS 4
This option allows to configure how many threads
pgloader
will use to process current section\&. See
split_file_reading
for more information about how those threads will serve the loading\&. Defaults to
1, which is the legacy behaviour and the more needed one too\&.
.RE
.PP
split_file_reading
.RS 4
This option is only used by
pgloader
when
section_threads
is more than
1, and configures how the work will be spread to threads\&. It defaults to
False\&.
.sp
When
split_file_reading
is
True,
pgloader
will have each section thread process a part of the input file\&. The file splitting is done at the byte level, not at the line count level: knowing how many lines the input file has would require loading it first\&...
.sp
When
split_file_reading
is
False,
pgloader
will have one thread read the input file and give workers threads input lines to process in a round\-robin fashion\&. Please note the reader thread will have to parse the lines (according to
format
parameter)\&.
.RE
.PP
rrqueue_size
.RS 4
When
split_file_reading
is
False, this is the size of the
pgloader
queue used to balance input lines to workers threads\&. Instead of giving them one line at a time in a round\-robin fashion,
pgloader
will feed workers
rrqueue_size
lines at a time\&. This defaults to
copy_every\&.
.RE
.SH "TEXT FORMAT CONFIGURATION PARAMETERS"
.PP
field_count
.RS 4
The
UNLOAD
command does not escape newlines when they appear into table data\&. Hence, you may obtain multi\-line data files, where a single database row (say tuple if you prefer to) can span multiple physical lines into the unloaded file\&.
.sp
If this is your case, you may want to configure here the number of columns per tuple\&. Then pgloader will count columns and buffer line input in order to re\-assemble several physical lines into one data row when needed\&.
.sp
This parameter is optional\&.
.RE
.PP
trailing_sep
.RS 4
If this option is set to True, the input data file is known to append a
field_sep
as the last character of each of its lines\&. With this option set, this last character is then not considered as a field separator\&.
.sp
This parameter is optional and defaults to
False\&.
.RE
.PP
newline_escapes
.RS 4
Sometimes the input data file has field values containing newlines, and the export program used (as Informix
UNLOAD
command) escape in\-field newlines\&. So you want
pgloader
to keep those newlines, while at the same time preserving them\&.
.sp
This option does the described work on specified fields and considering the escaping character you configure, following this syntax:
.sp
.if n \{\
.RS 4
.\}
.nf
newline_escapes = colname:\e, other_colname:\(sc
.fi
.if n \{\
.RE
.\}
.sp
This parameter is optional, and the extra work is only done when set\&. You can configure
newline_escapes
for as many fields as necessary, and you may configure a different escaping character each time\&.
.sp
Please note that at the moment,
pgloader
does only support one character length
newline_escapes\&.
.sp
When both a global (see
[pgsql]
section)
newline_escapes
parameter and a table local one are set,
pgloader
issues a warning and only consider the global setting\&.
.RE
.SH "CSV FORMAT CONFIGURATION PARAMETERS"
.PP
doublequote
.RS 4
Controls how instances of
quotechar
appearing inside a field should be themselves be quoted\&. When
True, the character is doubled\&. When
False, the
escapechar
is used as a prefix to the
quotechar\&. It defaults to
True\&.
.RE
.PP
escapechar
.RS 4
A one\-character string used by the writer to escape the delimiter if quoting is set to
QUOTE_NONE
and the
quotechar
if
doublequote
is
False\&. On reading, the
escapechar
removes any special meaning from the following character\&. It defaults to
None, which disables escaping\&.
.RE
.PP
quotechar
.RS 4
A one\-character string used to quote fields containing special characters, such as the
delimiter
or
quotechar, or which contain new\-line characters\&. It defaults to
\fI"\fR\&.
.RE
.PP
skipinitialspace
.RS 4
When
True, whitespace immediately following the
delimiter
is ignored\&. The default is
False\&.
.RE
.PP
field_size_limit
.RS 4
Sets the maximum field size allowed by the python
CSV
parser\&. Accepts an number of bytes (integer), or a string containing a number then one of those units (case sensitive):
kB,
MB,
GB,
TB\&. Requires a at least python 2\&.5\&.
.RE
.SH "FIXED FORMAT CONFIGURATION PARAMETERS"
.PP
fixed_specs
.RS 4
This parameter allows to specify start position and byte length for each columns to load\&. Syntax is
column_name:start:len, separated by comas\&.
.sp
.if n \{\
.RS 4
.\}
.nf
fixed_specs = a:0:10, b:10:8, c:18:8, d:26:17
.fi
.if n \{\
.RE
.\}
.RE
.SH "CONFIGURATION EXAMPLE"
.sp
Please see the given configuration example which should be distributed in /usr/share/doc/pgloader/examples/pgloader\&.conf\&.
.sp
The example configuration file comes with example data and can be used a unit test of pgloader\&.
.SH "EXIT STATUS"
.PP
0
.RS 4
success; including the case when some records were rejected due constraint violations
.RE
.PP
1
.RS 4
a fatal error, such as a database connection failure, has occured
.RE
.SH "HISTORY"
.sp
pgloader has first been a tcl tool written by Jan Wieck and released by Christopher Kings\-Lynne, who created the \m[blue]\fBpgfoundry\fR\m[]\&\s-2\u[1]\d\s+2 project for it to be published\&. Later on, Jean\-Paul Argudo took over the maintenance\&. When it became clear that it would be easier to rewrite it in another language than to properly learn tcl and develop some missing options, pgloader was rewritten in python by Dimitri Fontaine\&.
.sp
pgloader was rewritten to act as an Informix to PostgreSQL migration helper which imported Informix large objects directly into a PostgreSQL database\&.
.sp
Then as we got some data we couldn\(cqt file tools to care about, we decided ifx_blob would become pgloader, as it had to be able to import all Informix UNLOAD data\&. Those data contains escaped separator into unquoted data field and multi\-lines fields (\er and \en are not escaped)\&.
.sp
pgloader has since gained some more features allowing it to directly import mysqldump \-T data, and is known to be used in production environments where a PostgreSQL database is used for reporting against data from several servers running different RDBMS softwares\&.
.SH "BUGS"
.sp
Please report bugs to Dimitri Fontaine <\m[blue]\fBdim@tapoueh\&.org\fR\m[]\&\s-2\u[2]\d\s+2>, and see current list of known bugs in the BUGS\&.txt distributed file (debian package includes it at /usr/share/doc/pgloader/BUGS\&.txt or online at following url: \m[blue]\fBhttp://pgloader\&.projects\&.postgresql\&.org/dev/BUGS\&.html\fR\m[]\&.
.SH "AUTHORS"
.sp
pgloader is written by Dimitri Fontaine <\m[blue]\fBdim@tapoueh\&.org\fR\m[]\&\s-2\u[2]\d\s+2>\&.
.SH "NOTES"
.IP " 1." 4
pgfoundry
.RS 4
\%http://pgfoundry.org
.RE
.IP " 2." 4
dim@tapoueh.org
.RS 4
\%mailto:dim@tapoueh.org
.RE