PostgreSQL log_line_prefix Builder

log_line_prefix decides what PostgreSQL writes at the start of every log line: timestamp, process ID, user, database, application, client address, SQLSTATE and more. This tool explains each % escape in a prefix, flags escapes your version ignores, previews a line from a client session and from a background process, writes the postgresql.conf and ALTER SYSTEM lines, and builds a regex with named groups. Paste real log lines to check that the regex splits them correctly. It follows the PostgreSQL 14 to 18 source and runs in your browser.

log_line_prefixPostgreSQL version

Presets

Breakdown, settings and regex

Parts of the prefix:
  %m  Timestamp with milliseconds
  " ["  literal text
  %p  Process ID
  "] ["  literal text
  %l  Log line number within the session or process
  "] "  literal text
  %q  stop here for background processes (checkpointer, autovacuum...); the rest is printed only for client sessions
  "[user="  literal text
  %u  User name [empty outside client sessions]
  " db="  literal text
  %d  Database name [empty outside client sessions]
  " app="  literal text
  %a  Application name ([unknown] if not set) [empty outside client sessions]
  " client="  literal text
  %r  Remote host and port, e.g. 10.0.0.7(52814), or [local] [empty outside client sessions]
  "] ["  literal text
  %e  SQLSTATE error code (00000 for normal messages)
  "] "  literal text

Example, client session:
  2026-11-09 16:40:01.237 UTC [48213] [3] [user=app_user db=shop app=psql client=10.0.0.7(52814)] [00000] LOG:  statement: SELECT 1
Example, background process (checkpointer):
  2026-11-09 16:40:01.237 UTC [48213] [3] LOG:  checkpoint starting: time

postgresql.conf:
  log_line_prefix = '%m [%p] [%l] %q[user=%u db=%d app=%a client=%r] [%e] '
Or from SQL (no restart needed):
  ALTER SYSTEM SET log_line_prefix = '%m [%p] [%l] %q[user=%u db=%d app=%a client=%r] [%e] ';
  SELECT pg_reload_conf();
pgBadger:
  pgbadger --prefix '%m [%p] [%l] %q[user=%u db=%d app=%a client=%r] [%e] ' postgresql.log

Regex for one log line (JavaScript, PCRE, Go, .NET):
  ^(?<time_ms>\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{3} \S+) \[(?<pid>\d+)\] \[(?<line>\d+)\] (?:\[user=(?<user>.*?) db=(?<db>.*?) app=(?<app>.*?) client=(?<client>.*?)\] \[(?<sqlstate>[0-9A-Z]{5})\] )?(?<level>DEBUG[1-5]|INFO|NOTICE|WARNING|ERROR|LOG|FATAL|PANIC|DETAIL|HINT|QUERY|CONTEXT|LOCATION|STATEMENT):  (?<message>.*)$
Python (re module):
  ^(?P<time_ms>\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{3} \S+) \[(?P<pid>\d+)\] \[(?P<line>\d+)\] (?:\[user=(?P<user>.*?) db=(?P<db>.*?) app=(?P<app>.*?) client=(?P<client>.*?)\] \[(?P<sqlstate>[0-9A-Z]{5})\] )?(?P<level>DEBUG[1-5]|INFO|NOTICE|WARNING|ERROR|LOG|FATAL|PANIC|DETAIL|HINT|QUERY|CONTEXT|LOCATION|STATEMENT):  (?P<message>.*)$

Test against log lines

Parsed fields

4 log entries parsed.

Line 1:
  time_ms        2026-11-09 16:40:01.237 UTC
  pid            48213
  line           1
  user           app_user
  db             shop
  app            billing api
  client         10.0.0.7(52814)
  sqlstate       00000
  level          LOG
  message        connection authorized: user=app_user database=shop

Line 2:
  time_ms        2026-11-09 16:40:02.511 UTC
  pid            48213
  line           2
  user           app_user
  db             shop
  app            billing api
  client         10.0.0.7(52814)
  sqlstate       42P01
  level          ERROR
  message        relation "ordes" does not exist at character 15

Line 3:
  time_ms        2026-11-09 16:40:02.511 UTC
  pid            48213
  line           3
  user           app_user
  db             shop
  app            billing api
  client         10.0.0.7(52814)
  sqlstate       42P01
  level          STATEMENT
  message        SELECT * FROM ordes
  (continued)    WHERE id = 1;

Line 5:
  time_ms        2026-11-09 16:45:00.004 UTC
  pid            48001
  line           7
  level          LOG
  message        checkpoint starting: time

Do more than postgresql log_line_prefix builder — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Pick a preset or type your own log_line_prefix, and choose the PostgreSQL version. Run SHOW log_line_prefix; to see the current value.
Read the breakdown and checks: what each escape prints, which parts are empty for background processes, and whether pgBadger can parse it.
Copy the postgresql.conf or ALTER SYSTEM line, then paste a few real log lines to test the generated regex against them.

Frequently asked questions

What is the default log_line_prefix in PostgreSQL?

Since PostgreSQL 10 the default is '%m [%p] ': a timestamp with milliseconds and time zone, then the process ID in brackets. It does not show the user, database or client, so most teams add %u, %d, %a and %h or %r. Change it in postgresql.conf or with ALTER SYSTEM SET log_line_prefix and SELECT pg_reload_conf(); no restart is needed.

What does %q do in log_line_prefix?

%q prints nothing. It tells background processes such as the checkpointer, autovacuum and WAL writer to stop at that point, because they have no user, database or client. Put session fields (%u, %d, %a, %r, %h, %i) after %q, and keep the timestamp and PID before it, so background lines do not end with empty user= and db= fields.

What log_line_prefix does pgBadger need?

pgBadger needs a time escape (%t, %m or %n) and a process escape (%p or %c). For stderr logs its documentation suggests '%t [%p]: user=%u,db=%d,app=%a,client=%h '. If you use a different prefix, pass the exact same string to pgbadger --prefix so it can parse the lines.