- Newest
- Most votes
- Most comments
Hello,
Since you are able to successfully update other parameters, it's important to check if your log_min_duration_statement is set in any other context.
The log_min_duration_statement parameter can be set:
- Per Session
- Per User
- Per Database
- Globally
The global setting (parameter groups / postgresql.conf) takes the least precedence here. There are multiple ways to check where this value is coming from:
Psql command: \drds
This command must be run from within psql. It will provide a global view of all user or database -level custom settings:
select name, setting, unit, source, context from pg_settings where name = 'log_min_duration_statement';
name | setting | unit | source | context
----------------------------+---------+------+---------+-----------
log_min_duration_statement | -1 | ms | default | superuser
(1 row)
postgres=> set log_min_duration_statement = '20s';
SET
postgres=# select name, setting, unit, source, context from pg_settings where name = 'log_min_duration_statement';
name | setting | unit | source | context
----------------------------+---------+------+---------+-----------
log_min_duration_statement | 20000 | ms | session | superuser
(1 row)
postgres=>
\drds+
List of settings
Role | Database | Settings
------+----------+--------------------------------
bob | | log_min_duration_statement=20s
(1 row)
In this example, I would need to remove this setting from the role 'bob'. I can do this with the ALTER USER sql command:
\drds+
List of settings
Role | Database | Settings
------+----------+--------------------------------
bob | | log_min_duration_statement=20s
(1 row)
postgres=> alter user bob reset log_min_duration_statement;
ALTER ROLE
postgres=> \drds+
Did not find any settings.
Similarly, anything that was set per-database could be removed using the ALTER DATABASE command
\drds+
List of settings
Role | Database | Settings
------+----------+--------------------------------
| wms | log_min_duration_statement=20s
(1 row)
postgres=> alter database wms reset log_min_duration_statement ;
ALTER DATABASE
postgres=> \drds+
Did not find any settings
You can also check these settings using the pg_settings system view. This can be useful in case you don't have psql available, or if the value was set in the session level. The only caveat to using this method is that it is does not show settings for other users or databases or sessions. It only shows them for the user, database, and session that you are currently connected to the database with.
select name, setting, unit, source, context from pg_settings where name = 'log_min_duration_statement';
name | setting | unit | source | context
----------------------------+---------+------+---------+-----------
log_min_duration_statement | -1 | ms | default | superuser
(1 row)
postgres=> set log_min_duration_statement = '20s';
SET
postgres=# select name, setting, unit, source, context from pg_settings where name = 'log_min_duration_statement';
name | setting | unit | source | context
----------------------------+---------+------+---------+-----------
log_min_duration_statement | 20000 | ms | session | superuser
(1 row)
postgres=>
Thanks a lot! This saved my day :)
The parameter was set at the database level.
I'd like to add, for anyone will land here, that after
ALTER DATABASE <redacted> RESET log_min_duration_statement;
I had to disconnect and reconnect to see the change.
Relevant content
- Accepted Answerasked a year ago
- AWS OFFICIALUpdated 10 months ago
- AWS OFFICIALUpdated 10 months ago
- AWS OFFICIALUpdated 4 years ago
- AWS OFFICIALUpdated 2 years ago