Skip to content

Snowflake World Tour hits your city

See how leading teams deploy agents at scale. Find a stop near you. Register free.

Register now

A modern Postgres instance creates robust and comprehensive logs for nearly every facet of database and query behavior. While logs are the go-to place for finding and debugging critical errors, they are also a key tool in application performance monitoring and overall system health.

There are two parts to a good logging setup:

  1. Configuring what to log — tuning Postgres settings so you capture the right level of detail (slow queries, lock waits, DDL changes, temp file usage) without overwhelming your disk or your team.
  2. Hosting and searching your logs — getting logs into a searchable system (pgBadger, Datadog, Observe, Snowflake event table, etc.) so they're ready when you need them.

Both steps matter. The best logging configuration in the world doesn't help if you can't find the relevant entries during an incident. And the fanciest log dashboard is useless if you aren't capturing the data you need.

Initiating Logging for Postgres

Out of the box, Postgres sends logs to the terminal. To turn on sending things to log files, enable the logging collector.

ALTER SYSTEM SET logging_collector = 'on';

What File Format Do You Want for Logs?

The log message formatting is determined by the log_destination parameter, which can be set to one or more of: stderr, csvlog, jsonlog, and syslog. stderr is the default. Use commas to separate values when using more than one destination:

-- setting multiple log destinations
ALTER SYSTEM SET log_destination = 'stderr,jsonlog';

If logging_collector = 'on', then stderr, csvlog, and jsonlog logging will go to files in the directory specified by log_directory.

Many hosted and fully managed systems have logs available in different formats for use by different tools. syslog is what you'll use on servers for log shipping to an external logging host.

What Level of Logging Do You Want?

All log messages generated by the server will have one of these severity levels:

SeverityDescription
PANICSevere errors — system must shut down and recover
FATALErrors that cause the session to terminate
ERRORErrors that abort the current operation but not the session
WARNINGPotential problems — deprecated features, possible data issues
NOTICESignificant messages — non-critical issues
INFOLow informational messages — autovacuum, config reloads
DEBUG1-5Basic debug to most verbose

The log_min_messages setting determines which messages are actually logged. All messages with the configured severity level or above will be sent. The default is warning and that's a generally good setting.

ALTER SYSTEM SET log_min_messages = 'warning';

Logging SQL Statements

SQL queries can be selected for logging based on the log_statement parameter:

ValueWhat It Logs
noneNo SQL statements (errors still appear via log_min_messages)
ddlData definition changes only — table, column, and index changes
modAll DDL plus inserts, updates, and deletes
allEvery SQL statement and query (generally not recommended for production)

DDL is a good choice for production:

ALTER SYSTEM SET log_statement = 'ddl';

Statements with syntax errors or that fail during parsing or planning are not covered by log_statement. These are covered by log_min_error_statement:

ALTER SYSTEM SET log_min_error_statement = 'ERROR';

SQL errors will look like this:

2025-05-09 14:02:37 UTC [28561] ERROR:  operator does not exist: integer == integer at character 33
2025-05-09 14:02:37 UTC [28561] HINT:  Perhaps you meant to use the standard operator "=".
2025-05-09 14:02:37 UTC [28561] STATEMENT:  SELECT * FROM users WHERE id == 42;

Logging Prepared Statements and Sensitive Data

The log_parameter_max_length and log_parameter_max_length_on_error parameters limit the length of bind parameter values logged with query and error log messages. This applies to both explicit prepared statements (PREPARE / EXECUTE) and the unnamed prepared statements used by application database drivers via the extended query protocol.

Set them to 0 to fully disable bind parameter logging:

ALTER SYSTEM SET log_parameter_max_length = 0;
ALTER SYSTEM SET log_parameter_max_length_on_error = 0;

These can also be set per-session, per-transaction, per-user, or per-database:

-- set for a session
SET SESSION log_parameter_max_length = 0;

-- set for a transaction
BEGIN;
SET LOCAL log_parameter_max_length = 0;
COMMIT;

-- set for all queries run by a specific user
ALTER ROLE app_user SET log_parameter_max_length = 0;

-- set for all traffic on a specific database
ALTER DATABASE sensitive_db SET log_parameter_max_length = 0;

Formatting Log Entries

Out of the box, Postgres log entries look like this:

2025-05-19 13:49:04.908 EDT [3108283] ERROR: column "asdfklasdf" does not exist at character 8

The timestamp and process ID come from the default log_line_prefix:

-- default
log_line_prefix = '%m [%p] '

-- recommended: adds user and database context
ALTER SYSTEM SET log_line_prefix = '%m [%p]%q %u@%d ';

Keep the process ID (%p) — it's essential when troubleshooting a specific process. %u adds the user and %d adds the database, which is helpful when using multiple databases in a single instance.

See the log_line_prefix documentation for the full list of % escape sequences.

The log_error_verbosity setting controls how verbose log messages are:

SettingBehavior
terseShortened errors with SQL state error code
defaultError and hint messages
verboseAdditional context like source file and function names (not recommended for production)

Log File Naming, Location, and Rotation

File Names and Location

The log_filename setting specifies log filenames using strftime escape patterns:

-- default (includes seconds, usually unnecessary)
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

-- simpler daily format
ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d';

Find your current log location:

SHOW data_directory;
SHOW log_directory;
SHOW log_filename;
SELECT pg_current_logfile();

Rotating Logs

Without rotation, logs will fill up your disk.

-- rotate logs every day
ALTER SYSTEM SET log_rotation_age = '1d';

-- rotate if file exceeds 10MB before the day is up
ALTER SYSTEM SET log_rotation_size = '10MB';

-- truncate reused log filenames instead of appending
ALTER SYSTEM SET log_truncate_on_rotation = 'on';

If your log_filename format does not result in automatic name re-use (e.g., postgresql-%Y-%m-%d.log), use an external tool like Linux's logrotate to remove old logs after archiving.

Audit Logging with PGAudit

Beyond server and query logs, you can audit user behavior with the PGAudit extension. It records who performed an action, when it happened, and the exact changes made.

-- add to preloaded libraries (requires restart)
-- shared_preload_libraries = 'pgaudit'

-- create the extension
CREATE EXTENSION pgaudit;

-- enable audit logging
ALTER SYSTEM SET pgaudit.log = 'ddl';

Possible values for pgaudit.log: read, write, role, ddl, misc.

You can set audit logging per role:

ALTER ROLE audited_user SET pgaudit.log = 'read, write, ddl';

Audit log output:

2025-05-09 12:34:56.789 UTC [12345] myuser@mydb LOG:  AUDIT: SESSION,1,SELECT,pg_catalog.pg_stat_activity,SELECT * FROM pg_stat_activity;

aside positive If you only need DDL logging without the additional auditing features of PGAudit (session info, role tracking), then log_statement = 'ddl' may be sufficient on its own.

Logging for Performance Monitoring

Logging Long Running Queries

The log_min_duration_statement parameter is PostgreSQL's slow query logging threshold:

ALTER SYSTEM SET log_min_duration_statement = '1000';

LOG:  duration: 2001.342 ms  statement: SELECT count(*) from orders;

Logging Locks and Lock Waits

Enable log_lock_waits to log when queries wait on locks. There is virtually no overhead and it's safe for production:

ALTER SYSTEM SET log_lock_waits = 'on';

With this enabled, the deadlock_timeout setting is used as the threshold. Lock wait entries show:

2024-05-16 14:45:12.345 UTC [45678] user@database LOG:  process 45678 still waiting for ShareLock on transaction 123456 after 1000.001 ms
2024-05-16 14:45:12.345 UTC [45678] user@database DETAIL:  Process holding the lock: 12345. Wait queue: 45678, 45670.
2024-05-16 14:45:12.345 UTC [45678] user@database STATEMENT:  UPDATE orders SET status = 'shipped' WHERE id = 42;

Logging Temp Files

If queries perform reads or sorts on disk instead of in memory, it may mean you need to increase work_mem. Set log_temp_files to the same size as your work_mem:

-- log temp files > 4MB (set to current work_mem in kB)
ALTER SYSTEM SET log_temp_files = '4096';

2024-05-16 14:23:05.123 UTC [12345] user@database LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 245760

Query Plans with auto_explain

auto_explain automatically logs EXPLAIN plans for slow queries:

-- create the extension
CREATE EXTENSION IF NOT EXISTS auto_explain;

-- log plans for queries that run longer than 1000ms
ALTER SYSTEM SET auto_explain.log_min_duration = '1000';

aside negative auto_explain generates a lot of log output. For really big queries or partitioned tables, plans can be very long. Consider setting it for a single session instead of globally when doing targeted performance work.

Autovacuum Logging

The log_autovacuum_min_duration parameter controls whether autovacuum jobs are logged. It defaults to 10 minutes since PG15.

-- log all autovacuum operations that take longer than 1 second
ALTER SYSTEM SET log_autovacuum_min_duration = '1000';

Autovacuum log entries contain all the same information as VACUUM VERBOSE — pages removed, tuples removed, index scan details, I/O timings, buffer usage, WAL usage, and system resource usage.

Extracting and Parsing Logs

Having your logs accessible and searchable is critical for debugging and helpful for performance. Logs are like insurance — you may not need them every day, but when you have a problem, you're glad they're there.

pgBadger

pgBadger is an open source project that turns Postgres log output into an HTML page with zoomable charts. Run it periodically or on demand:

# send logs to a local text file
cb logs <cluster-id> > pglogs.txt

# pgBadger reads the text file and provides HTML output
pgbadger -f syslog pglogs.txt -o out.html

Third-Party Log Drain Tools

Tools like pgAnalyze, Datadog, and Observe can host, parse, and let you search your logs. These run as an agent, container, or service to export logs.

-- set up syslog drain
ALTER SYSTEM SET log_destination = 'syslog';

Data Warehousing for Logs

For large-scale applications, teams are opting to store and query logs in systems like Snowflake. When stored as flat files in object storage, these systems can be very cost effective for high-throughput log analysis.

Logging in Snowflake Postgres

Snowflake Postgres automatically collects Postgres logs and metrics into the Snowflake Event Table at SNOWFLAKE.TELEMETRY.EVENTS. This gives you the full power of Snowflake SQL to query, aggregate, and alert on your Postgres telemetry data with no additional configuration.

-- Query the last 10 minutes of Postgres logs from the event table
SELECT TIMESTAMP, VALUE:MESSAGE as log_line
FROM SNOWFLAKE.TELEMETRY.EVENTS
WHERE resource_attributes['snowflake.o11y.logtype'] = 'postgres-otelcol-vm-agent'
  AND resource_attributes['instance.id'] = '<your-instance-id>'
  AND record_type = 'LOG'
  AND TIMESTAMP > CURRENT_TIMESTAMP() - INTERVAL '10 MINUTES'
LIMIT 100;

You can build custom alerts by querying this table on a schedule with Snowflake Tasks, or send logs to third-party monitoring tools like Grafana, Datadog, or Observe.

Here's a summary of recommended settings for a production Postgres instance. Review the docs and your individual application needs — don't just copy paste this in.

-- Set up logging collector
ALTER SYSTEM SET logging_collector = 'on';

-- Log system error messages
ALTER SYSTEM SET log_min_messages = 'error';

-- Log all data definition changes
ALTER SYSTEM SET log_statement = 'ddl';

-- Log the full statement for SQL errors
ALTER SYSTEM SET log_min_error_statement = 'ERROR';

-- Set log file name
ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d';

-- Add database name and process id to log prefix
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';

-- Rotate logs every day
ALTER SYSTEM SET log_rotation_age = '1d';

-- Enable pgaudit for DDL
ALTER SYSTEM SET pgaudit.log = 'ddl';

-- Log queries longer than 1000ms
ALTER SYSTEM SET log_min_duration_statement = '1000';

-- Log lock waits
ALTER SYSTEM SET log_lock_waits = 'on';

-- Log temp files when Postgres needs disk instead of cache
ALTER SYSTEM SET log_temp_files = '4096';

-- Log plans for queries that run longer than 1000ms
ALTER SYSTEM SET auto_explain.log_min_duration = '1000';

-- Set up a log destination for searchability
ALTER SYSTEM SET log_destination = 'syslog';

aside positive Moderation is key with Postgres logging. Keep logs that are helpful, set up rotation so they're discarded after a few days or when they hit a certain size, and don't keep logs you aren't actively using.

Conclusion

Related Resources

🐘 Postgres Logging for Performance Optimization — Crunchy Data Blog

The full guide on Postgres logging covering everything from basic setup to performance monitoring.

❄️ Getting Started with Snowflake Postgres

Create a Snowflake Postgres instance, connect to it, and run your first queries.

❄️ Getting Started with Snowflake Postgres Tables

Create databases, roles, schemas, tables, and learn about transactions and data masking.

❄️ Monitor Snowflake Postgres with Grafana

Connect Grafana to the Snowflake event table for real-time Postgres dashboards and alerting.

❄️ Monitor Snowflake Postgres Logs with Datadog

Send Postgres logs from the Snowflake event table to Datadog for monitoring and analysis.

❄️ Monitor Snowflake Postgres with Observe

Set up Postgres monitoring with Observe using the Observe for Snowflake native application.

🐘 PostgreSQL Documentation — Server Log

Official PostgreSQL documentation on all logging configuration parameters.

Updated 2026-07-08

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances