pg-admin — PostgreSQL Production Admin Scripts

A suite of 27 scripts for day-to-day PostgreSQL + PgBouncer administration in production environments. All scripts share a single .env file for credentials so you only configure your connection once.

---

Quick Start

# 1. Copy and edit the environment file
cp .env  /path/to/your/working/dir/.env
vim /path/to/your/working/dir/.env

# 2. Run any script from that working directory
cd /path/to/your/working/dir
/path/to/pg-admin/25-health-check.sh

Every script sources .env from $PWD (the directory you cd into before running), so you can maintain separate .env files for different clusters (staging, prod-eu, prod-us, etc.) and switch between them just by changing directory.

---

Environment File (.env)

Variable	Description
PG_HOST	PostgreSQL host (IP or hostname)
PG_PORT	PostgreSQL port (default 5432)
PG_USER	Admin/superuser login
PG_PASSWORD	Password
PG_DATABASE	Default database for admin queries
PGB_HOST	PgBouncer host (defaults to PG_HOST)
PGB_PORT	PgBouncer port (default 6432)
PGB_USER	PgBouncer admin user
PGB_PASSWORD	PgBouncer admin password
PGB_ADMIN_DATABASE	PgBouncer admin db (default pgbouncer)
DEFAULT_SCHEMA	Schema used when listing tables (default public)
SLOW_QUERY_MS	Slow query threshold in ms (default 1000)
LONG_RUNNING_SEC	Long-running session threshold in s (default 30)

---

Script Reference

Inspection Scripts

Script	Description	Arguments
01-list-databases.sh	All databases with sizes, owners, encodings, connection counts	—
02-list-tables.sh	Tables in a schema with sizes, row estimates, vacuum history	[db] [schema]
03-db-sizes.sh	Database sizes with % of cluster total	—
04-table-sizes.sh	Top N tables: heap vs index vs TOAST breakdown	[db] [limit]
05-active-connections.sh	Connections by state, app, user; capacity check	—
06-running-queries.sh	All active queries with duration and wait event	[min_secs]
07-long-running-queries.sh	Queries and idle-in-transaction sessions over threshold	[threshold_secs]
08-lock-monitor.sh	All locks: waiting, blocking chains, lock summary	—
09-index-usage.sh	Index hit rates, per-index scan counts, unused/duplicate indexes	[db]
10-cache-hit-ratio.sh	Buffer cache hit rate for tables and indexes	[db]
11-vacuum-stats.sh	Dead tuple counts, autovacuum history, XID wraparound risk	[db]
12-replication-status.sh	WAL senders, replication slots, standby lag, WAL receiver	—
13-pgbouncer-stats.sh	Full PgBouncer snapshot: pools, stats, clients, servers	—
14-slow-queries.sh	Top slow/frequent queries from pg_stat_statements	[db] [top_n]
15-users-roles.sh	All roles, attributes, memberships, DB-level privileges	—
16-bloat-check.sh	Estimated table and index bloat (no extra extension needed)	[db]
17-schema-overview.sh	Object counts, schema sizes, FK map, views	[db] [schema]
18-wal-checkpoint-stats.sh	WAL position, checkpoint frequency, bgwriter metrics	—
19-missing-indexes.sh	High seq-scan tables, zero-index-scan tables, unused indexes	[db]
20-server-config.sh	Memory, WAL, planner, connection, logging settings	—
21-sequence-check.sh	Sequences with usage % and overflow risk warning	[db] [schema]
22-table-detail.sh	Deep dive on one table: columns, indexes, constraints, I/O	<table> [db] [schema]
23-extensions.sh	Installed extensions and available-but-not-installed list	[db]
24-partitions.sh	Partitioned tables, strategies, child counts and sizes	[db]
25-health-check.sh	One-stop ✔/✖ health summary — run this first	—
26-terminate-sessions.sh	Safely cancel/terminate sessions by PID, user, state, age	see below
27-io-stats.sh	Disk I/O per table/index — find hot spots	[db]

Watch Scripts (live, auto-refreshing)

Run from the watch/ subdirectory. All accept an optional [interval_seconds] argument.

Script	Description
watch/watch-connections.sh	Connection counts by state, app, capacity gauge
watch/watch-queries.sh	Live active query list ordered by duration
watch/watch-locks.sh	Lock waits and blocking chains — alerts red when >0 waits
watch/watch-pgbouncer.sh	PgBouncer pool utilisation and per-DB stats
watch/watch-replication.sh	Replication lag per standby + slot retained WAL
watch/watch-vacuum.sh	Running vacuum workers, progress %, tables with most dead rows

---

26-terminate-sessions.sh — Flags

--pid <pid>            Target a specific PID
--user <username>      All sessions for a user
--database <dbname>    All sessions on a database
--state <state>        Filter by pg_stat_activity.state
--app <app_name>       Filter by application_name
--older-than <secs>    Only sessions older than N seconds
--idle-txn             Shorthand for --state 'idle in transaction'
--dry-run              Preview what would be affected (safe)
--cancel               Use pg_cancel_backend (kills query only)  ← default
--terminate            Use pg_terminate_backend (drops connection)

Examples:

# Dry-run: what idle-in-transaction sessions are >5 min old?
./26-terminate-sessions.sh --idle-txn --older-than 300 --dry-run

# Cancel all queries from a specific user
./26-terminate-sessions.sh --user reporting_user --cancel

# Hard-terminate a specific PID
./26-terminate-sessions.sh --pid 98765 --terminate

---

Tips

• Start with 25-health-check.sh to get a quick summary of the cluster state.
• watch/watch-connections.sh + watch/watch-locks.sh side-by-side in two terminal panes gives excellent situational awareness during incidents.
• pg_stat_statements must be installed for 14-slow-queries.sh to work. Add shared_preload_libraries = 'pg_stat_statements' to postgresql.conf and run CREATE EXTENSION pg_stat_statements; in each database you want to profile.
• Scripts that accept a [database] argument default to PG_DATABASE from .env when omitted.
• Set NO_COLOR=1 in your environment to disable colour output (useful for log files or non-colour terminals).