Pg_Gather Tool for a PostgreSQL Database Health Assessment

​Pg_Gather will collect and review data from your database using several SQL scripts. After analyzing, it will generate a database health analysis report from the data collected. Everything is written in SQL format to leverage the built-in features of psql, the command-line utility of PostgreSQL.

Pg_Gather is supported in PostgreSQL Versions : 10-17

You can read more about the Pg_Gather tool here. 

Pg_Gather Pre-Requisites

Download pg_gather, unzip it, and provision PostgreSQL permissions.

Also, install and create the following pg_stat_statement:

root@ip-172-xx-xx-xxx:/home/ubuntu# wget https://github.com/jobinau/pg_gather/archive/refs/heads/main.zip
root@ip-172-xx-xx-xxx:/home/ubuntu# unzip main.zip
root@ip-172-xx-xx-xxx:/home/ubuntu#  chown -R postgres:postgres pg_gather-main

Creating Sample Tables with Data Using the pgbench Utility

postgres@ip-172-xx-xx-xxx:/home/ubuntu$ pgbench -i -s 50 pgtest
dropping old tables...
creating tables...
generating data (client-side)...
5000000 of 5000000 tuples (100%) done (elapsed 16.99 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 34.99 s (drop tables 0.03 s, create tables 0.01 s, client-side generate 17.09 s, vacuum 6.33 s, primary keys 11.54 s).

Creating More Connections and Load on the PostgreSQL Database

postgres@ip-172-xx-xx-xxx:/home/ubuntu$ pgbench -c 10 -j 2 -t 100 pgtest
pgbench (16.2 (Ubuntu 16.2-1.pgdg20.04+1))
starting vacuum...end.
transaction type: 
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 1000/1000
number of failed transactions: 0 (0.000%)
latency average = 8.961 ms
initial connection time = 24.249 ms
tps = 1115.956844 (without initial connection time)

Creating Indexes Manually

pgtest=# create index ixd_bid_abalance on pgbench_accounts(bid,abalance);
CREATE INDEX
pgtest=# create index ixd_abalance_bid on pgbench_accounts(abalance,bid);
CREATE INDEX
pgtest=# create index ixd_filler on pgbench_accounts(filler);
CREATE INDEX

Gathering Data from the PostgreSQL Database

postgres@ip-172-xx-xx-xxx:/home/ubuntu/pg_gather-main$ psql -X -d pgtest -f gather.sql > /tmp/out.tsv
postgres@ip-172-xx-xx-xxx:/home/ubuntu/pg_gather-main$ ls -ltrh /tmp/out.tsv
-rw-rw-r-- 1 postgres postgres 255K Mar 13 09:01 /tmp/out.tsv

Analyzing the Pg_Gather Data

Collected data needs to be imported into the PostgreSQL database. It’s recommended to not import this data into the same critical database that you’re assessing. Instead, spin-up a new instance on the same node using the initdb utility with a different port and import the data. Alternatively, you can use a temporary PostgreSQL database.

postgres@ip-172-xx-xx-xxx:/home/ubuntu/pg_gather-main$ psql -f gather_schema.sql -f /tmp/out.tsv
**Dropping pg_gather tables**
**Creating pg_gather tables**
Query buffer reset (cleared).
COPY 1
Tuples only is on.
Query buffer reset (cleared).
COPY 1
COPY 5
COPY 5000
COPY 4
COPY 1
COPY 366
COPY 27
COPY 0
COPY 92
COPY 170

Generating the Postgres Database Report

postgres@ip-172-xx-xx-xxx:/home/ubuntu/pg_gather-main$ psql -X -f gather_report.sql > GatherReport.html

Once the PostgreSQL database health report is generated, you will see a report that is similar to the following example screenshots:

PostgreSQL database health checks are an important process for staying on top of your performance and stability. By continually monitoring your Postgres database environment, you set your organization up for ongoing success. At Datavail, we have extensive experience assessing database environments of all sizes and complexities. Want to connect with our PostgreSQL experts for a database health check? Get in touch today.