A log line has two parts: the message, which PostgreSQL decides, and everything in front of it, which you do. log_line_prefix is the join key. Every fact that lets you connect a line to a session, a transaction, a client, or a moment in another system’s logs has to be in the prefix, because the message itself doesn’t carry it. The default has been '%m [%p] ' since 10; for the twelve years before that it was the empty string, and a PostgreSQL log line, out of the box, had no timestamp. log_timezone decides what clock %m reads.
log_line_prefix
Context sighup. It’s a printf-style string in which %-escapes are replaced per line and everything else is copied through, with optional padding (%-10u left-justifies the user name in ten columns). The text format applies it to every line it emits, not every message: the LOG: line, and then each DETAIL:, HINT:, CONTEXT:, and STATEMENT: line that belongs to the same message, each gets its own copy of the prefix. When a single field is itself multi-line (an EXPLAIN plan from auto_explain, a RAISE with a newline in it), the first line gets the prefix and the rest get a tab. That tab is how a parser tells “continuation of the previous field” from “new line.” The prefix also goes to syslog, which is why the documentation suggests leaving %t and %p out there since syslog adds its own. csvlog and jsonlog ignore it entirely; they carry every field regardless.
The escapes, in the order I’d argue for them.
%m is the timestamp with milliseconds and the zone abbreviation; %t drops the milliseconds and there is no reason to want that. %n is the same instant as a Unix epoch with milliseconds, and if a machine is the primary reader, use it and skip the parsing. %p is the PID, the one field that joins to pg_stat_activity, to ps, to pg_terminate_backend(), and to the other process named in a deadlock report.
%q is the pivot. Escapes marked “session only” in the docs produce nothing for the checkpointer, autovacuum, the postmaster, and so on; %q tells those processes to stop reading the string, so a checkpoint line doesn’t end with @/. Put it right after the fields everything has and before the fields only sessions have: the documentation’s example '%m [%p] %q%u@%d/%a ' is correct and I’d extend it rather than replace it. %u@%d goes in on any server with more than one database or role, which is all of them. %a is application_name, which your applications set, or will after the first outage where the log couldn’t say which one it was.
%h is the client address (or the hostname, if you’ve turned on log_hostname, which you haven’t), and %r adds the port, which is what distinguishes forty connections from the same pooler. %L, new in 18, is the address on the server the client connected to, for hosts listening on several; the 18 release notes describe it as the client IP address, which it isn’t.
%v/%x are the virtual and real transaction IDs. %x is zero until the transaction writes something (it was 2/3/0 for a read-only block in my sandbox and 2/4/776 after one INSERT), so it can’t be used to group a read-only transaction’s lines; %v can. Both earn their place the first time you read a log_lock_waits message and need to find the other side.
%c and %l are for log analyzers. %c is a session identifier built from the hex of the process start time and the PID, which means it’s to_hex(trunc(extract(epoch from backend_start))::integer) || '.' || to_hex(pid) from pg_stat_activity, and I checked that they match. It exists because PIDs recycle; across a week of logs, %p alone does not identify a session. %l is a per-session counter of lines, not messages: a RAISE from PL/pgSQL produces LOG, CONTEXT, and STATEMENT lines and consumes three numbers. It’s there so a parser can detect a gap.
Then the ones to think twice about. %i, the command tag, doesn’t come from the executor; it’s read from the process title, so it reports whatever update_process_title last wrote. Turn that off (it’s the default on Windows, for good reason) and %i freezes at the last value it saw, which in my sandbox was SET, from the SET that turned it off. %e is the SQLSTATE and is 00000 on every line that isn’t an error; log_error_verbosity = verbose puts the same code after the severity. %b, the backend type, is what lets a background line say checkpointer instead of just a PID; it’s cheap, and the cost is the string client backend on every session line. %Q (14) is the query ID and needs compute_query_id; it’s always zero on log_statement lines, which are written before the ID exists. %P (14) is the parallel leader’s PID, which matters if you read parallel-worker output and not otherwise.
So: '%m [%p] %q%u@%d/%a %h %v/%x '. Add %r in place of %h behind a pooler, %c:%l if a tool consumes the log, %b if you want background processes named. Resist %i. The result is about seventy characters of prefix per line, and every one of them is a question you’d otherwise be asking pg_stat_activity about a session that has already gone.
log_timezone
Context sighup, cluster-wide, built-in default GMT, and initdb overwrites that in postgresql.conf with whatever the system’s zone was at the moment initdb ran. It has nothing to do with timezone, which is per-session and decides how timestamptz values are displayed to clients; in the sandbox, setting this to America/Los_Angeles made the log say 21:05:05.402 PDT while now() in the same session still said 04:05:05+00. What it does govern: %t, %m, and %s in the prefix, the timestamps in CSV and JSON logs, the rendering of log_filename, and the clock the rotation boundaries are aligned to. You can’t set it to an abbreviation (PDT is rejected; America/Los_Angeles is what you mean), but the abbreviation is what appears in the log, which is the one thing that makes a local-time log survive the repeated hour in autumn.
Set it to UTC. Every other system whose logs you’ll lay next to this one is already there; a fleet across regions has one clock instead of four; the repeated and missing hours stop existing, along with the twice-a-year rotation wobble from the last post; and the file is no longer carrying the accident of which zone the machine that ran initdb happened to be in. The objection is that the on-call engineer thinks in local time. The on-call engineer reads the log through a tool that converts, or with a terminal that can do arithmetic, and either is better than a log whose 01:30 means two different things on one night a year.