Importing LARGE quantity of data into a PostgreSQL database

Question

I am importing a large amount of data into a PostgreSQL server (across multiple databases on the server).

When I last attempted to import the data, it effectively "crashed" my machine (Ubuntu 10.0.4), because /var/log/postgres contained over 360G of files (and I was only about 1 half way through the import).

I am not sure what the log files are for (I am guessing it must be for ACID compliancy etc). But I would like to know if there is a way to reduce the size of the log files generated during import of a large amount of data.

• user1822: I don't know Ubuntu but I think /var/log/postgres contains the regular logfiles. Just turn down the loglevel to write less logging information: postgresql.org/docs/current/static/runtime-config-logging.html
• JohnP: Along with Erwins tips you could try setting log_truncate_on_rotation in order to regulary truncate the log on rotation.

Answer 1

Log files in /var/log/postgres are only for your information (in a standard installation) and do not serve the system itself, especially not "ACID compliancy". They need to be writable once configured, that's all the server needs.

There are a number of settings in your postgresql.conf that govern what is logged. Most of them can also be set at the command line to overrule the setting in the config file. We are not talking about WAL files, which are not usually placed in /var/log/postgres.

I would drastically reduce the verbosity in your case. Among others, I would set (and reload the server):
log_statement = none

Depending how you import the data, you probably need to do more than that. Look at:
log_min_messages

To skip writing log files altogether you can set:
log_destination = 'stderr' ...

More in the manual.

Answer 2

Assuming the default postgresql.conf as shipped with Ubuntu's PostgreSQL package, a plausible reason for these huge log files would be that your data import would generate tons on warnings like this:

2012年06月11日 14:00:01 CEST WARNING: nonstandard use of \ in a string literal at character 25
2012年06月11日 14:00:01 CEST HINT: Use the escape string syntax for backslashes, e.g., E'\'.

Run sudo more /var/log/postgresql/postgresql-8.4-main.log to check the actual contents of the latest log file.

If these warnings are the reason why the log files are huge, they can be muted by setting escape_string_warning to OFF in /etc/postgresql/8.4/main/postgresql.conf

See the compatibility notes in postgresql 8.4 docs for why this warning kicks in.

The other default logging settings of PostgreSQL on Ubuntu are:

• log_destination='stderr'
• log_statement=none
• log_connections=off

These are sane values that should not produce log overflow.

The log rotation is handled by logrotate and configured in /etc/logrotate.d/postgresql-common.

By default, it's:

/var/log/postgresql/*.log {
weekly
rotate 10 copytruncate
delaycompress
compress
notifempty
missingok
}

Should you want to reduce the number of log files or accelerate their deletion, this is the file to modify. But it would better to identify and suppress the root cause of these huge logs.