postgres_dba turns psql into a diagnostic console with 34 reports

Thirty-four reports, one menu, no daemon

postgres_dba describes itself as the missing set of useful tools for Postgres DBAs and all engineers, and the README puts the pitch in one line. 34 diagnostic reports for PostgreSQL, right inside psql. No agents, no daemons, no external dependencies, just SQL.

Then the instruction, which is the whole user experience. Type:dba and go.

That is a genuinely different approach to the category. The usual Postgres monitoring tooling is a daemon that connects to the server, collects metrics over time and stores them, and you query its database. This project inverts that, the diagnostics are SQL that you run when you need them, against the server you are already connected to, and the output is the answer rather than a series of samples you then have to interpret.

The practical consequence is that there is nothing to install on the server. The setup is a clone and a line in your.psqlrc.

git clone https://github.com/NikolayS/postgres_dba.git
cd postgres_dba
bash -c "echo \"\\set dba '\\\\i $(pwd)/start.psql'\" >> ~/.psqlrc"

So the:dba command is a psql meta-command that includes a start script, and the whole set lives in your home directory rather than in the database.

The version requirements are stated carefully, and the distinction between client and server is the one people get wrong. Requires psql 10+. The server can be any version. For best results, use the latest psql client.

That is worth internalising, since the compatibility statement is about the client, not about the server. The server side is covered separately, with the README claiming it is tested on PostgreSQL 13 through 18 via CI on every commit, and noting that older versions from 9.6 to 12 may work but are not actively tested.

The report IDs are a taxonomy

The report table is organised by area, and the ID prefix is the area. That is a design decision worth noting, because it means the menu is not just a list but a namespace.

General, 0 to 3. Node info covering primary or replica, replication lag, database size, temp files, WAL and replication slots. Database sizes and stats. Table and index sizes with row counts. Load profile.

Activity and locks. a1 for current connections grouped by database, user and state. l1 for lock trees, lightweight. l2 for lock trees with wait times, which needs PG14 and above because it uses pg_locks.waitstart.

Bloat, b1 to b5. Table bloat estimation, B-tree index bloat estimation, the same two via pgstattuple which is marked expensive, and b5 for tables without statistics, where the README explains that bloat cannot be estimated.

Memory, m1 for the buffer cache contents via pg_buffercache, also marked expensive.

Indexes, i1 to i5. Unused and rarely used indexes, redundant indexes, foreign keys with missing indexes, invalid indexes, and an index cleanup DDL generator that produces both DO and UNDO.

Vacuum, v1 and v2, for current activity and for autovacuum progress and queue.

Progress, p1, for CREATE INDEX and REINDEX progress.

Statements, s1 to s3, backed by pg_stat_statements. Slowest queries by total time, a full query performance report, and a workload profile by query type.

Tuning and config, t1 and t2 for parameters and objects with custom storage parameters, e1 for installed extensions, x1 for alignment padding analysis which is marked experimental, and r1 and r2 for creating and altering a user with a random password.

The last pair is the one with a security angle, since generating a random password inside the database rather than at a workstation is a reasonable way to avoid a password ever landing in a shell history or a password manager export.

The corruption checks are graded by the lock they take

The amcheck reports are the most interesting group in the list, because each one is annotated with the lock level it acquires, and the grading is explicit about which ones are safe.

| ID | Lock | Report | |----|------|--------| | c1 | AccessShareLock | Quick index check: btree + GIN (PG18+). Safe for production. | | c2 | AccessShareLock | Indexes + heap/TOAST (PG14+). Safe but reads all data. | | c3 | ShareLock | B-tree parent check, detects glibc/collation corruption. Use on clones. | | c4 | ShareLock | Full: heapallindexed + parent + heap. Proves every tuple is indexed. |

Two ideas are at work here. The first is that a check that verifies every tuple is indexed is a stronger statement than a check that verifies the index structure is intact, and c4 is the one that proves it. The second is that c3 targets a specific and nasty failure, glibc or collation corruption, which is the kind of bug that produces subtly wrong query results rather than an error, and the README's instruction to use it on clones is a real operational constraint.

The lock annotation is the useful part for an operator. AccessShareLock is what a plain SELECT takes, so c1 and c2 do not block normal traffic. ShareLock blocks writes, which is why c3 and c4 are marked for clones. The README also states the permission requirement, that the tool works with the pg_monitor role and superuser is not required for most reports, with the corruption checks needing superuser or an explicit GRANT EXECUTE.

So the design separates three concerns that a monitoring tool would conflate. What you can safely run on production, what you can run only on a copy, and what you need elevated rights for. Each of the 34 reports carries that information, which is a better contract than a single advisory note about locking at the top of the documentation.

Extensions are optional and the README says which reports need them

The Optional Extensions table is short and it is the key to the whole design, because it explains why some reports are marked expensive and some are not.

| Extension | Reports | Install | |-----------|---------|---------| | `pg_stat_statements` | s1, s2, s3 | `shared_preload_libraries = 'pg_stat_statements'` | | `amcheck` | c1, c2, c3, c4 | `CREATE EXTENSION amcheck;` | | `pgstattuple` | b3, b4 | `CREATE EXTENSION pgstattuple;` | | `pg_buffercache` | m1 | `CREATE EXTENSION pg_buffercache;` |

Each of these is a standard PostgreSQL extension, and the install instructions are given as they would be typed. Only pg_stat_statements needs a server restart to enable, because it is a shared preload library, and the others are a plain CREATE EXTENSION in the database.

That is the whole dependency story, and it explains the design claim. The reports that need no extension are written against the system catalogs, which every PostgreSQL server has, and the reports that need an extension are the ones that genuinely require more than the catalogs, bloat estimation via the real tuple statistics, the buffer cache contents, the index consistency checks and the statement statistics.

The bloat reports make the distinction visible. b1 and b2 are estimations that work from the catalog statistics, and b3 and b4 use pgstattuple for the real numbers, and are marked expensive because pgstattuple reads the whole relation. So a bloat report is available either way, with different cost and different confidence.

The consequence for an operator is that a managed Postgres service where you cannot install extensions still gives you the majority of the toolkit, and the README says the reports that need amcheck need superuser or an explicit GRANT EXECUTE, which is a manageable ask in an environment where you already hold a monitoring role.

Adding a report is one file and one script

The extension mechanism is the part of the project that makes it a platform rather than a script collection, and it is unusually simple.

Drop a .sql file in sql/. The filename format is <id>_<name>.sql. The first line must be a -- comment with the description, and it becomes the menu entry automatically.

# Regenerate the menu after adding/removing reports
bash ./init/generate.sh

So a custom report is a SQL file with a naming convention and a first line comment, and a script regenerates the menu from the contents of the directory. There is no registration file to edit by hand and no manifest to keep in sync, which is the failure mode that every file-based plugin system eventually has.

The repository top level listing shows the structure behind this. There is a sql/ directory holding the reports, an init/ directory holding the generation script, a start.psql and a warmup.psql at the root, matviews/ for materialized views, misc/, roles/ for the role definitions, and test/ for the test suite, which is what the CI badge at the top of the README refers to. There is also a .cursor/ directory and a CLAUDE.md, so the project is developed with some AI tooling assistance in the loop.

The pspg recommendation is a nice practical touch. pspg makes tabular output much easier to read, and the way to use it is three psql settings.

\setenv PAGER pspg
\pset border 2
\pset linestyle unicode

So a report that produces a wide table is readable in a pager rather than a wall of pipe-separated text.

The credits section is also worth reading, because it explains the provenance. The project is built on diagnostic queries contributed by many people over the years, with Gilles Darold credited for the bloat estimation queries, Alexey Lesovsky, Maxim Boguk, Ilya Kosmodemiansky and Andrey Ermakov for the pg-utils diagnostic suite, and Josh Berkus, Greg Smith, Christophe Pettus and Quinn Weaver for the pgx_scripts collection. Those are the well known names in the Postgres tooling world, and the release history shows the project has been assembled over years, with 5.0 in 2019, 6.0 in 2020 and 7.0 on 2026-02-10.

Where postgres_dba is the wrong tool

The first wrong case is continuous monitoring. These are point-in-time reports. Nothing here samples, nothing stores a history, and nothing alerts. A team that needs to know that replication lag grew at 3am or that autovacuum fell behind needs a monitoring system, and a collection of excellent diagnostic SQL does not become one by being run by hand.

The second wrong case is a managed Postgres service. Several of the most useful reports need an extension, and a hosted service will often let you install some extensions and not others, and pg_stat_statements in particular needs the server restarted with it preloaded, which not every managed service allows. The README's extension table is the place to check, and if your provider blocks amcheck then the corruption checks go with it.

The third wrong case is a team that wants results in a dashboard or a report for an auditor. The output is psql text, which is exactly right for an engineer at a terminal and exactly wrong for a quarterly review.

The fourth wrong case is a production system where you will run the more thorough checks carelessly. The lock annotations are the safety mechanism, and they only work if someone reads them. c4 takes ShareLock and reads every tuple, which on a large table is a long transaction holding back writes, and the fact that it proves every tuple is indexed makes it tempting to run on the primary rather than the clone the README recommends.

The fifth wrong case is a very old server. Compatibility is claimed for PostgreSQL 13 through 18, and 9.6 to 12 may work but is not actively tested, and several reports depend on version-specific catalog changes and on features like pg_locks.waitstart, which is PG14 and later.

How it differs from installing a monitoring stack

The comparison worth making is with the usual approach, which is pgMustard, Datadog, pganalyze, the Percona tooling, or a self hosted Prometheus and Grafana, and the difference in approach is where the state lives and when you get the answer.

A monitoring system runs continuously. It samples on a schedule, it stores the samples, and it gives you a graph over time. That is exactly what you need for a trend, for a capacity question, for an alert, and for evidence after an incident. It is also a system to install, to configure, to secure, to pay for, and to keep running.

postgres_dba has none of that. There is nothing to run, nothing to store, and no history. In exchange, the answer you get is the current state, computed from the catalogs right now, and it is the same answer a senior engineer would have produced by hand with the same queries.

The second difference is the audience. A monitoring dashboard is built for a team and for a wall. This is built for one person at a terminal with a problem. The reports are phrased accordingly, node info with replication lag and WAL and slots in one screen, lock trees with wait times, autovacuum progress and queue, index progress for a long CREATE INDEX, and a query performance report from pg_stat_statements.

The third difference is the composition. A monitoring stack is configured with a schema, thresholds and dashboards, and that configuration is a maintenance burden of its own. Here, adding a report is a SQL file in a directory, so a team that wants a check nobody else wrote drops in a file and regenerates the menu.

The fourth difference is what a monitoring system cannot easily do. The corruption checks and the DDL generators are one-shot operations against a specific state, and a monitoring stack has no concept of them. The index cleanup generator that produces both DO and UNDO is a good example, since it is a statement about a set of objects rather than a metric over time, and it belongs in a toolbox rather than in a dashboard.

Editorial conclusion

postgres_dba fits a DBA or an engineer who needs an answer about a live Postgres server in the next five minutes and does not want to install a monitoring stack, since the reports are plain SQL against the system catalogs and the extensions are optional and well known. It does not fit a team that needs continuous monitoring, alerting or a history of metrics, and it does not fit a managed Postgres service where the extensions cannot be installed. Before using it, check the lock level of the corruption checks against the impact of the ones you intend to run, because c3 and c4 take ShareLock, and note that the newest release is 7.0 from 2026-02-10 with the last push on 2026-08-18, and that compatibility is claimed for PostgreSQL 13 through 18 with the server version and the psql client version being separate requirements.

Frequently asked questions

Official sources

1. Issues
2. License: BSD-3-Clause
3. NikolayS/postgres_dba on GitHub
4. README
5. Releases