Different levels :

• configuration file postgresql.conf defaults here;
• environment variables in container, eg. PGDATA;
• parameters in container entrypoint, eg postgres -c shared_buffers=256m;
• set up in client:
  • for server (cluster): ALTER SYSTEM
  • for database : ALTER DATABASE
  • for user : ALTER ROLE
  • for session : ALTER SESSION

What to log - log_statement:

• none
• ddl : DDL (CREATE TABLE)
• mod : DDL (CREATE TABLE) + DQL (SELECT) + DML (INSERT) + DCL (GRANT)
• all : DDL (CREATE TABLE) + DQL (SELECT) + DML (INSERT) + DCL (GRANT) + ?

When to log - log_min_duration_statement :

• -1 : log nothing
• 30s: log queries which last at least 30 seconds, upon completion
• 0 : log all queries, upon completion

This setting apply to all queries executed, no matter who submit them.

To set these settings for all instances of the cluster, you can use postgresql.conf file. If you modify them, you'll need to reload the file using.

If you want to change them quickly, using SQL prompt, get an account with superadmin role (or a role with ALTER SYSTEM privilege). In GCP, use cloudsql.admin role and use flags.

To log all queries, irrespective of their type (DDL, DML) or duration, for all instances of the database, without restarting them.

ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_duration_statement=0;
SELECT pg_reload_conf();

To log all DDL/DML/DQL lasting more than 10 seconds, for all instance of the database, without restarting them.

ALTER SYSTEM SET log_statement = 'mod';
ALTER SYSTEM SET log_min_duration_statement='10s';
SELECT pg_reload_conf();

To test

# start you postgresql container
docker logs --follow $CONTAINER_NAME
psql --dbname "host=localhost port=5400 dbname=database user=user password=password" --command="SELECT pg_sleep(15);"

You'll get

2025-05-07 12:31:49.143 UTC [1633] LOG:  duration: 15014.740 ms  statement: SELECT pg_sleep(15);

To log the execution plan of all queries, without using EXPLAIN PLAN, use auto_explain module:

• for all sessions, use shared_preload_libraries : change postgresql.conf or ALTER SYSTEM SET shared_preload_libraries = 'auto_explain'; (each user can then change settings in its own session));
• for a specific session : LOAD 'auto_explain'; (only superuser can do that, and it will be active till session ends).

To log the execution plan of all queries lasting more than 1 minute, with minimum details (few overhead):

SET auto_explain.log_min_duration = '10s';
SET auto_explain.log_analyze = false;
SET auto_explain.log_buffers = false;
SET auto_explain.log_timing = false;

To log the execution plan of all queries, with maximum details (more overhead):

SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;
SET auto_explain.log_timing = true;

To stop logging

SET auto_explain.log_min_duration = -1;

Test

SELECT count(*) FROM t;

You'll get

2025-05-07 12:51:41.361 UTC [2558] LOG:  duration: 0.012 ms  plan:
	Query Text: SELECT count(*) FROM t;
	Aggregate  (cost=41.88..41.88 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)
	  ->  Seq Scan on t  (cost=0.00..35.50 rows=2550 width=0) (actual time=0.004..0.005 rows=0 loops=1)

log_min_error_statement

By default: ERROR level events are considered errors and logged.

You can deactivate (why would you do that ?) error logging by setting to PANIC.

If you consider lower-criticity events to be worth logging, lower to this level (criticity ascending):

• DEBUG5
• DEBUG4
• DEBUG3
• DEBUG2
• DEBUG1
• INFO
• NOTICEs
• WARNING
• ERROR
• LOG
• FATAL
• PANIC