Using the pt-pg-summary Percona Toolkit for PostgreSQL

Sebastian Insausti

The Percona Toolkit is a powerful, free, and open-source product developed by Percona. It is a collection of command-line tools that allow you to perform different MySQL, MongoDB, PostgreSQL, and system tasks that, in general, are too complex to perform manually. It supports Percona Server for MySQL, MySQL, MariaDB, PostgreSQL, Percona Server for MongoDB, and MongoDB.

In this blog, we will show you how to install Percona Toolkit and how to use a new tool added recently in the Percona Toolkit package called pt-pg-summary.

Installing Percona Toolkits

First, let’s see how to install Percona Toolkits to be able to use pt-pg-summary. In this case, we will use CentOS 7 and PostgreSQL 12.

Install the Percona Repository:

$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Having the repository installed, you should be able to install the percona-toolkit package:

$ yum install percona-toolkit

Now you just need to run it using the correct parameters.

$ pt-pg-summary --help

usage: pt-pg-summary [<flags>]

Percona Toolkit - PostgreSQL Summary

Flags:

      --help                     Show context-sensitive help (also try --help-long and --help-man).

      --version                  Show application version.

      --databases=DATABASES ...  Summarize this comma-separated list of databases. All if not specified

  -h, --host=HOST                Host to connect to

  -W, --password=PASSWORD        Password to use when connecting

  -p, --port=PORT                Port number to use for connection

      --sleep=10                 Seconds to sleep when gathering status counters

  -U, --username=USERNAME        User for login if not current user

      --disable-ssl              Diable SSL for the connection

      --verbose                  Show verbose log

      --debug                    Show debug information in the logs

Running Percona Toolkits

When you run this tool, you will see an output grouped by the following categories:

  • Database Port and Data_Directory
  • List of Tablespaces
  • Slave and the lag with Master
  • Cluster Information
  • Databases
  • Index Cache Hit Ratios
  • Table Cache Hit Ratios
  • List of Wait_events for the entire Cluster - all-databases
  • List of users and client_addr or client_hostname connected to --all-databases
  • Counters diff after 10 seconds
  • Table access per database
  • Instance settings
  • Processes startup command

So, let’s run it on a PostgreSQL instance to see an example:

$ pt-pg-summary -hlocalhost -p5432 -Uadmindb

The first action is to collect the information from your PostgreSQL server:

INFO[0000] Connecting to the database server using: host=localhost port=5432 user=admindb sslmode=disable dbname=postgres

INFO[0000] Connection OK

INFO[0000] Detected PostgreSQL version: 12.0.5

INFO[0000] Getting global information

INFO[0000] Collecting global counters (1st pass)

INFO[0000] Collecting Cluster information

INFO[0000] Waiting 10 seconds to read  counters

INFO[0000] Collecting Connected Clients information

INFO[0000] Collecting Database Wait Events information

INFO[0000] Collecting Global Wait Events information

...

Then, it will show all the collected information in this way:

##### --- Database Port and Data_Directory --- ####

+----------------------+----------------------------------------------------+

|         Name         |                      Setting                       |

+----------------------+----------------------------------------------------+

| data_directory       | /var/lib/pgsql/12/data                             |

+----------------------+----------------------------------------------------+

##### --- List of Tablespaces ---- ######

+----------------------+----------------------+-----------------------------+

|         Name         |         Owner        |               Location      |

+----------------------+----------------------+-----------------------------+

| pg_default           | postgres             |                             |

| pg_global            | postgres             |                             |

+----------------------+----------------------+-----------------------------+

##### --- Slave and the lag with Master --- ####

+----------------------+----------------------+------------------+----------+

|  Application Name    |    Client Address    |      State       |   Lag    |

+----------------------+----------------------+------------------+----------+

| pgsql_14_node_0      | 10.10.10.127         | streaming        |     0.00 |

+----------------------+----------------------+------------------+----------+

##### --- Cluster Information --- ####

+---------------------------------------------------------------------------+

 Usename        : admindb

 Time           : 2020-11-12 17:27:18.200552 +0000 UTC

 Client Address : ::1

 Client Hostname:

 Version        : PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8....

 Started        : 2020-11-12 17:07:07.185407 +0000 UTC

 Is Slave       : false

+---------------------------------------------------------------------------+

##### --- Databases --- ####

+----------------------+------------+

|       Dat Name       |    Size    |

+----------------------+------------+

| postgres             |    8193 kB |

| template1            |    8193 kB |

| template0            |    8049 kB |

+----------------------+------------+

##### --- Index Cache Hit Ratios --- ####

Database: postgres

+----------------------+------------+

|      Index Name      |    Ratio   |

+----------------------+------------+

| index hit rate       |      0.00  |

+----------------------+------------+

##### --- Table Cache Hit Ratios --- ####

Database: postgres

+----------------------+------------+

|      Index Name      |    Ratio   |

+----------------------+------------+

| cache hit rate       |       0.00 |

+----------------------+------------+

##### --- List of Wait_events for the entire Cluster - all-databases --- ####

+----------------------+----------------------+---------+

|   Wait Event Type    |        Event         |  Count  |

+----------------------+----------------------+---------+

| Activity             | BgWriterHibernate    |     1   |

| Activity             | WalWriterMain        |     1   |

| Activity             | LogicalLauncherMain  |     1   |

| Activity             | WalSenderMain        |     1   |

| Client               | ClientRead           |     2   |

| Activity             | CheckpointerMain     |     1   |

| Activity             | AutoVacuumMain       |     1   |

+----------------------+----------------------+---------+

##### --- List of users and client_addr or client_hostname connected to --all-databases --- ####

+----------------------+------------+---------+----------------------+--------+

|   Wait Event Type    |        Client        |         State        |  Count |

+----------------------+------------+---------+----------------------+--------+

| admindb              | 10.10.10.121/32      | idle                 |      2 |

| cmon_replication     | 10.10.10.127/32      | active               |      1 |

| admindb              | ::1/128              | active               |      1 |

+----------------------+------------+---------+----------------------+--------+

##### --- Counters diff after 10 seconds --- ####

+----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+

| Database             | Numbackends | XactCommit | XactRollback | BlksRead    | BlksHit    | TupReturned | TupFetched | TupInserted | TupUpdated | TupDeleted | Conflicts | TempFiles | TempBytes | Deadlocks  |

+----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+

|                      |       0     |       0    |       0      |       0     |     119    |      77     |      31    |       0     |       0    |       0    |       0   |       0   |       0   |       0    |

| postgres             |       0     |      39    |       0      |       0     |    1541    |    1641     |     816    |       0     |       0    |       0    |       0   |       0   |       0   |       0    |

...

##### --- Table access per database --- ####

Database: postgres

+----------------------------------------------------+------+--------------------------------+---------+

|                       Relname                      | Kind |             Datname            |  Count  |

+----------------------------------------------------+------+--------------------------------+---------+

| pg_database_datname_index                          |   i  |                                |       1 |

| pg_class                                           |   r  | postgres                       |       1 |

| pg_database                                        |   r  |                                |       1 |

...

##### --- Instance settings --- ####

                      Setting                                            Value

allow_system_table_mods                       : off

application_name                              :

archive_cleanup_command                       :

archive_command                               : (disabled)

archive_mode                                  : off

archive_timeout                               : 0

array_nulls                                   : on

authentication_timeout                        : 60

autovacuum                                    : on

autovacuum_analyze_scale_factor               : 0.1

…

##### --- Processes start up command --- ####

PID  :    Command line

  5158 : /usr/pgsql-12/bin/postgres -p 5432

Now you have all this information, you can use it for different purposes like troubleshooting, performance monitoring, or even to have an overview of your current configuration. You can also complement this with another Percona Toolkit like “pt-summary” to have more information about the system where it is running.

Conclusion

Percona Toolkit is a powerful tool to help you with database management tasks. Now with pt-pg-summary, you will be able to have information about your PostgreSQL instance, and you can integrate this in a monitoring system or even combine this tool with another Percona Toolkit component to collect even more information from your systems.

ClusterControl
The only management system you’ll ever need to take control of your open source database infrastructure.