Skip to content
logo
Percona Monitoring and Management
PostgreSQL
Initializing search
    percona/pmm-doc
    percona/pmm-doc
    • Welcome
        • Network
        • Docker
        • Podman
        • Helm
        • Virtual Appliance
        • AWS Marketplace
        • Easy-install script
        • MySQL
        • MongoDB
        • PostgreSQL
          • Before you start
          • Create a database account for PMM
          • Choose and configure an extension
            • pg_stat_statements
              • Install
              • Configure
            • pg_stat_monitor
              • Install
              • Configure
          • Add service
            • With the user interface
            • On the command line
            • Examples
              • Connecting via SSL/TLS
          • Check the service
            • Check service - PMM user interface
            • Check service - Command line
            • Check data
            • Running custom queries
              • Example
        • ProxySQL
        • Amazon RDS
        • Microsoft Azure
        • Google Cloud Platform
        • Linux
        • External Services
        • HAProxy
        • Remote instances
      • User Interface
      • Percona Alerting
        • Prepare a storage location
          • MongoDB backup prerequisites
          • Create MongoDB on-demand and scheduled backups
          • Create MongoDB PITR backups
          • Restore a MongoDB backup
          • MongoDB Backup and Restore support matrix
          • MySQL backup prerequisites
          • Create a MySQL backup
          • Restore a MySQL backup
        • Edit a scheduled backup
        • Delete a backup
        • Configure access control
        • Labels for access control
        • Create access roles
        • Manage access roles
        • Assign roles to users
        • Use Case
      • Query Analytics
      • Working with Advisors
      • Configure
      • Manage users
      • Upgrade
      • Secure
      • Optimize
      • Annotate
      • Share dashboards and panels
      • Extend Metrics
      • Resolve issues
        • Check Percona Portal account information
      • Architecture
      • UI components
      • PMM components and versions
      • Data handling in PMM
      • Develop Advisor checks
      • PMM Inventory
        • Manage dashboards
            • Advanced Data Exploration
            • VictoriaMetrics
            • VictoriaMetrics Agents Overview
            • Environment Summary
            • Kubernetes Pods Status
            • Kubernetes Volumes
          • DBaas
            • Disk Details
            • Network Details
            • Memory Details
            • Node Temperature Details
            • Nodes Compare
            • Nodes Overview
            • Node Summary
            • NUMA Details
            • Processes Details
            • Prometheus Exporters Overview
            • MySQL Command/Handler Counters Compare
            • MySQL InnoDB Compression Details
            • MySQL InnoDB Details
            • MySQL MyISAM/Aria Details
            • MySQL MyRocks Details
            • MySQL Instance Summary
            • MySQL Instances Compare
            • MySQL Instances Overview
            • MySQL Wait Event Analyses Details
            • MySQL Performance Schema Details
            • MySQL Query Response Time Details
            • MySQL Replication Summary
            • MySQL Group Replication Summary
            • MySQL Table Details
            • MySQL User Details
            • MySQL TokuDB Details
            • Experimental MongoDB Collection Details
            • Experimental MongoDB Oplog Details
            • MongoDB Cluster Summary
            • MongoDB Instance Summary
            • MongoDB Instances Overview
            • MongoDB Instances Compare
            • MongoDB ReplSet Summary
            • MongoDB InMemory Details
            • MongoDB MMAPv1 Details
            • MongoDB WiredTiger Details
            • PostgreSQL Instance Summary
            • PostgreSQL Instances Compare
            • Experimental PostgreSQL Vacuum Monitoring
          • ProxySQL Dashboards
            • PXC/Galera Cluster Summary
            • Experimental PXC/Galera Cluster Summary
            • PXC/Galera Nodes Compare
            • HAProxy Instance Summary
        • pmm-admin - PMM Administration Tool
        • pmm-agent - PMM Client agent
      • API
      • VictoriaMetrics
      • ClickHouse
      • PostgreSQL
      • Glossary
      • Introduction
      • DBaaS architecture
        • Setting up DBaaS
        • Create a Kubernetes Cluster
        • Deleting Kubernetes clusters
        • Activating DBaaS
        • Add a Kubernetes cluster automatically
        • Add a Kubernetes cluster manually
        • Manage allowed component versions
          • OLM installation
          • Operators installation
        • Add a DB Cluster
        • Manage a DB Cluster
        • Delete a DB Cluster
        • Create a database cluster from a template
      • Backup and restore
    • FAQ
      • PMM 2.39.0
      • PMM 2.38.1
      • PMM 2.38.0
      • PMM 2.37.1
      • PMM 2.37.0
      • PMM 2.36.0
      • PMM 2.35.0
      • PMM 2.34.0
      • PMM 2.33.0
      • PMM 2.32.0
      • PMM 2.31.0
      • PMM 2.30.0
      • PMM 2.29.1
      • PMM 2.29.0
      • PMM 2.28.0
      • PMM 2.27.0
      • PMM 2.26.0
      • PMM 2.25.0
      • PMM 2.24.0
      • PMM 2.23.0
      • PMM 2.22.0
      • PMM 2.21.0
      • PMM 2.20.0
      • PMM 2.19.0
      • PMM 2.18.0
      • PMM 2.17.0
      • PMM 2.16.0
      • PMM 2.15.1
      • PMM 2.15.0
      • PMM 2.14.0
      • PMM 2.13.0
      • PMM 2.12.0
      • PMM 2.11.1
      • PMM 2.11.0
      • PMM 2.10.1
      • PMM 2.10.0
      • PMM 2.9.1
      • PMM 2.9.0
      • PMM 2.8.0
      • PMM 2.7.0
      • PMM 2.6.1
      • PMM 2.6.0
      • PMM 2.5.0
      • PMM 2.4.0
      • PMM 2.3.0
      • PMM 2.2.2
      • PMM 2.2.1
      • PMM 2.2.0
      • PMM 2.1.0
      • PMM 2.0.1
      • PMM 2.0.0
      • Copyright and licensing information
      • Trademark policy

    • Before you start
    • Create a database account for PMM
    • Choose and configure an extension
      • pg_stat_statements
        • Install
        • Configure
      • pg_stat_monitor
        • Install
        • Configure
    • Add service
      • With the user interface
      • On the command line
      • Examples
        • Connecting via SSL/TLS
    • Check the service
      • Check service - PMM user interface
      • Check service - Command line
      • Check data
      • Running custom queries
        • Example

    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.
    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, 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_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 → 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.)

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

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2023-09-22
    Percona LLC, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better. Read more about Percona Cookie Policy.