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


Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
  • Search
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
Brand Logo

Cloudron Forum

Apps | Demo | Docs | Install
  1. Cloudron Forum
  2. Support
  3. postgresql configuration / slow log

postgresql configuration / slow log

Scheduled Pinned Locked Moved Solved Support
postgresql
8 Posts 4 Posters 1.4k Views 4 Watching
  • 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 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.dV Offline
        vladimir.dV Offline
        vladimir.d
        wrote 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
          • nebulonN 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?

            marcusquinnM Offline
            marcusquinnM Offline
            marcusquinn
            wrote on last edited by
            #4

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

            Web Design https://www.evergreen.je
            Development https://brandlight.org
            Life https://marcusquinn.com

            1 Reply Last reply
            0
            • nebulonN nebulon moved this topic from Support on
            • nebulonN 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?

              vladimir.dV Offline
              vladimir.dV Offline
              vladimir.d
              wrote 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 Offline
                    marcusquinnM Offline
                    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.

                    Web Design https://www.evergreen.je
                    Development https://brandlight.org
                    Life https://marcusquinn.com

                    1 Reply Last reply
                    1
                    • M msbt referenced this topic on
                    Reply
                    • Reply as topic
                    Log in to reply
                    • Oldest to Newest
                    • Newest to Oldest
                    • Most Votes


                      • Login

                      • Don't have an account? Register

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