Database Troubleshooting - bcgov/common-hosted-form-service GitHub Wiki

This documentation is no longer being updated. For the most up to date information please visit our techdocs

Home > Developer > DevOps > Database Troubleshooting

CHEFS uses the PostgreSQL database with high availability provided by Patroni. As CHEFS users increase the database grows both in size and the number of queries that it has to process. The queries must be very efficient, otherwise they will become a bottleneck, the load on the database will grow until it reaches the CPU limit, and then API calls will begin to fail.

Basic Monitoring (TODO)

Using pg_stat_statements

Using the primary database pod, check if the pg_stat_statements extension is installed:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

If not then check that it's available:

SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

It should be there! If it isn't then you're on your own. To install the extension:

CREATE EXTENSION pg_stat_statements;

Now re-check that the extension is installed:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

Doing a SELECT for the view should fail:

SELECT * FROM pg_stat_statements;

SQL Error [55000]: ERROR: pg_stat_statements must be loaded via shared_preload_libraries

You need to edit the Patroni configuration, which is fun because Patroni uses a minimalist container that doesn't have an editor like ed or vi.

  1. Open a terminal for one of the database pods.
  2. Temporarily install busybox to use it as an editor: 
    1. export EDITOR=/tmp/vi

    2. curl https://busybox.net/downloads/binaries/1.35.0-x86_64-linux-musl/busybox --output $EDITOR

    3. chmod 700 $EDITOR
  3. Now's the time to use the fancy Patroni editing: 
    patronictl edit-config
  4. Add shared_preload_libraries: pg_stat_statements under parameters (keep them in order - we love alphabetizing things!). Save the file and choose Y to apply the changes. This will update /home/postgres/pgdata/pgroot/data/patroni.dynamic.json, and then updates /home/postgres/pgdata/pgroot/data/postgresql.conf
  5. Delete the editor: 
    rm $EDITOR
  6. Restart Patroni - this does not restart the pods, there is no outage: 
    patronictl restart master

Done! pg_stat_statements can now be used to find long-running queries.