Skip to content

PostgreSQL

How to set up PMM to monitor a PostgreSQL or Percona Distribution for PostgreSQL database instance.

Summary

Before you start

Check that:

(PMM follows PostgreSQL’s end-of-life policy. For specific details on supported platforms and versions, see Percona’s Software Platform Lifecycle page.)

Create a database account for PMM

We recommend creating a PMM database account that can connect to the postgres database with the SUPERUSER role.

  1. Create a user. This example uses pmm. (Replace ****** with a strong password of your choice.)

    CREATE USER pmm WITH SUPERUSER ENCRYPTED PASSWORD '******';

    If your database runs on Amazon RDS / Aurora PostgreSQL, The SUPERUSER cannot be assigned.
    So we have to create the user first and then grant the rds_superuser role to it.

    CREATE USER pmm WITH PASSWORD '******';
GRANT rds_superuser TO pmm;
    Optionally, you can also set up a connection limit (only if the user is not a SUPERUSER):

    ALTER USER pmm CONNECTION LIMIT 10;

  2. PMM must be able to log in locally as this user to the PostgreSQL instance. To enable this, edit the pg_hba.conf file. If not already enabled by an existing rule, add:

    local   all             pmm                                md5
# TYPE  DATABASE        USER        ADDRESS                METHOD

    (Ignore the second line. It is a comment to show field alignment.)

  3. Reload the configuration:

    su - postgres
psql -c "select pg_reload_conf()"

  4. Check local login.

    psql postgres pmm -c "\conninfo"

  5. Enter the password for the pmm user when prompted.

Choose and configure an extension

Decide which database extension to use, and configure your database server for it. The choices are:

  1. pg_stat_statements, the original extension created by PostgreSQL, part of the postgresql-contrib package available on Linux.

  2. pg_stat_monitor is a new extension created by Percona. pg_stat_monitor has all the features of pg_stat_statements but adds bucket-based data aggregation, provides more accurate data, and can expose Query Examples.

Here are the benefits and drawbacks of each.

Benefits Drawbacks
pg_stat_statements 1. Part of official postgresql-contrib package. 1. No aggregated statistics or histograms.
2. No Query Examples.
pg_stat_monitor 1. Builds on pg_stat_monitor features.
2. Bucket-based aggregation.

For a more detailed comparison of extensions, follow pg_stat monitor User Guide

Bucket-based data aggregation

pg_stat_monitor collects statistics and aggregates data in a data collection unit called a bucket. These are linked together to form a bucket chain.

You can specify:

  • the number of buckets (the length of the chain);
  • how much space is available for all buckets;
  • a time limit for each bucket’s data collection (the bucket expiry).

When a bucket’s expiration time is reached, accumulated statistics are reset and data is stored in the next available bucket in the chain.

When all buckets in the chain have been used, the first bucket is reused and its contents are overwritten.

If a bucket fills before its expiration time is reached, data is discarded.

pg_stat_statements

Install

  • Debian/Ubuntu

    Root permissions

    apt install -y postgresql-contrib

  • Red Hat/CentOS

    Root permissions

    yum install -y postgresql-contrib

Configure

  1. Add these lines to your postgresql.conf file:

    shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 2048 # Increase tracked query string size
pg_stat_statements.track = all   # Track all statements including nested
track_io_timing = on             # Capture read/write stats

  2. Restart the database server. After the restart, the extension starts capturing statistics from every database.

  3. Install the extension. 

    psql postgres postgres -c "CREATE EXTENSION pg_stat_statements SCHEMA public"

    This command creates the view where you can access the collected statistics.

We recommend that you create the extension for the postgres database. In this case, you receive access to the statistics collected from every database.

You can now add the service.

pg_stat_monitor

pg_stat_monitor has been tested with:

  • PostgreSQL versions 11, 12, 13, 14.
  • Percona Distribution for PostgreSQL versions 11, 12, 13, 14.

Install

Configure

  1. Set or change the value for shared_preload_library.

    In your postgresql.conf file:

    shared_preload_libraries = 'pg_stat_monitor'

    Caution

    If you use both pg_stat_statements and pg_stat_monitor, set pg_stat_monitor after pg_stat_statements:

    shared_preload_libraries = 'pg_stat_statements, pg_stat_monitor'

  2. Set configuration values.

    In your postgresql.conf file: 

    pg_stat_monitor.pgsm_query_max_len = 2048

    Caution

    It is important to set maximal length of query to 2048 characters or more for PMM to work properly.

    You can get a list of other available settings with SELECT * FROM pg_stat_monitor_settings;.

    Other important parameters are: 

    pg_stat_monitor.pgsm_normalized_query
    and 
    pg_stat_monitor.pgsm_enable_query_plan

    If the value for pg_stat_monitor.pgsm_normalized_query is set to 1, the actual query values are replaced by placeholders. If the value is 0, the examples are given in QAN. Examples can be found in QAN details tab example.

    If pg_stat_monitor.pgsm_enable_query_plan is enabled, the query plans are captured and will be available in the Plan tab on the Query Analytics dashboard.

    See pg_stat_monitor online documentation for details about available parameters.

  3. Start or restart your PostgreSQL instance. The extension starts capturing statistics from every database.

  4. In a psql session:

    CREATE EXTENSION pg_stat_monitor;

    This command creates the view where you can access the collected statistics.

    We recommend that you create the extension for the postgres database. In this case, you receive the access to the statistics, collected from every database.

  5. Check the version.

    SELECT pg_stat_monitor_version();

Add service

When you have configured your database server, you can add a PostgreSQL service with the user interface or on the command line.

With the user interface

  1. Select Configuration → PMM Inventory → Add Instance.

  2. Select PostgreSQL – Add a remote instance.

  3. Enter or select values for the fields.

  4. Click Add service.

If your PostgreSQL instance is configured to use TLS, click on the Use TLS for database connections check box and fill in your TLS certificates and key.

Note

For TLS connection to work SSL needs to be configured in your PostgreSQL instance. Make sure SSL is enabled in the server configuration file postgresql.conf, and that hosts are allowed to connect in the client authentication configuration file pg_hba.conf. (See PostgreSQL documentation on Secure TCP/IP Connections with SSL.)

On the command line

Add the database server as a service using one of these example commands. If successful, PMM Client will print PostgreSQL Service added with the service’s ID and name. Use the --environment and -custom-labels options to set tags for the service to help identify them.

Examples

Add instance with default node (<node>-postgresql).

pmm-admin add postgresql \
--username=pmm \
--password=password \
--server-url=https://admin:admin@X.X.X.X:443 \
--server-insecure-tls
  • <user name>: The PostgreSQL PMM user
  • <password>: The PostgreSQL user credentials.

The service name will be automatically chosen.

Add instance with specified service name.

pmm-admin add postgresql \
--username=pmm \
--password=password \
--server-url=https://admin:admin@X.X.X.X:443 \
--server-insecure-tls \
--service-name=SERVICE-NAME

Add instance to connect with a UNIX socket.

pmm-admin add postgresql --socket=/var/run/postgresql

where: - SOCKET: directory containing the socket

Connecting via SSL/TLS

pmm-admin add postgresql --tls \
--tls-cert-file=PATHTOCERT \
--tls-ca-file=PATHTOCACERT \
--tls-key-file=PATHTOKEY \
--host=HOST \
--port=PORT \
--username=USER \
--service-name=SERVICE-NAME

where:

  • PATHTOCERT: Path to client certificate file.
  • PATHTOCACERT: Path to certificate authority file.
  • PATHTOKEY: Path to client key file.
  • HOST: Instance hostname or IP.
  • PORT: PostgreSQL service port number.
  • USER: Database user allowed to connect via TLS. Should match the common name (CN) used in the client certificate.
  • SERVICE: Name to give to the service within PMM.

Check the service

Check service - PMM user interface

  1. Select Configuration → PMM Inventory → Inventory list.

  2. Look in the Services tab for a matching Service Type (PostgreSQL), Service name, Addresses, and any other details entered in the form.

  3. Look in the Agents tab to check the desired data source is being used.

Check service - Command line

Look for your service in the output of this command.

pmm-admin inventory list services

If using Docker, use docker exec pmm-client pmm-admin inventory list services

Check data

  1. Open the PostgreSQL Instance Summary dashboard.

  2. Set the Service Name to the newly-added service.

Running custom queries

The PostgreSQL exporter can run custom queries to add new metrics not provided by default.
Those custom queries must be defined in the /usr/local/percona/pmm2/collectors/custom-queries/postgresql in the same host where the exporter is running. There are 3 directories inside it: - high-resolution/ - every 5 seconds - medium-resolution/ - every 10 seconds - low-resolution/ - every 60 seconds

Depending on the desired resolution for your custom queries, you can place a file with the queries definition. The file is a yaml where each query can have these fields:

query_name:
   query: the query definition
   master: boolean to specify if the query should be executed only in the master
   metrics:
     - metric name:
         usage: GAUGE, LABEL, COUNTER, MAPPEDMETRIC or DURATION
         description: a human readable description

Example

pg_postmaster_uptime:
   query: "select extract(epoch from current_timestamp - pg_postmaster_start_time()) as seconds"
   master: true
   metrics:
     - seconds:
         usage: "GAUGE"
         description: "Service uptime"

Check the see also section for a more detailed description on MySQL custom queries with more examples about how to use custom queries in dashboards.

See also

Last update: 2023-03-30