Connect to PostgreSQL with psql and SSL

State all five connection dimensions

psql -X \
  --host=db.example.com \
  --port=5432 \
  --username=app_reader \
  --dbname=commerce

Host, port, database, user, and TLS parameters identify the target together. A database name alone is insufficient; many instances can contain the same name.

Equivalent connection URI:

psql -X "postgresql://app_reader@db.example.com:5432/commerce?sslmode=verify-full"

Do not place passwords in command-line URIs, source code, or logs. Use an interactive prompt, secret manager, short-lived identity, .pgpass, or a libpq service file.

pgpass

The Unix default is ~/.pgpass, restricted to mode 0600:

hostname:5432:database:username:password

chmod 600 ~/.pgpass

The Windows default is %APPDATA%\postgresql\pgpass.conf. Wildcards broaden where a credential applies; prefer a specific host, database, and user.

Connection service file

A libpq service file keeps host, user, and TLS parameters out of shell history and command lines. The default path is ~/.pg_service.conf, overridable with PGSERVICEFILE:

[prod]
host=db.example.com
port=5432
user=app_reader
dbname=commerce
sslmode=verify-full

psql -X service=prod

Applications can reference the same entry through PGSERVICE=prod. Keep the password in .pgpass or a secret manager, not in the service file.

SSL modes

verify-full requires the URI host to match the certificate identity and a correct root certificate. Cloud providers can have CA rotation workflows; do not pin an expired certificate forever.

Confirm immediately after connecting

\conninfo
SELECT
  current_database(), current_user, session_user,
  inet_server_addr(), inet_server_port(),
  current_setting('server_version') AS server_version,
  current_setting('TimeZone') AS timezone;

For scripts:

psql -X --set ON_ERROR_STOP=on --file migration.sql "$DATABASE_URL"

-X prevents a user's .psqlrc from changing automation; ON_ERROR_STOP exits on SQL errors. See the PostgreSQL 18 psql documentation for exit-status semantics.

Machine-readable output formats

Interactive output is an aligned table; pipelines need unaligned, tuples-only results:

psql -X -A -F, -t -c "SELECT id, email FROM users" > users.csv

-A disables alignment, -F, sets the field separator, and -t prints tuples only. Inside a session, \pset format csv, \pset null '[NULL]', and \o /tmp/out.txt (then \o again to return to stdout) provide the same control.

Useful one-liners

Largest tables by total relation size:

psql -X -c "
SELECT schemaname||'.'||relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;"

Currently running queries, oldest first:

psql -X -c "
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start
LIMIT 20;"

Terminate a runaway backend after confirming the PID:

psql -X -c "SELECT pg_terminate_backend(12345);"

An interactive ~/.psqlrc

~/.psqlrc runs at every interactive startup; -X skips it, so automation is unaffected. A minimal template:

\timing on
\pset null '[NULL]'
\x auto
\pset linestyle unicode
\pset border 2

\set conns 'SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE state <> ''idle'';'
\set locks 'SELECT pid, mode, locktype, relation::regclass, granted FROM pg_locks WHERE NOT granted;'

\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups

:conns and :locks then expand to the stored queries, and per-database history files keep statements from one database out of another's prompt.

On failure, retain the full error and SQLSTATE, then use the error fieldbook. Do not troubleshoot by disabling TLS or broadening privileges.