PG Collector: SQL script for PostgreSQL health inspection

May 15, 2024

Summary: PG Collector for PostgreSQL is a sql script that gathers valuable database information, and presents it in a consolidated HTML file which provides a convenient way to view and navigate between different sections of the report.

PG Collector

Table of Contents

Overview

PG Collector is safe to run on production environments and does not create any database objects to produce the output.

With PG Collector an operator gains insights on various aspects of the database, such as:

  • Database size
  • Configuration parameters
  • Installed extensions
  • Vacuum & Statistics
  • Unused Indexes & invalid indexes
  • Users & Roles Info
  • Toast Tables Mapping
  • Database schemas
  • Fragmentation (Bloat)
  • Tablespaces Info
  • Memory setting
  • Tables and Indexes Size and info
  • Transaction ID
  • Replication slots
  • public Schema info
  • Unlogged Tables

and more, please check the example reports.

Versioning Policy

Starting from PostgreSQL 13, PG Collector will have a dedicated script for each PostgreSQL major version.

Each PG Collector major version will have it is own branch and the main branch will be for PG Collector that support PostgreSQL 12 and older versions.

branches

How to download PG Collector?

From pg-collector releases, select PG Collector version that match your PostgreSQL major version.

releases_1

releases_2

How to check PG Collector version?

  • from the PG Collector report header report_header_version

  • from the PG Collector script header script_header

PG Collector report header

header_V2.6

Example of PG Collector report

pg_collector v2.9

pg_collector v2.7

PG Collector Sample report

PG Collector output

Report name

PG Collector script will generate HTML file using the following naming convention pg_colletcor_[DB Name]-[timestamp].html.

[DB Name] : is the database name that you are connected to.

Example : pg_collector_testdb-2020-10-10_030920.html

Report location

PG Collector script will generate HTML file under /tmp directory.

How to run PG Collector script?

1. You need psql to be able to connect to the postgresql DB and run the pg_collector.sql script.

2. Download pg_collector.sql in your laptop or the host that want to access the database from.

3. Login to the database using psql.

psql -h [hostname or RDS endpoint] -p [Port] -d [Database name ] -U [user name]

4. Run the pg_collector.sql script

\i pg_collector.sql
\q

or use -f option in psql

psql -h [hostname or RDS endpoint] -p [Port] -d [Database name ] -U [user name] -f pg_collector.sql

Example:

$ psql -h localhost -p 5432 -d testdb -U mohamed

testdb=> \i pg_collector.sql
Output format is html.
Default footer is off.
testdb=> \q

$ ls -lhrt /tmp/pg_colletcor_*
-rw-r--r-- 1 mohamed mohamed 569K Oct  7 21:51 /tmp/pg_colletcor_testdb-2019-10-07_215146.html

5. Open the report using any internet browser.

Notes

1. It is ok to see below errors while executing the pg_colletcor.sql script if you did not install pg_stat_statements extension.

postgres=> \i pg_collector.sql
Output format is html.
Default footer is off.
psql:pg_collector.sql:481: ERROR:  relation "pg_stat_statements" does not exist
LINE 10: from pg_stat_statements
              ^
psql:pg_collector.sql:495: ERROR:  relation "pg_stat_statements" does not exist
LINE 10: from pg_stat_statements
              ^
psql:pg_collector.sql:509: ERROR:  relation "pg_stat_statements" does not exist
LINE 10: from pg_stat_statements
              ^
psql:pg_collector.sql:523: ERROR:  relation "pg_stat_statements" does not exist
LINE 10: from pg_stat_statements
              ^
postgres=> \q

2. If the Database have Tens of thousands of tables , some queries can take longer time. use statement_timeout to Abort any statement that takes more than the specified number of milliseconds. please check below example.

postgres=> set statement_timeout=30000;
SET
postgres=> \i pg_collector.sql
Output format is html.
Report name and location: /tmp/pg_collector_postgres-2021-07-22_194944.html
psql:pg_collector.sql:1442: ERROR:  canceling statement due to statement timeout
comments powered by Disqus