Cloudron makes it easy to run web apps like WordPress, Nextcloud, GitLab on your server. Find out more or install now.


  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse

Cloudron Forum

Apps | Demo | Docs | Install

postgresql configuration / slow log

Scheduled Pinned Locked Moved Solved Support
postgresql
8 Posts 4 Posters 207 Views
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • vladimir.dV Offline
    vladimir.dV Offline
    vladimir.d
    wrote on last edited by girish
    #1

    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.dV 1 Reply Last reply
    1
  • vladimir.dV Offline
    vladimir.dV Offline
    vladimir.d
    replied to vladimir.d on last edited by
    #2

    @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
    
    1 Reply Last reply
    1
  • nebulonN Offline
    nebulonN Offline
    nebulon Staff
    wrote on last edited by
    #3

    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?

    marcusquinnM vladimir.dV 2 Replies Last reply
    0
  • marcusquinnM Online
    marcusquinnM Online
    marcusquinn
    replied to nebulon on last edited by
    #4

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

    We're not here for a long time - but we are here for a good time :)
    Jersey/UK
    Work & Ecommerce Advice: https://brandlight.org
    Personal & Software Tips: https://marcusquinn.com

    1 Reply Last reply
    0
  • nebulonN nebulon moved this topic from Support on
  • vladimir.dV Offline
    vladimir.dV Offline
    vladimir.d
    replied to nebulon on last edited by
    #5

    @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.

    1 Reply Last reply
    0
  • girishG Offline
    girishG Offline
    girish Staff
    wrote on last edited by
    #6

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

    1 Reply Last reply
    1
  • girishG girish moved this topic from Nextcloud on
  • girishG Offline
    girishG Offline
    girish Staff
    wrote on last edited by
    #7

    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.

    1 Reply Last reply
    2
  • girishG girish marked this topic as a question on
  • girishG girish has marked this topic as solved on
  • marcusquinnM Online
    marcusquinnM Online
    marcusquinn
    wrote on last edited by
    #8

    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.

    We're not here for a long time - but we are here for a good time :)
    Jersey/UK
    Work & Ecommerce Advice: https://brandlight.org
    Personal & Software Tips: https://marcusquinn.com

    1 Reply Last reply
    1

  • Login

  • Don't have an account? Register

  • Login or register to search.
  • First post
    Last post
0
  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
  • Login

  • Don't have an account? Register

  • Login or register to search.