Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-55124

Support for connection pooler (pgbouncer) in PostgreSQL connection

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Use attached test_db_connection.php, it connects to the database and the prints out the encoding and standard_conforming_strings values and runs performance analysis on the setup_DB() function.

      1. Install pgBouncer and PostgreSQL on the same server, different ports.
      2. configure config.php to use pgBouncer tcp, port
      3. Confirm failure of connection
      4. configure config.php to use pgBouncer tcp, port, dbhandlesoptions
      5. confirm success of connection
      6. configure config.php to use pgBouncer dbsocket => path, port
      7. confirm failure of connection
      8. configure config.php to use pgBouncer dbsocket => path, port, dbhandlesoptions
      9. confirm success of connection.
      10. configure config.php to use pgBouncer dbsocket => true, port
      11. confirm failure of connection
      12. configure config.php to use pgBouncer dbsocket => true, port, dbhandlesoptions
      13. confirm success of connection.

      14. configure config.php to use PostgreSQL dbsocket => path, port
      15. confirm success of connection
      16. configure config.php to use PostgreSQL dbsocket => path, port, dbhandlesoptions
      17. confirm success of connection.
      18. configure config.php to use PostgreSQL dbsocket => true, port
      18. confirm success of connection
      20. configure config.php to use PostgreSQL dbsocket => true, port, dbhandlesoptions
      21. confirm success of connection.

      Show
      Use attached test_db_connection.php, it connects to the database and the prints out the encoding and standard_conforming_strings values and runs performance analysis on the setup_DB() function. 1. Install pgBouncer and PostgreSQL on the same server, different ports. 2. configure config.php to use pgBouncer tcp, port 3. Confirm failure of connection 4. configure config.php to use pgBouncer tcp, port, dbhandlesoptions 5. confirm success of connection 6. configure config.php to use pgBouncer dbsocket => path, port 7. confirm failure of connection 8. configure config.php to use pgBouncer dbsocket => path, port, dbhandlesoptions 9. confirm success of connection. 10. configure config.php to use pgBouncer dbsocket => true, port 11. confirm failure of connection 12. configure config.php to use pgBouncer dbsocket => true, port, dbhandlesoptions 13. confirm success of connection. — 14. configure config.php to use PostgreSQL dbsocket => path, port 15. confirm success of connection 16. configure config.php to use PostgreSQL dbsocket => path, port, dbhandlesoptions 17. confirm success of connection. 18. configure config.php to use PostgreSQL dbsocket => true, port 18. confirm success of connection 20. configure config.php to use PostgreSQL dbsocket => true, port, dbhandlesoptions 21. confirm success of connection.
    • Affected Branches:
      MOODLE_32_STABLE
    • Fixed Branches:
      MOODLE_32_STABLE
    • Pull Master Branch:

      Description

      Jordan's comments about testing MDL-54947 with pgbouncer:


      I just tested this on a staging server for MoodleCloud, where we use pgbouncer to pool connections.
      on prod, pgbouncer connects to an AWS RDS server
      on staging, pgbouncer connects to a locally running pgsql server configured to listen on 127.0.0.1:5432 and pgbouncer listens on 127.0.0.1:5431
      its worth mentioning this is postgresql 9.4.8 out of the box with no special configuration as its merely a testing env that has almost no load these values will likely differ if used on production.
      pre-patch with pgbouncer:
      1.0141880512238 2k setup_DB
      1.6915528774261 2k work_with_binary_data
      pre-patch without pgbouncer:
      11.553825855255 2k setup_DB
      1.4306471347809 2k work_with_binary_data
      after the patch, pgbouncer started refusing connections with the following in the log:
      unsupported startup parameter: options=--client_encoding=utf8 --standard_conforming_strings=on
      Pooler Error: Unsupported startup parameter: options
      So we set 'ignore_startup_parameters = options' in the pgbouncer ini, which I assume undoes what this patch is trying to accomplish?
      post-patch with pgbouncer (ignored_startup_parameters=options)
      0.3159339427948 2k setup_DB
      1.2424399852753 2k work_with_binary_data
      post-patch without pgbouncer
      10.278841018677 2k setup_DB
      1.0223269462585 2k work_with_binary_data


      options as a parameter is accepted by PostgreSQL, however it is not accepted by pgbouncer.

      Options: (See MDL-54947 for further background)

      1. Inform pgbouncer users they need to apply the following configurations
        • standard_conforming_strings = on
        • client_encoding = utf8
        • ALTER USER/DATABASE SET search_path=schema
      2. Implement options in Moodle to handle not sending options
        • 'dbconnectoptions' => 'connect|afterconnect|notneeded', 3 options default 'connect'
        • 'dbhandlesoptions' => true, which is notneeded and throws exceptions if you try to use schemas.

      The best approach needs to be determined and implemented.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  5/Dec/16