I needed to connect to a PostgreSQL database for debugging production issues. After years of copying connection commands from Slack, I finally wrote down the patterns I actually use.

Connection methods Link to heading

Connect to localhost:

psql -h localhost -U myuser -d mydb

Connect to a specific port:

psql -h localhost -p 5432 -U myuser -d mydb

Connect via Cloud SQL proxy (commonly used in GCP environments):

psql -h 127.0.0.1 -p 15432 -U myuser mydb

Connect with password in env var (safer than having it in your shell history):

export PGPASSWORD=mypassword
psql -h localhost -U myuser -d mydb

Or use a connection string (my preferred method when scripting):

psql "postgresql://myuser:mypassword@localhost:5432/mydb"

My .psqlrc configuration Link to heading

I keep a .psqlrc file in my home directory to make psql more usable. Here’s what I have:

-- Show query timing
\timing on

-- Better null display
\pset null '∅'

-- Expanded display for wide tables
\x auto

-- Better prompt showing database and transaction status
\set PROMPT1 '%n@%/%R%x%# '

-- Save history to a file per database
\set HISTFILE ~/.psql_history- :DBNAME

-- Ignore duplicate commands in history
\set HISTCONTROL ignoredups

-- Autocomplete keywords in uppercase
\set COMP_KEYWORD_CASE upper

The \x auto setting is brilliant - it automatically switches to expanded display when results are too wide. Saves me from manually typing \x all the time.

Essential psql shortcuts Link to heading

Once connected, these are the most useful shortcuts:

-- List databases
\l

-- List tables (I probably type this 50 times a day)
\dt

-- Describe a table (shows columns, types, indexes)
\d tablename

-- List all schemas
\dn

-- List functions
\df

-- Show table sizes
\dt+

-- List indexes on a table
\di tablename

-- Quit
\q

The + modifier is incredibly useful - it shows additional information like size, description, etc. I wish I’d discovered \dt+ earlier.

Non-interactive usage Link to heading

Run a single command (great for scripts):

psql -h localhost -U myuser -d mydb -c "SELECT count(*) FROM users"

Run a SQL file:

psql -h localhost -U myuser -d mydb -f script.sql

The -c flag is particularly useful in CI pipelines for quick health checks.

Connection troubleshooting Link to heading

The error message I see most often is FATAL: password authentication failed. Usually it’s because:

  1. Wrong username (check with \du if you can connect as superuser)
  2. Password stored in the wrong PGPASSWORD variable
  3. Wrong database name (use \l to list all databases)
  4. Connection coming from wrong IP (check pg_hba.conf)

For Cloud SQL specifically, make sure your proxy is actually running - I can’t count how many times I’ve forgotten to start it and spent 10 minutes debugging “connection refused”.

Further reading Link to heading

The official psql documentation is comprehensive if a bit dry. For more practical tips, I recommend: