In a previous article we detailed how to use pgloader, let’s now see how to write the pgloader.conf that instructs pgloader about what to do.

This file is expected in the INI format, with a global section then one section per file you want to import. The global section defines some default options and how to connect to the PostgreSQL server.

The configuration setup is fully documented on the pgloader man page that you can even easily find online. As all unix style man pages, though, it’s more a complete reference than introductory material. Let’s review.

global section

Here’s the global section of the examples/pgloader.conf file of the source files. Well, some options are debugger only options, really, so I changed their value so that what you see here is a better starting point.

[pgsql]
base = pgloader

log_file            = /tmp/pgloader.log
log_min_messages    = INFO
client_min_messages = WARNING

lc_messages         = C
pg_option_client_encoding = 'utf-8'
pg_option_standard_conforming_strings = on
pg_option_work_mem = 128MB

copy_every      = 15000

null         = ""
empty_string = "\ "

max_parallel_sections = 4

You don’t see all the connection setup, here base was enough. You might need to setup host, port and user, and maybe even pass, too, to be able to connect to the PostgreSQL server.

The logging options allows you to set a file where to log all pgloader messages, that are categorized as either DEBUG, INFO, WARNING, ERROR or CRITICAL. The options log_min_messages and client_min_messages are another good idea stolen from PostgreSQL and allow you to setup the level of chatter you want to see on the interactive console (standard output and standard error streams) and on the log file.

Please note that the DEBUG level will produce more that 3 times as many data as the data file you’re importing. If you’re not a pgloader contributor or helping them, well, debug it, you want to avoid setting the log chatter to this value.

The client_encoding will be SET by pgloader on the PostgreSQL connection it establish. You can now even set any parameter you want by using the pg_option_parameter_name magic settings. Note that the command line option --pg-options (or -o for brevity) allows you to override that.

Then, the copy_every parameter is set to 5 in the examples, because the test files are containing less than 10 lines and we want to test several batches of commits when using them. So for your real loading, stick to default parameters ( 10 000 lines per COPY command), or more. You can play with this parameter, depending on the network (or local access) and disk system you’re using you might see improvements by reducing it or enlarging it. There’s no so much theory of operation as empirical testing and setting here. For a one-off operation, just remove the lines from the configuration.

The parameters null and empty_string are related to interpreting the data in the text or csv files you have, and the documentation is quite clear about them. Note that you have global setting and per-section setting too.

The last parameter of this example, max_parallel_sections, is detailed later in the article.

files section

After the global section come as many sections as you have file to load. Plus the template sections, that are only there so that you can share a bunch of parameters in more than one section. Picture a series of data file all of the same format, the only thing that will change is the filename. Use a template section in this case!

Let’s see an example:

[simple_tmpl]
template     = True
format       = text
datestyle    = dmy
field_sep    = |
trailing_sep = True

[simple]
use_template    = simple_tmpl
table           = simple
filename        = simple/simple.data
columns         = a:1, b:3, c:2
skip_head_lines = 2

# those reject settings are defaults one
reject_log   = /tmp/simple.rej.log
reject_data  = /tmp/simple.rej

[partial]
table        = partial
format       = text
filename     = partial/partial.data
field_sep    = %
columns      = *
only_cols    = 1-3, 5

That’s 2 of the examples from the examples/pgloader.conf file, in 3 sections so that we see one template example. Of course, having a single section using the template, it’s just here for the example.

data file format

The most important setting that you have to care about is the file format. Your choice here is either text, csv or fixed. Mostly, what we are given nowadays is csv. You might remember having read that the nice thing about standards is that there’s so many to choose from… well, the csv land is one where it’s pretty hard to find different producers that understand it the same way.

So when you fail to have pgloader load your mostly csv files with a csv setup, it’s time to consider using text instead. The text file format accept a lot of tunables to adapt to crazy situations, but is all python code when the python csv module is a C-coded module, more efficient.

If you’re wondering what kind of format we’re talking about here, here’s the cluttered pgloader example for your reading pleasure, using ^ (carret) as the field separator:

1^some multi\
line text with\
newline escaping^and some other data following^
2^and another line^clean^
3^and\
a last multiline\
escaped line
with a missing\
escaping^just to test^
4^\ ^empty value^
5^^null value^
6^multi line\
escaped value\
\
with empty line\
embeded^last line^

And here’s what we get by loading that:

pgloader/examples$ pgloader -c pgloader.conf -s cluttered
Table name        |    duration |    size |  copy rows |     errors 
====================================================================
cluttered         |      0.193s |       - |          6 |          0

pgloader/examples$ psql pgloader -c "table cluttered;"
 a |               b               |        c         
---+-------------------------------+------------------
 1 | and some other data following | some multi
                                   : line text with
                                   : newline escaping
 2 | clean                         | and another line
 3 | just to test                  | and
                                   : a last multiline
                                   : escaped line
                                   : with a missing
                                   : escaping
 4 | empty value                   | 
 5 | null value                    | 
 6 | last line                     | multi line
                                   : escaped value
                                   : 
                                   : with empty line
                                   : embeded
(6 rows)

So when you have such kind of data, well, it might be that pgloader is still able to help you!

Please refer to the pgloader man page to know about each and every parameter that you can define and the values accepted, etc. And the fixed data format is to be used when you’re not given a field separator but field positions in the file. Yes, we still encounter those from time to time. Who needs variable size storage, after all?