My PostgreSQL CLI setup – Negative Feedback
Skip to content
My PostgreSQL CLI setup
Written by
alchemyst
in
Uncategorized
Motivatation
My database of choice is PostgreSQL. I often find myself wanting to do quick ad-hoc queries against a set of well-defined databases. Maybe I just want to know the internal id of a customer from their name, or check on the status of a job.
If I’m doing real reporting and producing outputs I will usually spin up PyCharm. It’s got amazing db support, which is the same as you would get from JetBrain’s dedicated database product DataGrip. Great ergonomics, but Pycharm takes a while to get started and frankly the setup of all our dbs is onerous enough that I haven’t registered all of them. I also use pgAdmin, but that’s also tedious to set up "just right". And sometimes when you’re already in a terminal mindset, you don’t want to jump out to a GUI.
So I used to ssh into our host and fire up psql. I got laggy input and a bit of a startup-delay due to jumping through our bastion, but it worked well enough that I found myself staring at that no-frills psql prompt a lot.
But I kept thinking it could be better. I was right. After a few hours of ChatGPT and self-directed browsing and experimentation, I ended up assembling a surprisingly nice terminal-based workflow using:
PostgreSQL service configs
pgcli
pspg
duckdb
The result feels very “close-to-hand”: fast startup, pleasant interaction, integrated with the terminal environment, and has opened up a powerful cross-database workflow.
PostgreSQL Service Definitions
PostgreSQL already has a built-in mechanism for naming database connections: ~/.pg_service.conf
#~/.pg_service.conf<br>[prod-db1]<br>host=localhost<br>port=port1<br>dbname=prod_db1<br>user=readonly_prod_db1<br>sslmode=prefer
[dev-db1]<br>host=localhost<br>port=port2<br>dbname=dev_db1<br>user=readonly_dev_db1<br>sslmode=prefer
[prod-db2]<br>host=localhost<br>port=port3<br>dbname=prod_db2<br>user=readonly_prod_db2<br>sslmode=prefer#~/.pg_service.conf<br>[prod-db1]<br>host=localhost<br>port=port1<br>dbname=prod_db1<br>user=readonly_prod_db1<br>sslmode=prefer
[dev-db1]<br>host=localhost<br>port=port2<br>dbname=dev_db1<br>user=readonly_dev_db1<br>sslmode=prefer
[prod-db2]<br>host=localhost<br>port=port3<br>dbname=prod_db2<br>user=readonly_prod_db2<br>sslmode=prefer
This lets you connect using
psql service=prod-db1psql service=prod-db1
I’ve set up port forwarding for our dbs so that the different servers are reachable through different ports. The nice thing about a text-based system like this is you can create the entries programmatically, like if you have dev, qa and prod versions of all these dbs.
Password Management
PostgreSQL uses ~/.pgpass for password lookup.
Example:
#~/.pgpass<br>#host:port:dbname:username:password<br>localhost:port1:prod_db1:readonly_prod_db1:password1<br>localhost:port2:dev_db1:readonly_dev_db1:password2<br>localhost:port3:prod_db2:readonly_prod_db2:password3#~/.pgpass<br>#host:port:dbname:username:password<br>localhost:port1:prod_db1:readonly_prod_db1:password1<br>localhost:port2:dev_db1:readonly_dev_db1:password2<br>localhost:port3:prod_db2:readonly_prod_db2:password3
Postgres won’t read from this if you don’t set these permissions:
chmod 600 ~/.pgpasschmod 600 ~/.pgpass
Once configured, authentication becomes automatic. If it freaks you out to have passwords stored at rest like this, you can generate .pgpass on the fly and pass it through as an environment variable to a local terminal, too.
A Better Interactive Client: pgcli
psql is powerful and reliable, but pgcli makes interactive querying much more pleasant.
Install with Homebrew:
brew install pgclibrew install pgcli
Then:
pgcli service=prod-db1pgcli service=prod-db1
This gives you quite a nice interactive experience. As-you-type syntax highlighting, a visible autocomplete, even for those hard-to-remember backslash psql commands, great history searching:
I can’t say enough good things about pgcli (bonus: check out litecli which is the same thing for sqlite).
I also edit ~/.config/pgcli/config, which in the default homebrew install gets set to a nicely commented default config, to set table_format = fancygrid.
A Better Pager: pspg
Another huge win is having a better pager. Normally, large query results in psql are paged through less, which is not very pleasant for tabular data.
pspg is a terminal pager specifically designed for SQL tables.
Install using Homebrew:
brew install pspgbrew install pspg
Configure in ~/.psqlrc:
# ~/.psqlrc<br># Some other config I like<br>\pset linestyle unicode<br>\pset border 2<br>\pset null ∅
# The actual pager<br>\setenv PSQL_PAGER 'pspg'# ~/.psqlrc<br># Some other config I like<br>\pset linestyle unicode<br>\pset border 2<br>\pset null ∅
# The actual pager<br>\setenv PSQL_PAGER 'pspg'
This gives you a table-aware browser instead of a line-based pager. It freezes headers, allows scrolling and searching in straightforward ways and has a built-in save-to-csv feature (I can never remember the syntax for outputing to csv in postgres). pgcli also supports this by...