Thread: Odd config issue, cannot set log_min_duration_statement
All;
I have a client running PostgreSQL v10
I set:
log_min_duration_statement = 0
and restarted the cluster but if I connect via psql and check it's still disabled
postgres=# show log_min_duration_statement ;
log_min_duration_statement
----------------------------
-1
(1 row)
I tried an alter system, now if I cat the auto.conf file i see:
$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
log_min_duration_statement = '0'
However even after another restart It's still disabled:
postgres=# show log_min_duration_statement ;
log_min_duration_statement
----------------------------
-1
(1 row)
I grep'ed for the setting in the config file just in case:
$ grep log_min_duration_statement postgresql.conf
log_min_duration_statement = 0 # -1 is disabled, 0 logs all statements
I'm stumped... Thoughts?
Thanks in advance
S Bob <sbob@quadratum-braccas.com> writes: > I set: > log_min_duration_statement = 0 > and restarted the cluster but if I connect via psql and check it's still > disabled If you just did "SET" then it'd only affect the current session. > I tried an alter system, now if I cat the auto.conf file i see: > $ cat postgresql.auto.conf > # Do not edit this file manually! > # It will be overwritten by the ALTER SYSTEM command. > log_min_duration_statement = '0' Hmm, that should have worked. What do you see in select * from pg_settings where name = 'log_min_duration_statement'; (the "source..." columns are important here) regards, tom lane
> I set:
> log_min_duration_statement = 0
> and restarted the cluster but if I connect via psql and check it's still
> disabled
If you just did "SET" then it'd only affect the current session.
> I tried an alter system, now if I cat the auto.conf file i see:
> $ cat postgresql.auto.conf
> # Do not edit this file manually!
> # It will be overwritten by the ALTER SYSTEM command.
> log_min_duration_statement = '0'
Hmm, that should have worked. What do you see in
select * from pg_settings where name = 'log_min_duration_statement';
(the "source..." columns are important here)
regards, tom lane
> On Jan 14, 2021, at 7:12 PM, S Bob <sbob@quadratum-braccas.com> wrote: > > and restarted the cluster but if I connect via psql and check it's still disabled Sounds to me like maybe the config file PG loads is not where you think it is...
On 1/14/21 7:31 PM, Tom Lane wrote: > S Bob <sbob@quadratum-braccas.com> writes: >> I set: >> log_min_duration_statement = 0 >> and restarted the cluster but if I connect via psql and check it's still >> disabled > If you just did "SET" then it'd only affect the current session. > >> I tried an alter system, now if I cat the auto.conf file i see: >> $ cat postgresql.auto.conf >> # Do not edit this file manually! >> # It will be overwritten by the ALTER SYSTEM command. >> log_min_duration_statement = '0' > Hmm, that should have worked. What do you see in > > select * from pg_settings where name = 'log_min_duration_statement'; > > (the "source..." columns are important here) > > regards, tom lane postgres=# \x Expanded display is on. postgres=# select * from pg_settings where name = 'log_min_duration_statement'; -[ RECORD 1 ]---+----------------------------------------------------------------------- name | log_min_duration_statement setting | -1 unit | ms category | Reporting and Logging / When to Log short_desc | Sets the minimum execution time above which statements will be logged. extra_desc | Zero prints all queries. -1 turns this feature off. context | superuser vartype | integer source | user min_val | -1 max_val | 2147483647 enumvals | boot_val | -1 reset_val | -1 sourcefile | sourceline | pending_restart | f
On 1/14/21 7:37 PM, Scott Ribe wrote: >> On Jan 14, 2021, at 7:12 PM, S Bob <sbob@quadratum-braccas.com> wrote: >> >> and restarted the cluster but if I connect via psql and check it's still disabled > Sounds to me like maybe the config file PG loads is not where you think it is... > Actually I thought about that too, verified it, and I also changed the shared_buffers setting at the same time I tried to change log_min_duration_statement, the shared_buffers change was successful
S Bob <sbob@quadratum-braccas.com> writes: > On 1/14/21 7:31 PM, Tom Lane wrote: >> Hmm, that should have worked. What do you see in >> select * from pg_settings where name = 'log_min_duration_statement'; > source | user IIRC, that means you've applied a setting via ALTER USER, which'd override the default from the config file. regards, tom lane
S Bob <sbob@quadratum-braccas.com> writes:On 1/14/21 7:31 PM, Tom Lane wrote:Hmm, that should have worked. What do you see in select * from pg_settings where name = 'log_min_duration_statement';source | userIIRC, that means you've applied a setting via ALTER USER, which'd override the default from the config file. regards, tom lane
Yup, that's the last thing I tried:
postgres=# ALTER SYSTEM SET log_min_duration_statement = 0;
and it added the entry in the postgresql.auto.conf file:
$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
log_min_duration_statement = '0'
But... it's still -1, even after another restart
S Bob <sbob@quadratum-braccas.com> writes: > On 1/14/21 7:53 PM, Tom Lane wrote: >> IIRC, that means you've applied a setting via ALTER USER, >> which'd override the default from the config file. > Yup, that's the last thing I tried: > postgres=# ALTER SYSTEM SET log_min_duration_statement = 0; But somewhere you did "ALTER *USER* yourself", and that's overriding the ALTER SYSTEM for your sessions. ALTER USER RESET should get rid of that. regards, tom lane