Tools Basics

10 examples for building a DBA toolkit with pinned versions on PostgreSQL 18.4 clusters - backup, log analysis, bloat control, and advisory tools your team can reproduce on laptop, staging, and production bastion.

Prerequisites

SSH or kubectl exec access to a host that can reach Postgres (bastion or debug pod).
Client binaries installed at versions documented in your repo.
Read-only production access policy for juniors; write tools gated to on-call.

# tools/versions.env (commit this file)
PG_VERSION=18.4
PGBACKREST_VERSION=2.55.0
PGBADGER_VERSION=12.4
PG_REPACK_VERSION=1.5.2
PSQL_CLIENT=18.4

Scope: This section covers operational tools, not application drivers. See psql Basics for interactive client workflows.

Basic Examples

1. Minimum Toolkit by Role

Role	Tool	Purpose
Backup / PITR	pgBackRest	Physical base backup + WAL archive
Slow query discovery	pgbadger	Parse `log_min_duration_statement` CSV logs
Bloat control	pg_repack	Online heap/index repack without long ACCESS EXCLUSIVE
Plan review	`psql` + `EXPLAIN (ANALYZE, BUFFERS)`	Human-verified query plans
Advisory (optional)	pganalyze / OtterTune	Index and knob suggestions - verify manually

One tool per concern; avoid overlapping backup agents on the same cluster
Managed RDS/Aurora: use native backup APIs; pgBackRest applies to self-hosted and some VM images

2. Verify Client Versions Match Server

psql "$DATABASE_URL" -c "SHOW server_version;"
psql --version
pgbackrest version
pgbadger --version

-- In psql
SELECT version();
SHOW server_version_num;  -- 180004 for 18.4

Client major must match server major for pg_dump/pg_restore compatibility
Minor drift (18.3 client, 18.4 server) is usually fine for psql queries

3. Install Pattern on Linux Bastion

#!/usr/bin/env bash
set -euo pipefail
source tools/versions.env
 
# Example: pgBackRest from package or source pin
sudo apt-get install -y "pgbackrest=${PGBACKREST_VERSION}*"
 
# pgbadger (Perl)
sudo apt-get install -y pgbadger
 
# pg_repack extension + CLI (cluster must CREATE EXTENSION)
sudo apt-get install -y "postgresql-${PG_VERSION%%.*}-repack"

Document install commands in docs/runbooks/tooling.md
Container images: bake versions in Dockerfile; never apt install without pin on prod hosts

4. Tool Access Matrix

| Tool        | Dev laptop | Staging bastion | Prod bastion | CI |
|-------------|------------|-----------------|--------------|-----|
| psql        | yes        | yes             | read-only    | migrate job |
| pgBackRest  | no         | yes (restore drill) | on-call only | backup verify |
| pgbadger    | yes (sample logs) | yes      | yes (read logs) | no |
| pg_repack   | local only | yes (change window) | on-call + ticket | no |

Prod write tools require change ticket and pairing for first-time operators
CI runs migration lint and pgbackrest check against restore sandbox, not prod

5. Log Shipping for pgbadger

-- postgresql.conf (staging/prod)
ALTER SYSTEM SET log_destination = 'csvlog';
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %u@%d ';
SELECT pg_reload_conf();

# Nightly report on bastion
pgbadger /var/log/postgresql/postgresql-*.csv \
  -o /var/reports/pgbadger/$(date +%F) \
  --incremental

pgbadger needs CSV or standard log format with duration lines
Ship reports to object storage; link slow queries to tickets

Related: pgbadger

6. Backup Tool Boundary

# Self-hosted: pgBackRest stanza
pgbackrest --stanza=main info
pgbackrest --stanza=main check

# RDS: use aws rds describe-db-snapshots (not pgBackRest)
aws rds describe-db-snapshots --db-instance-identifier myapp-prod

Pick one physical backup system per cluster
pgBackRest stanza name matches pg1 Patroni role in HA topologies

Related: pgBackRest, PITR Basics

7. Extension Tools Need DBA Approval

-- pg_repack requires extension on each database
CREATE EXTENSION IF NOT EXISTS pg_repack;

CREATE EXTENSION is a governance event - track in extension allowlist ADR
pg_repack version must match server major (18.x extension for PG 18)

Intermediate Examples

8. Monorepo tools/ Directory Layout

tools/
├── versions.env
├── bin/
│   ├── pg-health.sh
│   ├── run-pgbadger.sh
│   └── verify-backup.sh
└── README.md

#!/usr/bin/env bash
# tools/bin/pg-health.sh
set -euo pipefail
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "
SELECT datname, numbackends, xact_commit, blks_hit::float / NULLIF(blks_hit + blks_read, 0) AS cache_hit
FROM pg_stat_database
WHERE datname = current_database();"

Scripts use set -euo pipefail and ON_ERROR_STOP=1
Same script runs locally against Docker Postgres and in staging smoke

9. Quarterly Tool Audit Checklist

- [ ] versions.env matches production server_version
- [ ] pgbackrest check exit 0 on all stanzas
- [ ] pgbadger report generated last 7 days
- [ ] pg_repack extension present only where approved
- [ ] No duplicate backup cron (pg_basebackup AND pgBackRest)
- [ ] Bastion packages match versions.env

Attach audit results to governance ticket
Upgrade tools after Postgres minor upgrade validation window

10. When Not to Add a Tool

Situation	Skip new tool	Use instead
Managed Neon/Supabase hobby	pgBackRest	Provider PITR UI
Single small dev DB	pganalyze subscription	`EXPLAIN` in CI
Table under 100 MB bloat	pg_repack	`VACUUM (FULL)` in maintenance window

FAQs

Do app developers need pgBackRest installed?

No. Developers need psql, migration runner, and EXPLAIN habits. Backup tools stay on bastion and CI restore jobs.

Can pgbadger replace pg_stat_statements?

No. pg_stat_statements gives live aggregates; pgbadger analyzes historical log files. Use both.

How often repin tool versions?

After every Postgres minor upgrade and quarterly security patch cycle. Update tools/versions.env in the same PR as runbook changes.

pgBackRest - physical backup and PITR
pgbadger - slow query log reports
pg_repack - online bloat reclamation
Essential Tools Best Practices - 25-item team summary
Extensions Basics - extension install policy

Stack versions: This page was written for PostgreSQL 18.4, pgBackRest 2.55+, pgBadger 12+, pg_repack 1.5+, and psql client 18.4.

Tools Basics

Prerequisites

SSH or kubectl exec access to a host that can reach Postgres (bastion or debug pod).
Client binaries installed at versions documented in your repo.
Read-only production access policy for juniors; write tools gated to on-call.

# tools/versions.env (commit this file)
PG_VERSION=18.4
PGBACKREST_VERSION=2.55.0
PGBADGER_VERSION=12.4
PG_REPACK_VERSION=1.5.2
PSQL_CLIENT=18.4

Scope: This section covers operational tools, not application drivers. See psql Basics for interactive client workflows.

Basic Examples

1. Minimum Toolkit by Role

Role	Tool	Purpose
Backup / PITR	pgBackRest	Physical base backup + WAL archive
Slow query discovery	pgbadger	Parse `log_min_duration_statement` CSV logs
Bloat control	pg_repack	Online heap/index repack without long ACCESS EXCLUSIVE
Plan review	`psql` + `EXPLAIN (ANALYZE, BUFFERS)`	Human-verified query plans
Advisory (optional)	pganalyze / OtterTune	Index and knob suggestions - verify manually

One tool per concern; avoid overlapping backup agents on the same cluster
Managed RDS/Aurora: use native backup APIs; pgBackRest applies to self-hosted and some VM images

2. Verify Client Versions Match Server

psql "$DATABASE_URL" -c "SHOW server_version;"
psql --version
pgbackrest version
pgbadger --version

-- In psql
SELECT version();
SHOW server_version_num;  -- 180004 for 18.4

Client major must match server major for pg_dump/pg_restore compatibility
Minor drift (18.3 client, 18.4 server) is usually fine for psql queries

3. Install Pattern on Linux Bastion

#!/usr/bin/env bash
set -euo pipefail
source tools/versions.env
 
# Example: pgBackRest from package or source pin
sudo apt-get install -y "pgbackrest=${PGBACKREST_VERSION}*"
 
# pgbadger (Perl)
sudo apt-get install -y pgbadger
 
# pg_repack extension + CLI (cluster must CREATE EXTENSION)
sudo apt-get install -y "postgresql-${PG_VERSION%%.*}-repack"

Document install commands in docs/runbooks/tooling.md
Container images: bake versions in Dockerfile; never apt install without pin on prod hosts

4. Tool Access Matrix

| Tool        | Dev laptop | Staging bastion | Prod bastion | CI |
|-------------|------------|-----------------|--------------|-----|
| psql        | yes        | yes             | read-only    | migrate job |
| pgBackRest  | no         | yes (restore drill) | on-call only | backup verify |
| pgbadger    | yes (sample logs) | yes      | yes (read logs) | no |
| pg_repack   | local only | yes (change window) | on-call + ticket | no |

Prod write tools require change ticket and pairing for first-time operators
CI runs migration lint and pgbackrest check against restore sandbox, not prod

5. Log Shipping for pgbadger

-- postgresql.conf (staging/prod)
ALTER SYSTEM SET log_destination = 'csvlog';
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %u@%d ';
SELECT pg_reload_conf();

# Nightly report on bastion
pgbadger /var/log/postgresql/postgresql-*.csv \
  -o /var/reports/pgbadger/$(date +%F) \
  --incremental

pgbadger needs CSV or standard log format with duration lines
Ship reports to object storage; link slow queries to tickets

Related: pgbadger

6. Backup Tool Boundary

# Self-hosted: pgBackRest stanza
pgbackrest --stanza=main info
pgbackrest --stanza=main check

# RDS: use aws rds describe-db-snapshots (not pgBackRest)
aws rds describe-db-snapshots --db-instance-identifier myapp-prod

Pick one physical backup system per cluster
pgBackRest stanza name matches pg1 Patroni role in HA topologies

Related: pgBackRest, PITR Basics

7. Extension Tools Need DBA Approval

-- pg_repack requires extension on each database
CREATE EXTENSION IF NOT EXISTS pg_repack;

CREATE EXTENSION is a governance event - track in extension allowlist ADR
pg_repack version must match server major (18.x extension for PG 18)

Intermediate Examples

8. Monorepo tools/ Directory Layout

tools/
├── versions.env
├── bin/
│   ├── pg-health.sh
│   ├── run-pgbadger.sh
│   └── verify-backup.sh
└── README.md

#!/usr/bin/env bash
# tools/bin/pg-health.sh
set -euo pipefail
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "
SELECT datname, numbackends, xact_commit, blks_hit::float / NULLIF(blks_hit + blks_read, 0) AS cache_hit
FROM pg_stat_database
WHERE datname = current_database();"

Scripts use set -euo pipefail and ON_ERROR_STOP=1
Same script runs locally against Docker Postgres and in staging smoke

9. Quarterly Tool Audit Checklist

- [ ] versions.env matches production server_version
- [ ] pgbackrest check exit 0 on all stanzas
- [ ] pgbadger report generated last 7 days
- [ ] pg_repack extension present only where approved
- [ ] No duplicate backup cron (pg_basebackup AND pgBackRest)
- [ ] Bastion packages match versions.env

Attach audit results to governance ticket
Upgrade tools after Postgres minor upgrade validation window

10. When Not to Add a Tool

Situation	Skip new tool	Use instead
Managed Neon/Supabase hobby	pgBackRest	Provider PITR UI
Single small dev DB	pganalyze subscription	`EXPLAIN` in CI
Table under 100 MB bloat	pg_repack	`VACUUM (FULL)` in maintenance window

FAQs

Do app developers need pgBackRest installed?

No. Developers need psql, migration runner, and EXPLAIN habits. Backup tools stay on bastion and CI restore jobs.

Can pgbadger replace pg_stat_statements?

No. pg_stat_statements gives live aggregates; pgbadger analyzes historical log files. Use both.

How often repin tool versions?

After every Postgres minor upgrade and quarterly security patch cycle. Update tools/versions.env in the same PR as runbook changes.

pgBackRest - physical backup and PITR
pgbadger - slow query log reports
pg_repack - online bloat reclamation
Essential Tools Best Practices - 25-item team summary
Extensions Basics - extension install policy

Stack versions: This page was written for PostgreSQL 18.4, pgBackRest 2.55+, pgBadger 12+, pg_repack 1.5+, and psql client 18.4.

Tools Basics

Search Documentation

Tools Basics