Learn how to – How To Monitor PostgreSQL Database with pgDash

Learn how to – How To Monitor PostgreSQL Database with pgDash.

PostgreSQL is an open-source full-featured relational database developed in the 80’s. It was first developed at the Berkeley Computer Sciences Department at the University of California. It extends the SQL language by providing advanced features that store and scale the most complicated workloads. PostgreSQL supports both SQL (relational) and JSON (non-relational) querying. With the amazing features, PostgreSQL finds high use in Online Transaction Processing workloads, development with support for web frameworks such as Python’s Django, node.js, Hibernate, Ruby on Rails, PHP e.t.c

The PostgreSQL can be monitored to identify the health and performance of the database. Several tools can be used to perform monitoring on the PostgreSQL database. These tools include Logtail, pganalyze, pgDash, Sematext, AppDynamics, Nagios Core, Datadog, PRTG, pgWatch e.t.c

PgDash is one of the tools used to monitor the PostgreSQL database. It offers detailed and comprehensive metrics visualization for the database. It can be used as SaaS or self-hosted/on-premise. This tool works by collecting the metrics from PostgreSQL in JSON format using the pgmetrics tool. The collected metrics are then sent regularly using the Pgdash command-line tool. It collects metrics about each executed query and provides suggestions to improve the query performance

The features associated with PgDash are:

• Queries: It offers detailed and extensive information about SQL queries. This includes time series graphs, execution plans with visualization, and suggestions to improve query performance.
• Diagnostics: it examines the PostgreSQL server and performs database scanning for potential issues that can impact the health and performance of the deployment.
• Replication: it helps monitor replication metrics, including lags at primary and standby servers, physical and logical replication slot information, and standby recovery progress.
• Inspector: quickly pull up dashboards for key subsystem health and performance indicators across your entire fleet of Postgres servers and databases.
• Tables & Indexes: it shows information about each table and index, like size, bloat, activity, vacuum and analysis information, cache efficiency, and more.
• Locks & Backends: It allows users to see the queries and those waiting for others to be executed. Track backends waiting on locks, transactions that have been open too long, and idling transactions.
• Integrations: it can be used to provide metrics from other systems associated with your PostgreSQL server to provide a comprehensive picture of the entire system status. It can be integrated with AWS CloudWatch and PgBouncer.
• Collaboration: it allows users to share all the data for a server with your team members
• Alerting: it is possible to configure alerts on pgDash. The pgDash Basic Alerting allows users to configure meaningful alerts, such as “Commit Ratio of mydb is less than 80%”, “Number of backends waiting on locks is greater than 30”, and be able to receive notifications via email, PagerDuty, Slack, VictorOps, xMatters, and Webhooks

In this guide, we will learn how to monitor PostgreSQL Database with pgDash.

Getting Started

For this guide, you need PostgreSQL installed. This can be done using the aid provided in the guides below:

• How To Install PostgreSQL 14 on Rocky Linux 9
• Install PostgreSQL 14 on Ubuntu 22.04 (Jammy Jellyfish)
• How To Install PostgreSQL 14 on Debian
• How To Install PostgreSQL 14 CentOS 7 | RHEL 7

Once the PostgreSQL has been installed, you can proceed as below.

#1. Install and Configure pgmetrics

The pgmetrics tool collects the metrics from the PostgreSQL database. It can be downloaded from the pgmetrics downloads page.

It is also possible to pull the archive using cURL:

##For AMD64
VER=$(curl -s https://api.github.com/repos/rapidloop/pgmetrics/releases/latest|grep tag_name|cut -d '"' -f 4|sed 's/v//')
curl -O -L https://github.com/rapidloop/pgmetrics/releases/download/v${VER}/pgmetrics_${VER}_linux_amd64.tar.gz

##For ARM64
curl -O -L https://github.com/rapidloop/pgmetrics/releases/download/v${VER}/pgmetrics_${VER}_linux_arm64.tar.gz

##For ARM
curl -O -L https://github.com/rapidloop/pgmetrics/releases/download/v${VER}/pgmetrics_${VER}_linux_arm.tar.gz

Once downloaded, extract the archive:

tar xvf pgmetrics_${VER}_linux_*.tar.gz

Now copy the binary file to your $PATH;

sudo mv pgmetrics_${VER}_linux_*/pgmetrics /usr/local/bin

Export the $PATH variable.

$ sudo vim /etc/profile
export PATH=$PATH:/usr/local/bin

Add your the postgres user to the sudoers file:

$ sudo vim /etc/sudoers
postgres ALL=(ALL) NOPASSWD: ALL

Switch to the posgres user

sudo -i -u postgres

Now we will test the tool using the command with the syntax below:

pgmetrics -f json -h <PGHOST> -U <dbuser> <dbname>

In the command, you need to provide the PGHOST, the User, and the database to connect to. For example:

$ pgmetrics -f json -h localhost -U admin admin
Password: 

For this demo, I have a PostgreSQL user admin with a database called admin. You will see a JSON output as shown below:

#2. Install pgdash CLI

The pgdash CLI is used to upload the metrics collected to the pgDash website. To download it, visit the GitHub release page.

You can also pull the latest archive using cURL:

##For AMD64
VER=$(curl -s https://api.github.com/repos/rapidloop/pgdash/releases/latest|grep tag_name|cut -d '"' -f 4|sed 's/v//')
curl -O -L  https://github.com/rapidloop/pgdash/releases/download/v${VER}/pgdash_${VER}_linux_amd64.tar.gz

##For ARM64
VER=$(curl -s https://api.github.com/repos/rapidloop/pgdash/releases/latest|grep tag_name|cut -d '"' -f 4|sed 's/v//')
curl -O -L https://github.com/rapidloop/pgdash/releases/download/v${VER}/pgdash_${VER}_linux_arm64.tar.gz

##For ARM
VER=$(curl -s https://api.github.com/repos/rapidloop/pgdash/releases/latest|grep tag_name|cut -d '"' -f 4|sed 's/v//')
curl -O -L https://github.com/rapidloop/pgdash/releases/download/v${VER}/pgdash_${VER}_linux_arm.tar.gz

Extract the archive:

tar xvf pgdash_${VER}_*.tar.gz

Copy the binary file to your $PATH:

sudo mv pgdash_${VER}_*/pgdash /usr/local/bin

#3. Get Your API Key

Sign up for pgDash and obtain your API key.

Once an account has been created, the API key will be available in the Profile tab.

#4. Create and Upload a Report

Now you need to create and upload a report in JSON format using pgdash. The command has the syntax below:

pgmetrics --no-password -f json <dbname> | pgdash -a <YOUR-API-KEY> report <hostname>

The above command will push the reports without requesting for password. Remember to provide the same database name and hostname that was used to collect the metrics with pgmetrics earlier.

For example:

pgmetrics --no-password -f json admin | pgdash -a someAPIkey report localhost

#5. View Metrics on pgdash

Once the metrics have been pushed, you will see the host added on the pgdash dashboard.

Click on the host to view the metrics

Scroll down to view the system information:

Working With Queries

To collect query and plan information with pgDash, you need to configure two PostgreSQL extensions i.e pg_stat_statements and auto_explain.

First, install the Contrib package. For example on Rocky, with PostgreSQL 14, the command will be

sudo dnf install postgresql14-contrib

To enable the pg_stat_statements extensions, edit the PostgreSQL config

##On Debian/Ubuntu
sudo vim /etc/postgresql/14/main/postgresql.conf

##On Rhel/CentOS/Rocky/Alma
sudo vim /var/lib/pgsql/14/data/postgresql.conf 

Uncomment and edit the below lines

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_activity_query_size = 2048

Still in the file, enable track_io_timing

......
# STATISTICS
#------------------------------------------------------------------------------

# - Query and Index Statistics Collector -

track_activities = on
#track_activity_query_size = 1024       # (change requires restart)
#track_counts = on
track_io_timing = on
#track_wal_io_timing = off
....

Save the file and restart PostgreSQL:

sudo systemctl restart postgresql-14

Now login to PostgreSQL and switch to your database

psql
c admin;

Enable pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify if the changes have been made:

admin=# SELECT * FROM pg_stat_statements LIMIT 1;
 userid | dbid  | toplevel |       queryid       |                           query        
                   | plans | total_plan_time | min_plan_time | max_plan_time | mean_plan_t
ime | stddev_plan_time | calls | total_exec_time | min_exec_time | max_exec_time | mean_ex
ec_time | stddev_exec_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirti
ed | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_b
lks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | wal_re
cords | wal_fpi | wal_bytes 
--------+-------+----------+---------------------+----------------------------------------
-------------------+-------+-----------------+---------------+---------------+------------
----+------------------+-------+-----------------+---------------+---------------+--------
--------+------------------+------+-----------------+------------------+------------------
---+---------------------+----------------+-----------------+--------------------+--------
------------+----------------+-------------------+---------------+----------------+-------
------+---------+-----------
.....
admin=# q

#6. Configure Cron Job

To automate the pushing of metrics to the pgDash dashboard, we will create a Cron Job. This can be done by executing the command:

crontab -e

Add the below lines to the file replacing them appropriately

PATH=/bin:/usr/bin:/usr/local/bin    
*/5 * * * * pgmetrics --no-password -f json <dbname> | pgdash -a <api-key> report <server-name>

The above task will automatically upload the metrics to the pgDash dashboard after every 5 minutes.

Now view the available databases.

You can view the metrics of each database by clicking on it.

View the SQL queries executed in the PostgreSQL database

View the Backends

You can also configure alerts as desired.

To check the health of the host system, PostgreSQL server, and replication, go back to the homepage and click on Inspector

Select the metrics you want to visualize and click on Inspect.

Recommended books:

• Best Books To Learn PostgreSQL Databases

Closing Thoughts

We have triumphantly walked through how to monitor PostgreSQL Database with pgDash. Now you can easily monitor the queries, tables and indexes, server health, and performance among many others. I hope this was significant to you.

See more:

• Monitor Percona MySQL / Percona XtraDB Cluster With Prometheus & Grafana
• Best Books To Learn PostgreSQL Database
• How To Monitor Linux Server Uptime with Prometheus