PostgreSQL databases often run for long periods without anyone checking whether they are configured properly or if hidden issues are building up over time. Small problems such as missing indexes, inefficient configuration values, table bloat, or replication settings can gradually affect performance and reliability. The pg-healthcheck tool provides a simple way to examine a PostgreSQL instance and generate a detailed report covering many important areas of the database.
Clone the repository.
git clone https://github.com/pgEdge/pg-healthcheck.git
Go to the folder
cd pg-healthcheck
Now, build the program
go build -o pg-healthcheck ./cmd/...
Make the binary executable
chmod +x pg-healthcheck
Check the current clusters
pg_lsclusters
Result :
Ver Cluster Port Status Owner Data directory Log file14 main 5435 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log17 main 5434 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log18 main2 5433 online postgres /var/lib/postgresql/18/main2 /var/log/postgresql/postgresql-18-main2.log
Check the available help options related to this pg_healthcheck tool
./pg-healthcheck --help
Result :
pg-healthcheck runs 90+ checks across 13 groups against a single PostgreSQL
instance or a pgEdge multi-node distributed cluster.
Every check queries real system catalog views -- no estimated or simulated data.
Output is either coloured terminal text (default) or JSON for GUI / API use.
Usage:
pg-healthcheck [flags]
pg-healthcheck [command]
Available Commands:
ask Run checks selected by a natural-language query (uses Ollama LLM or keyword fallback)
completion Generate the autocompletion script for the specified shell
help Help about any command
Flags:
--backrest-config string Path to pgbackrest.conf
--config string Path to YAML config file
--dbname string Database name (default "postgres")
--groups string Groups to run, e.g. G01,G05 (default: all)
-h, --help help for pg-healthcheck
--host string PostgreSQL host (default "localhost")
--mode string Run mode: single | cluster (default "single")
--no-color Disable terminal colour
--nodes string Comma-separated host:port list for cluster mode
--output string Output format: text | json (default "text")
--password string Password (prefer PGPASSWORD env var)
--port int PostgreSQL port (default 5432)
--target-version int Target PG major version for G10 upgrade checks
--user string Role name (or PGUSER env var) (default "postgres")
--verbose Show OK findings (hidden by default)
-v, --version version for pg-healthcheck
Use "pg-healthcheck [command] --help" for more information about a command.
Now use the pg-healthcheck tool to generate a postgres health check report like this.
./pg-healthcheck \
--host 127.0.0.1 \
--port 5432 \
--dbname odoo \
--user postgres \
--password cool
This will generate a long report, and it contains different types of statistics related to postgresql.
We will look at each section in more detail.
pg-healthcheck ¦ 127.0.0.1:5432 ¦ PG 18.4 (Ubuntu 18.4-1.pgdg22.04+1) ¦ single ¦ 2026-07-30 18:28:18 UTC
????????????????????????????????????????????????????????????????
The first section of this report mainly shows the postgres version related details.
CONNECTION & AVAILABILITY
--------------------------------------------------------------
? G01-005 PostgreSQL version EOL PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg22.04+1) (major 18)
The second section mainly shows the availability of an open-source backup and recovery tool named pg_backrest.
PGBACKREST CONFIGURATION & WAL ARCHIVING
--------------------------------------------------------------
? G02-000 pgBackRest not detected pgBackRest config not found at /etc/pgbackrest/pgbackrest.conf --...
This section mainly covers the postgres configuration parameters related to performance.
PERFORMANCE PARAMETERS
--------------------------------------------------------------
? G03-010 random_page_cost random_page_cost = 4.0
? G03-003 maintenance_work_mem maintenance_work_mem = 64MB
? G03-004 effective_cache_size effective_cache_size = 4096MB (524288 pages)
? G03-008 Skipped -- Requested checkpoint ratio ERROR: column "checkpoints_req" does not exist (SQLSTATE 42703)
? G03-009 wal_compression wal_compression = off
? G03-012 Skipped -- JIT overhead pg_stat_statements not available: ERROR: relation "pg_stat_state...
? G03-014 default_statistics_target default_statistics_target = 100
? G03-017 track_io_timing track_io_timing = off
This section contains the Postgres configuration parameters related to long-running queries and locks. And also, it checks the availability of an extension in postgres named pg_stat_statements. This extension is mainly used to track the long-running queries in postgresql.
LONG-RUNNING QUERIES & LOCK CONTENTION
--------------------------------------------------------------
? G04-006 idle_in_transaction_session_timeout idle_in_transaction_session_timeout = 0 (disabled)
? G04-007 pg_stat_statements extension pg_stat_statements is not installed
? G04-010 lock_timeout lock_timeout = 0 (disabled)
? G04-005 statement_timeout statement_timeout = 0 (disabled)
? G04-008 Skipped -- Top queries by total_exec_t... pg_stat_statements not available: ERROR: relation "pg_stat_state...
? G04-009 log_min_duration_statement log_min_duration_statement = -1 (disabled)
? G04-011 Skipped -- Slow query count pg_stat_statements not available: ERROR: relation "pg_stat_state...
Details related to vacuum and autovacuum.
VACUUM & AUTOVACUUM HEALTH
--------------------------------------------------------------
? G05-009 Table bloat estimate 9 table(s) may be significantly bloated
? G05-006 autovacuum_vacuum_scale_factor autovacuum_vacuum_scale_factor = 0.200
Details related to index health and its usage are also provided. It included the count of duplicate indexes, foreign keys without indexes, and unused indexes like things.
INDEX HEALTH
--------------------------------------------------------------
? G06-002 Duplicate indexes 3 set(s) of duplicate indexes
? G06-005 FK columns without index 20 FK column set(s) missing a supporting index
? G06-010 Tables without primary key 1 table(s) have no primary key
? G06-001 Unused indexes 20 unused index(es) wasting 712MB
? G06-006 Prefix-redundant indexes 5 potentially prefix-redundant index pair(s)
? G06-009 Statistics reset date Oldest index stats from: 2026-07-17 14:52:13.619224+05:30
This section mainly shows the availability of the amcheck extension and the pg_check_relation catalogue.In postgres version 18, this catalogue is not available. And also, it checks the availability of the extension named amcheck, and this extension is mainly used to check the internal consistency of indexes like btree.
TOAST TABLE & CORRUPTION DETECTION
--------------------------------------------------------------
? G07-007 Skipped -- amcheck index verification amcheck extension not installed; run: CREATE EXTENSION amcheck
? G07-009 pg_check_relation availability PostgreSQL 18 -- pg_check_relation not found in pg_catalog
Details related to the visibility map in postgres like high-read operation-occurred tables and the availability of the pg_visibility extension.
VISIBILITY MAP INTEGRITY
--------------------------------------------------------------
? G08-001 High heap_blks_read 1 table(s) with high heap block reads relative to index scans
? G08-003 Post-crash visibility map advisory Cluster is not in recovery mode
? G08-004 pg_visibility extension pg_visibility extension is not installed
? G08-006 Skipped -- VM integrity (pg_visibility) pg_visibility extension not installed -- run: CREATE EXTENSION pg...
Details related to the replication and WAL.
WAL & REPLICATION SLOT HEALTH
--------------------------------------------------------------
? G09-010 max_slot_wal_keep_size max_slot_wal_keep_size = -1 (unlimited)
? G09-008 WAL archiving cross-reference See G02-009 for WAL .ready file backlog and G02-011 for pg_stat_...
Details related to the postgres upgrade.
PG_UPGRADE READINESS
--------------------------------------------------------------
? G10-000 pg_upgrade readiness target_version not configured
Details related to the security in postgres.
SECURITY POSTURE
--------------------------------------------------------------
? G11-003 Public schema CREATE privilege PUBLIC role has CREATE privilege on the public schema
? G11-009 Superuser login count 4 superuser login role(s): averigouser, cybrosys, odoo, postgres
? G11-006 pgaudit extension pgaudit is not installed
? G11-007 Stale login accounts 0 non-superuser login role(s) exist
? G11-008 SSL certificate paths ssl_cert_file="/etc/ssl/certs/ssl-cert-snakeoil.pem", ssl_key_fi...
Postgres parameters related to the os and also the resource level metadata.
OS & RESOURCE-LEVEL CHECKS
--------------------------------------------------------------
? G13-002 pg_stat_io evictions Total client backend evictions: 4483140
? G13-003 maxwritten_clean maxwritten_clean = 2925
? G13-004 huge_pages setting huge_pages = try
? G13-009 CPU frequency governor CPU scaling governor: powersave
? G13-001 Skipped -- Checkpoint sync time ERROR: column "checkpoint_sync_time" does not exist (SQLSTATE 42...
? G13-011 Postmaster uptime PostgreSQL up for 9.0 hours (started 2026-07-30 03:58:16 UTC)
Details related to the wal generation.
WAL GROWTH & GENERATION RATE
--------------------------------------------------------------
? G14-004 WAL statistics summary 38.6 GB WAL | 265744895 records | 2382253 FPI | 3379098 buf_full...
? G14-003 WAL rate vs rolling baseline Collecting baseline -- will compare once 2+ samples are stored
? G14-006 Top WAL-generating tables public.sale_order_old 5952065 modi...
? G14-007 WAL compression wal_compression = off
? G14-010 WAL archiver status Archiving not yet started or archive_mode=off
? G14-012 Skipped -- Forced checkpoint rate ERROR: column "checkpoints_req" does not exist (SQLSTATE 42703)
? G14-013 pg_wal filesystem usage pg_wal filesystem check: permission denied (path: /var/lib/postg...
Checking the availability of the logical multi master replication extension named Spock.
PGEDGE / SPOCK CLUSTER
--------------------------------------------------------------
? G12-000 Spock extension not detected The Spock extension is not installed on any of the connected nod...
Final summary of the postgres health check report.
????????????????????????????????????????????????????????????????
SUMMARY
--------------------------------------------------------------
? OK 81 ? INFO 37 ? WARN 16 ? CRITICAL 0 (total: 134)
? 16 WARN finding(s) should be fixed before the next incident window.
pg-healthcheck makes it easy to review the overall condition of a PostgreSQL server from a single command. Instead of manually checking configuration parameters, indexes, vacuum activity, WAL generation, security settings, and other database components, the tool gathers everything into a structured report. Regularly running these checks helps detect potential issues before they become production problems and provides a clear starting point for improving the stability and performance of your PostgreSQL environment.