The missing set of useful tools for Postgres DBA and mere mortals.
👉 See also postgres_ai, a comprehensive monitoring and optimization platform that includes automated health checks, SQL performance analysis, and much more.
Questions? Ideas? Contact me: [email protected], Nikolay Samokhvalov.
postgres_dba is based on useful queries created and improved by many developers. Here is incomplete list of them:
- Jehan-Guillaume (ioguix) de Rorthais https://github.com/ioguix/pgsql-bloat-estimation
- Alexey Lesovsky, Alexey Ermakov, Maxim Boguk, Ilya Kosmodemiansky et al. https://github.com/dataegret/pg-utils
- Josh Berkus, Quinn Weaver et al. from PostgreSQL Experts, Inc. https://github.com/pgexperts/pgx_scripts
You need to have psql version 10 or newer, but the Postgres server itself can be older – most tools work with it. You can install the latest postgresql-client library on your machine and use it to work with older Postgres servers – in this case postgres_dba will work. It's recommended to use psql from PostgreSQL 18 (the latest release) for the best compatibility.
On clean Ubuntu, this is how you can get postgresql-client and have the most recent psql:
sudo sh -c "echo \"deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main\" >> /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install -y postgresql-client-18On macOS, use Homebrew to install PostgreSQL client and pspg:
# Install PostgreSQL client (includes psql)
brew install libpq
# Add libpq to PATH (required because it's keg-only)
echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc
# For Intel Macs, use:
# echo 'export PATH="/usr/local/opt/libpq/bin:$PATH"' >> ~/.zshrc
# Verify installation
psql --version
# Install pspg (recommended pager)
brew install pspgAlternatively, you can install the full PostgreSQL package which includes psql:
brew install postgresql@18Using alternative psql pager called "pspg" is highly recommended but optional: https://github.com/okbob/pspg.
After installing pspg, configure it in your ~/.psqlrc:
\setenv PAGER pspg
\pset border 2
\pset linestyle unicodepostgres_dba is tested and supports PostgreSQL 13-18, including the latest PostgreSQL 18 release.
- ✅ PostgreSQL 13 - Fully supported
- ✅ PostgreSQL 14 - Fully supported
- ✅ PostgreSQL 15 - Fully supported
- ✅ PostgreSQL 16 - Fully supported
- ✅ PostgreSQL 17 - Fully supported (includes
pg_stat_checkpointercompatibility) - ✅ PostgreSQL 18 - Fully supported (latest release)
Older versions (9.6-12) may work but are not actively tested. Some reports may require specific PostgreSQL features introduced in newer versions.
The installation is trivial. Clone the repository and put "dba" alias to your .psqlrc file (works in bash, zsh, and csh):
git clone https://github.com/NikolayS/postgres_dba.git
cd postgres_dba
printf "%s %s %s %s\n" \\echo 🧐 🐘 'postgres_dba 18.0 installed. Use ":dba" to see menu' >> ~/.psqlrc
printf "%s %s %s %s\n" \\set dba \'\\\\i $(pwd)/start.psql\' >> ~/.psqlrcThat's it.
If you are running psql and Postgres server on the same machine, just launch psql:
psql -U <username> <dbname>And type :dba <Enter> in psql. (Or \i /path/to/postgres_dba/start.psql if you haven't added shortcut to your ~/.psqlrc file).
– it will open interactive menu.
What to do if you need to connect to a remote Postgres server? Usually, Postgres is behind a firewall and/or doesn't listen to a public network interface. So you need to be able to connect to the server using SSH. If you can do it, then just create SSH tunnel (assuming that Postgres listens to default port 5432 on that server:
ssh -fNTML 9432:localhost:5432 [email protected]Then, just launch psql, connecting to port 9432 at localhost:
psql -h localhost -p 9432 -U <username> <dbname>And type :dba <Enter> in psql to launch postgres_dba.
Just open psql as you usually do with Heroku:
heroku pg:psql -a <your_project_name>And then:
:dba
postgres_dba includes interactive tools for secure role (user) management:
- r1 – Create user with random password (interactive)
- r2 – Alter user with random password (interactive)
These tools help prevent password exposure in psql history, logs, and command-line process lists by:
- Generating secure random 16-character passwords
- Using interactive prompts instead of command-line arguments
- Only displaying the password once at creation/alteration time
Usage example:
-- In psql, after launching :dba
-- Select option r1 to create a new user
-- The script will prompt you for:
-- - Username
-- - Superuser privilege (yes/no)
-- - Login privilege (yes/no)
-- The generated password will be displayed once in the output
-- To see the password, set client_min_messages to DEBUG first:
set client_min_messages to DEBUG;Security note: These are DBA tools designed for trusted environments where the user already has superuser privileges. The password is shown in the psql output, so ensure you're working in a secure session.
You can add your own useful SQL queries and use them from the main menu. Just add your SQL code to ./sql directory. The filename should start with some 1 or 2-letter code, followed by underscore and some additional arbitrary words. Extension should be .sql. Example:
sql/f1_cool_query.sql
– this will give you an option "f1" in the main menu. The very first line in the file should be an SQL comment (starts with --) with the query description. It will automatically appear in the menu.
Once you added your queries, regenerate start.psql file:
/bin/bash ./init/generate.shNow your have the new start.psql and can use it as described above.
No steps are needed, just delete postgres_dba directory and remove \set dba ... in your ~/.psqlrc if you added it.