Postgres log configurations

Available log settings:#

The table lists configurable log settings. See each setting's section for details.

To view log settings for your project, you can run:

1
select
2
  name,
3
  setting,
4
  unit,
5
  short_desc,
6
  extra_desc,
7
  context,
8
  enumvals,
9
  reset_val,
10
  case
11
    when sourcefile = '/etc/postgresql-custom/custom-overrides.conf' then 'set by CLI/API'
12
    else 'platform default'
13
  end as configuration_source
14
from "pg_settings"
15
where
16
  category in ('Reporting and Logging / When to Log', 'Reporting and Logging / What to Log')
17
  or (name like 'auto_explain.%' or name like 'pgaudit.%' or name = 'cron.log_statement');

To view settings targeting specific database roles, you can run:

1
select
2
  rolname,
3
  rolconfig
4
from pg_roles
5
where
6
  rolname in (
7
    'anon',
8
    'authenticated',
9
    'postgres',
10
    'service_role'
11
    -- ,<ANY CUSTOM ROLES>
12
  );

Configuring log settings#

There are three potential ways to change log settings:

• Supabase CLI
• Supabase Management API
• SQL commands

Background activity#

Logs the activity of Postgres background processes and utilities. Helps diagnose and detect performance and operational issues.

log_autovacuum_min_duration#

update and delete commands leave behind obsolete row versions to support rollbacks and concurrent queries. A background process called the Autovacuum permanently removes the rows in batch jobs at a later point. The setting logs Autovacuum when they run longer than the limit.

Useful for:

• Monitoring vacuum activity
• Identifying resource strain, such as IO usage, caused by vacuums

Example logs:

1
# records tables vacuumed
2
automatic vacuum of table "postgres.public.vac_test": index scans: 0
3
automatic analyze of table "postgres.public.vac_test"

log_checkpoints#

Checkpoints are background operations that write modified data from memory to disk. It enables Postgres to discard WAL files that otherwise must be retained for data recovery and replication.

The setting records automatic checkpoint events.

Useful for:

• Measuring checkpoint write volume
• Identifying excessive checkpoint-related disk activity
• Detecting read-replica issues
• Deciding whether checkpoint settings should be adjusted

Example logs:

1
# Monitoring checkpointer activity
2
checkpoint starting: time
3
checkpoint complete: wrote 405563 buffers (6.4%); 0 WAL file(s) added, 0 removed, 465 recycled; write=269.656 s, sync=3.570 s, total=274.133 s; sync files=2393, longest=0.375 s, average=0.002 s; distance=6635965 kB, estimate=7953512 kB

1
# Monitoring replay activity from read replicas
2
restartpoint starting: time
3
recovery restart point at 0/B837D6F0
4
restartpoint complete: wrote 266 buffers (0.4%); 0 WAL file(s) added, 1 removed, 0 recycled; write=25.760 s, sync=0.004 s, total=25.773 s; sync files=20, longest=0.003 s, average=0.001 s; distance=19779 kB, estimate=565114 kB; lsn=0/B837D748, redo lsn=0/B837D6F0

log_lock_waits#

Logs when operations are blocked by database locks for more than 1s. For more information on lock management, reference postgreslocksexplained.com.

Useful for:

• Identifying queries that are blocked by locks
• Identifying which queries are blocking
• Measuring how long queries remain blocked

Example logs:

1
# records when a process is waiting on a lock for 1+s
2
process 1017208 still waiting for "lock_type" on relation 75874 of database 5 after 1001.872 ms

1
# records when a process is finally able to claim its lock
2
process 1007982 acquired "lock_type" on transaction 445264 after 2000.880 ms

log_recovery_conflict_waits#

If a read-replica is acting on data that is being modified/discarded by the primary, then it may wait to determine if the data should be available or not before responding. The setting log_recovery_conflict_waits determines if the replica should report waits that last more than 1s.

Useful for:

• Detecting operations that interfere with replica queries
• Detecting operations that may cause replication lag

Example log:

1
recovery still waiting after 1000.156 ms: recovery conflict on lock

log_startup_progress_interval#

When a server is recovering from a crash, it has to go through several checks before it becomes operational again. To provide more clarity about a recovery's progress, the setting causes Postgres to log its current startup task if it takes longer than the interval.

Useful for:

• Determining if a server is responsive during startup/recovery

Example log:

1
syncing data directory (pre-fsync), elapsed time: 0.00 s, current path: ./base/4/13456

log_temp_files#

Some queries require sorting, hashing, or other memory-intensive operations. When these operations exceed the memory limits primarily managed by the work_mem and hash_mem_multiplier settings, Postgres uses temporary files on disk to complete them.

When temp files larger than the log_temp_files limit are created, Postgres logs the event, helping identify queries that can benefit from memory tuning.

Useful for:

• Determining when the memory constraint settings should be adjusted
• Identifying disk strain caused by temp files

Example log:

1
# records the creation of a temp file that is 8.33MB in size
2
temporary file: path "base/pgsql_tmp/pgsql_tmp306918.0", size 8331264

Network monitoring#

Logs information about clients connecting/disconnecting from the database. Some insightful values that can be captured include:

• When a client first authenticated
• How long they were connected for
• Their IP address

log_connections#

Logs when a client establishes a new database connection, including connection receipt, authentication, and authorization.

Useful for:

• Monitoring successful authentication attempts
• Auditing database access

Example logs:

1
connection received: host=127.0.0.1
2
connection authorized: user=postgres database=postgres application_name=Supavisor auth_query
3
connection authenticated: identity="pgbouncer" method=scram-sha-256

log_disconnections#

Logs when a database connection gracefully closes.

Useful for:

• Monitoring how long connections persist

Example log:

1
disconnection: session time: 0:00:01.492 user=postgres database=postgres host=127.0.0.1

Query activity#

Records queries or metadata about queries.

cron.log_statement#

Logs when the pg_cron extension starts a cron job.

Useful for:

• Monitoring successful cron job executions

Example log:

1
cron job 1 starting: select 1

auto_explain.*#

auto_explain is a Postgres module that is installed on all Supabase projects. It logs the statements and explain plans of queries that took more than the setting's limit.

Useful for:

• Monitoring and optimizing slow queries

Example log:

1
duration: 1661.934 ms plan: Query Text: SELECT * FROM example;
2
Seq Scan on example (cost=0.00..1443.00 rows=100000 width=36)

log_duration#

It logs the duration of all queries, but not the queries themselves.

Useful for:

• Monitoring query duration

Example log:

1
duration: 0.599 ms

Note, even though the query will not be logged, the primary command associated with the query will be recorded in the command subfield:

1
...other subfields
2
command_tag: "SELECT"

log_min_duration_statement#

It is similar to auto_explain.log_min_duration, but it lighter weight. It only logs query statements that run longer than the setting's limit.

Useful for:

• Monitoring and optimizing slow queries

Example log:

1
duration: 1.097 ms statement: select * from example_table limit 100;

log_min_error_statement#

When an event is logged, beyond the primary message, multiple subfields are also captured, such as the status code. The log_min_error_statement field determines if the query responsible for the log should be recorded, too, under the query subfield.

If the event is equally or more severe than log_min_error_statement, the query will be captured. To view the varying severity levels, reference log_min_messages.

Useful for:

• Detecting what queries induced specific logs
• Detecting what queries induced a specific error

Example log:

1
duplicate key value violates unique constraint "example_pkey"
2
...
3
# affiliated subfield
4
query: "insert into example (id) values (1), (1);

log_min_messages#

Determines what query generated logs (not background or networking logs) are recorded based on severity level, as described in the table below.

As an example of how log_min_messages works, if the setting were changed to error, Postgres would stop recording logs with the severity levels warning, notice, info, and debug1 ... debug5 events. However, it would continue recording all error, log, fatal, and panic occurrences.

Useful for:

• Controlling what query generated logs are recorded overall

log_statement#

Logs queries that match the configured action type:

• ddl: Log all alter, drop, and create commands
• all: Log all queries
• mod: Log update, insert, delete, and merge commands
• none: Log nothing (disables the setting)

Useful for:

• Monitoring queries on your platform

Example log:

1
# Logging a select query
2
statement: select * from testing WHERE id = 5 limit 100;

pgaudit.*#

A suite of log settings enabled by the pgAudit extension. Unlike log_statement, it allows you to monitor queries against specific tables, with a higher degree of granularity.

Useful for:

• Monitoring queries on your platform

Example log:

1
# Logging a DDL query
2
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(
3
  id int,
4
  name text,
5
  description text
6
); <not logged>

Configuration methods:

Review the pgAudit docs for more configuration details.

Resources#

• Advanced Log Filtering
• Database Function Logging
• Supabase Logging