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 two potential ways to change log settings:

• Supabase CLI
• SQL commands

1
supabase --experimental \
2
postgres-config update --config log_lock_waits=true \
3
--remote=<project-ref>

1
supabase --experimental \
2
postgres-config delete --config log_lock_waits,log_disconnections,... \
3
--remote=<project-ref>

1
-- impacts the role
2
alter role postgres set log_statement = 'none';
3
4
-- impacts just the live connection
5
set log_statement = 'none';

1
alter role postgres set log_statement = default;

1
NOTIFY pgrst, 'reload config';

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"

1
supabase --experimental \
2
postgres-config update --config log_autovacuum_min_duration=10ms \
3
--remote=<project-ref>

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

1
supabase --experimental \
2
postgres-config update --config log_checkpoints=true \
3
--remote=<project-ref>

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

1
supabase --experimental \
2
postgres-config update --config log_lock_waits=true \
3
--remote=<project-ref>

1
alter role "postgres" set log_lock_waits to true;

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

1
supabase --experimental \
2
postgres-config update --config log_recovery_conflict_waits=true \
3
--remote=<project-ref>

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

1
supabase --experimental \
2
postgres-config update --config log_startup_progress_interval=1s \
3
--remote=<project-ref>

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

1
supabase --experimental \
2
postgres-config update --config log_temp_files=10MB \
3
--remote=<project-ref>

1
alter role "postgres" set "log_temp_files" to '10kB';

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

1
supabase --experimental \
2
postgres-config update --config log_connections=true \
3
--remote=<project-ref>

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

1
supabase --experimental \
2
postgres-config update --config log_disconnections=true \
3
--remote=<project-ref>

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

1
supabase --experimental \
2
postgres-config update --config cron.log_statement=false --no-restart \
3
--remote=<project-ref>

auto_explain.*

Logs the query plan of each statement that runs longer than a threshold. To set it up, see Log slow and failing queries.

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"

1
alter role "postgres" set "log_duration" to true;

log_min_duration_statement

Logs each statement that runs longer than a threshold. To set it up, see Log slow and failing queries.

log_min_error_statement

Records the statement behind each error, and is on by default. To find that statement in your logs, see Log slow and failing queries.

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

1
alter role "postgres" set "log_min_messages" to 'log';

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;

1
alter role "postgres" set "log_statement" to 'ddl';

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