PostgreSQL

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

Summary

• Create PMM account and set permissions.
• Choose, install and configure an extension:
  • pg_stat_statements, or,
  • pg_stat_monitor.
• Add service.
• Check the service.

Before you start

Check that:

• PMM Server is installed and running with a known IP address accessible from the client node.
• PMM Client is installed and the node is registered with PMM Server.
• You have superuser (root) access on the client host.
• You have superuser access to any database servers that you want to monitor.

(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 ENCRYPTED 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 3. No plan execution information
pg_stat_monitor	1. Builds on pg_stat_monitor features. 2. Bucket-based aggregation.

For a more detailed comparison of extensions, see Comparison with pg_stat_statements in the pg_stat_monitor documentation.

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, 15.
• Percona Distribution for PostgreSQL versions 11, 12, 13, 14, 15.

Install

• If you use Percona Distribution for PostgreSQL, you can install the extension with your Linux package manager. See Installing Percona Distribution for PostgreSQL.

• If you use PostgreSQL you can install by downloading and compiling the source code. See Installing pg_stat_monitor.

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_settings WHERE name LIKE 'pg_stat_monitor.%';.

   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 → Inventory → Add Service.

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

Auto-discovery limit

Starting with PMM 2.41.0 you can set a limit for Auto-discovery in PostgreSQL, a feature that dynamically discovers all databases in your PostgreSQL instance.

Limiting Auto-discovery reduces connections and prevents high CPU and RAM usage caused by multiple databases.

Caution

Limiting auto-discovery may result in fewer metrics being captured from the non-primary databases. Ensure that you set the limit appropriately:

• Setting a high limit may impact performance adversely.
• Setting a low limit might result in some missing metrics due to Auto-discovery being disabled.

By default, Auto-discovery is enabled (server defined with a limit 10).

When you select Disabled, the Auto-discovery limit will be set to -1.

For a custom value, select Custom and enter or choose your preferred value from the Auto-discovery limit field.

Maximum connection limit

Starting with PMM 2.42.0, you can set a maximum limit on the number of connections that the PostgreSQL exporter can open to the same PostgreSQL instance.

By setting a maximum connection limit, you can prevent excessive connections during concurrent operations, and ensure that connections are closed promptly to avoid idle connections.

When adjusting the maximum number of connections, consider the following:

• higher values might be needed for larger or busier instances.
• setting the limit too high can impact performance.
• if no limit is specified or the option is disabled, the server will manage the connection limits automatically.

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 service name (<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.

Automatic discovery limit via CLI

Starting with PMM 2.41.0, there is a new flag in pmm-admin to limit Auto-discovery:

--auto-discovery-limit=XXX

• If number of databases > Auto-discovery limit, then auto discovery is OFF
• If number of databases <= Auto-discovery limit, then auto discovery is ON
• If the Auto-discovery limit is not defined, it takes the default value, which is 0 (server defined with limit 10), and Auto-discovery is ON(if you do not have more than 10 databases).
• If Auto-discovery limit < 0 then auto discovery is OFF.

Example:

If you set the limit to 10 and your PostgreSQL instance has 11 databases, automatic discovery will be disabled.

pmm-admin add postgresql --username="pmm-agent" --password="pmm-agent-password" --auto-discovery-limit=10

Check the service

Check service - PMM user interface

1. Select Configuration → Inventory.
2. In the Services tab, verify the Service name, Address and any other relevant details.
3. In the Options column, expand the Details section and check that the Agents are using the desired data source.

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

• pmm-admin man page for pmm-admin add postgresql
• Configuring Percona Repositories with percona-release
• Percona Blog – Running Custom MySQL Queries in Percona Monitoring and Management

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert