PostgreSQL: understand log_directory, log_filename, and log_file_mode before going to production

Configuring logging in PostgreSQL seems trivial until the moment someone needs an audit trail, the data disk fills up because of a forgotten log_statement = 'all', or the log is missing from an incident report at three in the morning. In the post "All Your GUCs in a Row: log_directory, log_filename, and log_file_mode", published on Planet PostgreSQL, the author of The Build blog dissects the three parameters that describe the files the logging collector writes: where they live, how they're named, and who can read them. The central thesis is direct: the defaults for all three are fine for a laptop and wrong for a server, and the reasons are more mechanical than the documentation lets on.

Worth noting the caveat the author himself makes: the three parameters do nothing while logging_collector is off. They only govern the collector's behavior. Without a collector, there's no file to name, place, or protect.

log_directory: why write outside the data directory

The default is log, context sighup. A relative path is resolved relative to the data directory, not the directory from which the postmaster was started. Up through version 10 the default was pg_log; the pg_ prefix became reserved for what is actually part of the cluster.

The behavior on reload deserves attention. When the value is changed with SIGHUP, the collector forces a rotation, attempts a mkdir of the new directory with the same permissions as the data directory (0700, or 0750 if the cluster was created with group access), and ignores failure to create it. What it does not ignore is failure to open a file inside it. The author reports that pointing log_directory to /var/log/pgtest before the directory existed produced could not open log file ... No such file or directory, followed by disabling automatic rotation (use SIGHUP to re-enable). The collector then kept writing to the old file, with rotation off, until the next reload. The consequence is treacherous: a typo in this parameter doesn't lose the logs, it just silently stops rotating them, and current_logfiles still points to the old file, so nothing looks wrong.

The real question is: inside or outside the data directory? The author's answer is outside, for three reasons that have nothing to do with organization:

• pg_basebackup copies the directory. The base backup's exclusion list (pg_dynshmem, pg_notify, pg_replslot, pg_stat_tmp, pgsql_tmp, and the like) doesn't include log. The author's backup came with 4.4 MB of logs. A replica built from a base backup is born carrying the primary's logs, and every restore drags the logs from before the disaster along with it. pg_rewind uses the same filter.
• Disk. Logs in the data directory share a filesystem with the heap and, generally, with the WAL. A flood of logs fills up the data disk, and a full data disk means a stopped server. On its own filesystem, the same flood becomes merely a logging outage: the collector fails to write, drops messages, and the database keeps running. That's the failure you want.
• Permissions. The directory the collector creates is 0700, and nothing in log_file_mode changes that. If the on-call engineer, the log shipper, and the monitoring agent need to read the logs (and they all do), the directory has to be created manually, with the intended mode, which means outside the data directory.

One important operational detail: the collector's mkdir fails if the parent directory isn't writable by postgres, and /var/log isn't. So, create the directory before reloading, not after. On the side of server-side read functions, the location doesn't matter: pg_ls_logdir() lists whatever log_directory points to, pg_current_logfile() reports the absolute path, and pg_read_file() accepts absolute paths under log_directory even for roles without pg_read_server_files.

log_filename: the name is the retention policy

The default is postgresql-%Y-%m-%d_%H%M%S.log, context sighup. It's a strftime pattern, rendered by PostgreSQL's own strftime (only the standard escapes, no libc extensions) in the log_timezone timezone. For csvlog and jsonlog, the collector swaps a trailing .log for .csv or .json; if the name doesn't end in .log, it appends the suffix. So server_log.%H%M generates server_log.0244 and server_log.0244.json side by side.

The article's critical point is here: PostgreSQL never deletes a log file. The collector's only unlink calls are for its own metadata files. On every rotation, whether by age, by size, or by pg_rotate_logfile(), it computes a name from the pattern and opens it. If the name is new, there's one more file, forever. If the name already exists, the collector appends, unless log_truncate_on_rotation is on and the rotation is time-based, in which case it truncates. The author verified this: a constant postgresql.log survived two forced rotations with everything from before and after preserved.

The naming pattern, therefore, chooses between two regimes:

• A name that changes with every rotation (like the default): complete history and an unbounded directory. Something outside PostgreSQL needs to compress and delete.
• A cyclical name, like postgresql-%a.log with truncation on: a fixed set of seven files and a hard ceiling on history. Last Tuesday disappears when the current one starts.

There's no third option inside the server, and the second-level granularity of the default timestamp is a hint that the project expects the DBA to choose. The author's recommendation is the first regime: name by day (postgresql-%Y-%m-%d.log) and run a job that compresses yesterday's and deletes last month's. The caveat: if there's size-based rotation (log_rotation_size), a name that only changes daily makes the size-based rotation reopen the same file, so include %H%M in that scenario.

log_file_mode: the logs are a copy of the data without the access controls

The default is 0600, context sighup, and it's an octal number: write it with the leading zero. According to the author, 600 and 640 without the zero are decimal, above the 0777 ceiling, and get rejected on reload (which is the good outcome); a smaller decimal would be accepted and would mean something unintended. The mode is applied via umask at the instant the collector creates the file, so the change takes effect on the next rotation and doesn't alter existing files. The collector forces the owner's write bit back on regardless of the value: 0000 results in 0200, write-only. It's ignored on Windows and never touches the directory.

The security argument is the text's most forceful. 0600 means only postgres reads the log. 0640 is the other value worth using, for a group with the people and agents who need to read it, and, as already noted, it's useless inside a 0700 directory. Don't go beyond that. In the author's words, a world-readable log is "a copy of the database's most interesting data, without any of the database's access controls." The log contains every statement that errored out, everything log_statement captures, the identity of every connection, and, if someone was generous with log_min_duration_statement, the parameters those statements ran with.

The recommended configuration and what's left to the DBA

Putting the three together, the author proposes: an absolute log_directory on the logs filesystem, created by you, 0750, with the group belonging to whoever ships and reads the logs; log_filename = 'postgresql-%Y-%m-%d.log'; log_file_mode = 0640; and a cron job that does the deleting the server never will.

For the Brazilian DBA running production databases, the structural lesson is the usual one: performance and security come from correct design, not emergency fixes. None of these three parameters works miracles on its own, but getting any of them wrong produces silent failures, the worst kind of failure in production: rotation turned off with no alarm, logs leaking into replicas and backups, or sensitive data exposed by a badly written octal mode. The point the server makes explicit, and that the text reinforces, is that retention and deletion are an external responsibility. Whoever doesn't set up the purge job will, sooner or later, end up with a log directory with no ceiling.

Source 1: Planet PostgreSQL (https://postgr.es/p/9tq)

All Your GUCs in a Row: log_directory, log_filename, and log_file_mode, by The Build