postgres-psql-for-dba

PostgreSQL psqlrc settings + DBA toolkit for daily tasks, universal across PostgreSQL versions (queries auto-switch between legacy and modern when the server version differs, e.g. pg_stat_activity.waiting on <13 vs wait_event_type/wait_event on modern).

Install

git clone https://github.com/gsusrafael/postgres-psql-for-dba.git
cd postgres-psql-for-dba
bash psql-toolkit-installer.sh

The installer is idempotent, backs up any previous install, and auto-selects the pager: pspg if available, otherwise less with the built-in LESS settings.

Uninstall (restores a previous backup):

bash psql-toolkit-installer.sh --uninstall

Note: start psql normally. Using psql -X skips ~/.psqlrc.

Usage

~/.psqlrc loads the toolkit and a navigable menu with arrow keys (whiptail/dialog TUI; falls back to numbered select if neither is installed):

Navigate with arrows + Enter. 0 exits.
 1 Server settings     10 Unused indexes
 2 Connections         11 Table bloat
 ...

Tip: apt install whiptail (or dialog) to get arrow-key navigation. You can also run any command directly as a psql variable:

Command	Description
:settings	Server settings (pg_settings)
:conninfo	Connections by user
:activity	Server activity
:waits	Waiting queries (version-aware)
:slowq	Slow queries (top 100, requires pg_stat_statements)
:dbsize / :tablesize	Database / table sizes
:locks	Blocked queries (version-aware)
:pg_blocking	Blocking queries (version-aware)
:long_running_queries	Queries > 5 min
:unused_indexes	Unused / near-unused indexes
:pg_bloat / :dead_tuples_monitor	Table & index bloat
:pg_cache_hit / :db_cache_hit / :cache_hit_tables	Cache hit ratios
:pg_index_usage / :pg_index_size	Index usage / sizes
:pg_vacuum_stats	Vacuum status
:pg_near_tx_wrap	Tables near transaction-id wraparound
:duplicate_indexes	Duplicate index detection
:progress_vacuum	In-progress vacuum/analyze (PG12+)
:db_stats	Per-database statistics summary
:ps	Active queries with execution time
:cache_hit_explain	EXPLAIN (ANALYZE, BUFFERS) prefix

Prerequisites

• pg_stat_statements for :slowq.
• pg_buffercache for :cache_stat.
• pgfincore for :cache_stat (OS page cache info) — optional; the query fails if the extension is absent.
• pspg (optional) for the nicer pager — auto-detected by the installer.

Compatibility

• Version-aware queries: :waits, :locks, :pg_blocking, :progress_vacuum.
• Tested targets: PostgreSQL 9.6–16 (including Aurora PostgreSQL 16).
• psql's \if has no expression evaluation — it only accepts boolean literals. Version checks are computed server-side first via the GUC: SELECT current_setting('server_version_num')::int >= 160000 AS modern \gset, then \if :modern.

Extending

New toolkit command recipe (see dba_toolkit.pgsql):

SELECT $$
 -- your query here
$$ my_command \gset

Other tools worth pairing

• pspg — paged/colored psql output (already auto-used when present).
• pg_activity (pg_activity CLI) — top-like activity monitor.
• pgBadger — log analyzer for periodic reports.
• pg_stat_statements + Grafana — slow-query dashboards.
• pg_repack — bloat remediation (online).
• HypoPG — hypothetical index testing (:hypo candidates).