postgresql configuration / slow log

[vladimir.d]

We need to enable postgresql slow query logging and we have done the following:

$ docker exec -ti postgresql bash
$ psql -d MY_DATABASE_NAME

psql (12.10 (Ubuntu 12.10-1.pgdg20.04+1+b1))
Type "help" for help.

MY_DATABASE_NAME =# ALTER DATABASE MY_DATABASE_NAME SET log_min_duration_statement = 5000;

MY_DATABASE_NAME =# SELECT pg_sleep(15);

But the slow log file didn't appear anywhere (/run/postgresql, /var/log/postgresql, /var/lib/postgresql/12/main/).

We need to set logging_collector to on in the postgresql config /etc/postgresql/12/main/postgresql.conf which is read-only.

Also please advise how to amend postgresql configuration in reboot/upgrade safe way.

[vladimir.d]

@girish could you amend the postgresql image so supervisord runs postgresql using the config /var/lib/postgresql/12/main/postgresql.conf (/home/yellowtent/platformdata/postgresql/12/main/postgresql.conf)?

Namely I suggest creating a symlink to the file:

$ ln -s /var/lib/postgresql/12/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf

OR changing supervisord config file /etc/supervisor/conf.d/postgresql.conf:

- command=/usr/lib/postgresql/12/bin/postmaster --config-file=/etc/postgresql/12/main/postgresql.conf
+ command=/usr/lib/postgresql/12/bin/postmaster --config-file=/var/lib/postgresql/12/main/postgresql.conf

[nebulon]

I am not sure what exactly the setting will do, but generally allowing to tweak these things is not much supported as it is quite easy to break things and adjusting the values makes it impossible for us to test with then.

Can you maybe explain the use-case on hand so we can see if it makes sense to possibly properly support that feature?

[marcusquinn]

@nebulon We can look into Nextcloud codebase optimisation with this, since it does suffer for speed at scale.

[vladimir.d]

@nebulon for the beginning we'd like to enable slow queries log to identify what causes postgresql taking almost 100% of CPU time all the time.

[girish]

Ah, this is a good suggestion. I guess the intent here is that this will help us report slow queries upstream @vladimir-d ?

[girish]

I have pushed this change now for 7.3.2. In the container, you can edit /run/postgresql/postgresql.conf . This won't persist but should help debugging.

[marcusquinn]

Sounds good. Yes, we're happy to help with query optimisation in apps and send feedback upstream. This is the quickest way we can do that. With some luck, we'll all end up with faster apps from this R&D too.