Including csvlog in the log_destination list
provides a convenient way to import log files into a database table.
This option emits log lines in comma-separated-value format,
with these columns: timestamp with milliseconds, user name, database
name, process ID, host:port number, session ID, per-session or -process line
number, command tag, session start time, virtual transaction ID,
regular transaction id, error severity, SQL state code, error message,
error message detail, hint, internal query that led to the error (if
any), character count of the error position thereof, error context,
user query that led to the error (if any and enabled by
log_min_error_statement), character count of the error
position thereof, location of the error in the PostgreSQL source code
(if log_error_verbosity is set to verbose).
Here is a sample table definition for storing CSV-format log output:
CREATE TABLE postgres_log
log_time timestamp(3) with time zone,
session_start_time timestamp with time zone,
PRIMARY KEY (session_id, session_line_num)
To import a log file into this table, use the COPY FROM
COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
There are a few things you need to do to simplify importing CSV log
files easily and automatically:
Set log_filename and
log_rotation_age to provide a consistent,
predictable naming scheme for your log files. This lets you
predict what the file name will be and know when an individual log
file is complete and therefore ready to be imported.
Set log_rotation_size to 0 to disable
size-based log rotation, as it makes the log file name difficult
Set log_truncate_on_rotation to on so
that old log data isn't mixed with the new in the same file.
The table definition above includes a primary key specification.
This is useful to protect against accidentally importing the same
information twice. The COPY command commits all of the
data it imports at one time, so any error will cause the entire
import to fail. If you import a partial log file and later import
the file again when it is complete, the primary key violation will
cause the import to fail. Wait until the log is complete and
closed before importing. This procedure will also protect against
accidentally importing a partial line that hasn't been completely
written, which would also cause COPY to fail.