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 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:

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

   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. It is based on and compatible with pg_stat_statements. pg_stat_monitor has all the features of pg_stat_statements, but adds bucket-based data aggregation.

We recommend choosing only one of these. If you use both, you will get duplicate metrics.

Caution

While we recommend use of the newer pg_stat_monitor extension, be aware it is currently in beta phase and unsupported.

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.	1. Beta software.

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

Caution

pg_stat_monitor is currently in beta phase and is unsupported.

pg_stat_monitor has been tested with:

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

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_stat_monitor_settings;.

   See pg_stat_monitor GitHub repository 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

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 Postgres 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

---

Last update: 2022-06-03