Logging Collector, Structured Logs, Startup Diagnostics, and a Baseline Production Profile

Learning outcomes

A database that cannot explain its own failures is expensive to operate. PostgreSQL logs can answer connection, startup, checkpoint, autovacuum, query, replication, and background-worker questions—but only if destinations, formats, verbosity, and correlation fields are configured intentionally. Logging too little hides incidents; logging everything can expose sensitive data, consume disk, and overwhelm analysis.

1. Where PostgreSQL can send logs

log_destination accepts one or more supported destinations. Core PostgreSQL supports stderr, csvlog, jsonlog, and syslog; Windows also supports eventlog. The default is commonly stderr. Whether stderr ultimately appears in a file, system journal, container log, service manager, or terminal depends on how PostgreSQL was started.

SELECT name, setting, context, source, pending_restartFROM pg_catalog.pg_settingsWHERE name IN ('log_destination','logging_collector','log_directory','log_filename')ORDER BY name;

2. The logging collector changes who owns stderr files

When logging_collector is enabled, PostgreSQL starts a logger process that captures stderr and redirects messages into files according to logging configuration. Because logging_collector is a server-start parameter, enabling/disabling it requires restart. CSV and JSON file destinations depend on the collector.

When the collector is managing stderr/csvlog/jsonlog files, PostgreSQL maintains a current_logfiles file that records the active log-file paths. That is safer than guessing a timestamped filename.

SHOW data_directory;SHOW logging_collector;SHOW log_directory;SHOW log_filename;SHOW log_destination;

The SQL settings tell you configuration, not the contents of current_logfiles. Reading the file requires server filesystem access under appropriate OS permissions. In containerized labs, prefer docker logs when stderr is owned by the runtime unless you deliberately enable file collection.

3. Correlation: a log line must identify the session behind it

Plain stderr text can be made much more useful with log_line_prefix. Useful escapes can include timestamp, PID, session identifier, user, database, remote host, application name, and more. Structured jsonlog already emits named fields such as timestamp, user, dbname, pid, remote_host, session_id, and session_start.

For a human-readable stderr baseline, a prefix conceptually like the following is useful:

# Example only; verify escapes in the PostgreSQL 18 logging docs.log_line_prefix = '%m [%p] %c %u@%d app=%a client=%r '

Do not copy prefixes blindly into production. Every extra field increases log volume, and some identifiers may be sensitive. The goal is enough correlation to join a log event back to pg_stat_activity, application telemetry, or an incident timeline.

4. Choose what to log: diagnostic value versus cost and exposure

PostgreSQL offers many event/threshold settings: connection/disconnection logging, statement duration thresholds, lock waits, checkpoints, temporary files, autovacuum details, and more. Logging all SQL statements can expose credentials embedded incorrectly in SQL, personal data, or large payloads. It also produces significant I/O.

SELECT name, setting, unit, context, sourceFROM pg_catalog.pg_settingsWHERE name IN (  'log_connections',  'log_disconnections',  'log_min_duration_statement',  'log_lock_waits',  'deadlock_timeout',  'log_checkpoints',  'log_temp_files')ORDER BY name;

A baseline should answer specific operational questions. For example: can I correlate a failed login to client/application identity? Can I see a statement that exceeds the SLO threshold? Can I detect lock waits or temp-file spills? Can I explain checkpoint-related write activity? The correct thresholds vary by system.

5. Structured logging with jsonlog

PostgreSQL’s JSON log format is useful when logs are ingested into structured search/analytics systems. Each line is a JSON object with named keys. Consumers should ignore unknown fields so new PostgreSQL versions can add fields without breaking ingestion.

A disposable file-collector setup might use:

logging_collector = onlog_destination = 'stderr,jsonlog'log_directory = 'log'log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

logging_collector requires restart. log_destination is a configuration setting whose context should be verified on the target server. Before applying any change, query pg_settings.context and validate file syntax with pg_file_settings.

6. Startup diagnosis: use evidence before editing randomly

A startup failure has a different diagnostic path from a reload problem. If PostgreSQL is not running, you cannot query pg_settings from that instance. You need service-manager/container stderr, PostgreSQL log output, or direct pg_ctl/postgres startup diagnostics for the disposable cluster.

Before restart, however, a running instance can often detect configuration-file problems:

SELECT sourcefile, sourceline, name, setting, applied, errorFROM pg_catalog.pg_file_settingsWHERE error IS NOT NULL   OR name IN ('logging_collector','log_destination','log_line_prefix')ORDER BY sourcefile, sourceline;SELECT pg_reload_conf();

If a reload encounters an invalid value, PostgreSQL logs the problem and keeps the previous effective setting where appropriate. A full startup can fail when critical configuration is invalid. That is why preflight and rollback copies matter.

7. Harmless failure injection: invalid port in an isolated restart rehearsal

For a restart-oriented exercise, use a separate disposable cluster or container, not the only Chapter 01 lab you care about. A simple failure is to set an invalid value for a startup parameter such as port or add an unrecognized parameter name, then observe startup failure and restore the file.

# Disposable cluster only. This is intentionally invalid.port = 'not-a-number'

Expected evidence is a parse/value error naming the parameter/file/line. The repair procedure is: preserve the error message, restore the known-good configuration, validate again, start/reload as appropriate, and verify from SQL. Do not keep trying random edits until it “starts somehow.”

8. A baseline production profile is a record, not a tuning prescription

At the end of Chapter 02, capture the configuration and identity facts future lessons depend on. Store the output with the lab notes, excluding secrets.

SELECT version() AS server_version;SELECT current_database(), current_user, pg_backend_pid();SHOW data_directory;SHOW config_file;SHOW hba_file;SHOW server_encoding;SHOW TimeZone;SELECT name, setting, unit, context, source, pending_restartFROM pg_catalog.pg_settingsWHERE name IN (  'port','listen_addresses','max_connections',  'shared_buffers','wal_buffers','work_mem','maintenance_work_mem',  'logging_collector','log_destination','log_line_prefix',  'log_min_duration_statement','log_lock_waits','log_temp_files')ORDER BY name;

Add host/container memory limit, PostgreSQL package/image identity, psql version, storage location, and whether the instance is disposable. Do not store passwords or secret-bearing connection strings in the baseline file.

9. Deliberately wrong approach: enable log_statement = 'all' everywhere

When debugging one incident, an operator may enable full statement logging globally and forget it. This can multiply log volume, expose sensitive literal values, increase I/O, and make important events harder to find. The repair is to define the question first, use the narrowest logging mechanism and duration that answers it, protect log access, and revert temporary diagnostics.

For slow-query diagnosis, a duration threshold or targeted extension/observability strategy is often more appropriate than permanent full statement logging. Later observability chapters add pg_stat_statements and workload-level evidence.

10. Hands-on lab: produce and correlate one diagnostic event

1. Set a distinct application_name=bda_ch02_l5 on an administrator lab session.
2. Record pg_backend_pid(), role, database, and current logging settings.
3. Choose a safe session-visible event: for example trigger statement_timeout on pg_sleep or deliberately reference a nonexistent table.
4. Locate the corresponding server log event using the platform’s actual logging path: collector file, Docker logs, system journal, or Windows Event Viewer as applicable.
5. Correlate the event to PID/application/session fields available in your format.
6. Record what the log proves and what it does not prove.

SET application_name = 'bda_ch02_l5';SELECT pg_backend_pid(), current_user, current_database();SET statement_timeout = '1s';SELECT pg_sleep(3);RESET statement_timeout;

11. Chapter 02 summary and bridge to Chapter 03

You can now observe a PostgreSQL instance as a living system. Connections become dedicated backend processes; auxiliary processes carry instance-wide responsibilities; PGDATA and configuration files have defined ownership and precedence; memory is a concurrency budget rather than a percentage ritual; GUC values have source, scope, context, and lifetime; and logs can correlate failure evidence back to sessions.

Chapter 03 moves from instance-wide architecture to PostgreSQL’s namespace and ownership model: cluster versus database versus schema boundaries, search_path, roles, catalogs, dependencies, and multi-team privilege design.

Authoritative references

• PostgreSQL 18 — Error Reporting and Logging
• PostgreSQL 18 — pg_file_settings
• PostgreSQL 18 — pg_settings
• PostgreSQL 18 — System Administration Functions
• PostgreSQL 18 — Setting Parameters