Connecting to PostgreSQL
1. Connecting with psql Client
psql -h localhost -p 5432 -U alice -d shop
psql -W # force password prompt
psql "postgresql://alice:s3cret@db.example.com:5432/shop?sslmode=require"
| Flag | Purpose |
|---|
| -h | Host |
| -p | Port |
| -U | Username |
| -d | Database |
| -c "SQL" | Run single command |
| -f file.sql | Execute script |
| -X | Skip ~/.psqlrc |
2. Using Connection Strings
| Form | Example |
|---|
| URI | postgresql://user:pwd@host:5432/db?sslmode=require |
| Keyword/value | host=db user=alice dbname=shop sslmode=require |
| Multi-host | postgresql://host1,host2/db?target_session_attrs=read-write |
| UNIX socket | postgresql:///shop?host=/var/run/postgresql |
3. Setting Connection Parameters
| Parameter | Effect |
|---|
| connect_timeout | Seconds before giving up |
| application_name | Visible in pg_stat_activity |
| options | Per-connection GUCs, e.g. -c statement_timeout=5000 |
| target_session_attrs | any, read-write, read-only, primary, standby, prefer-standby |
| channel_binding | prefer / require / disable |
4. Using Password File
# ~/.pgpass (chmod 600)
# hostname:port:database:username:password
db.example.com:5432:shop:alice:S3cret!
*:*:*:postgres:adminpwd
Note: Must be 0600 permissions or PostgreSQL ignores it.
5. Setting psql Environment Variables
| Variable | Purpose |
|---|
| PSQL_HISTORY | History file path |
| PSQLRC | Override ~/.psqlrc |
| PSQL_EDITOR | Editor for \e |
| PSQL_PAGER | Pager command |
| PAGER | Fallback pager |
6. Connecting to Remote Server
psql -h db.prod.internal -p 5432 -U app -d orders \
"sslmode=verify-full sslrootcert=~/certs/ca.pem"
| Prereq | Setting |
|---|
| Server | listen_addresses='*' + firewall rule on 5432 |
| pg_hba.conf | host or hostssl rule for client CIDR |
| Auth | scram-sha-256 or cert |
7. Using SSL Connections
| sslmode | Behavior |
|---|
| disable | No SSL |
| allow | Try non-SSL, fall back to SSL |
| prefer | Try SSL first (default) |
| require | SSL mandatory, no cert verify |
| verify-ca | SSL + CA verified |
| verify-full | SSL + CA + hostname match (recommended) |
8. Checking Connection Status
\conninfo -- psql meta-command
SELECT current_user, current_database(),
inet_server_addr(), inet_server_port(),
version();
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity WHERE pid = pg_backend_pid();
9. Switching Databases
| Command | Effect |
|---|
| \c dbname | Reconnect to same host as same user |
| \c dbname user | Switch user too |
| \c dbname user host port | Full reconnect |
10. Disconnecting from Server
| Method | Command |
|---|
| psql exit | \q or Ctrl-D |
| Server-side terminate | SELECT pg_terminate_backend(pid); |
| Cancel query only | SELECT pg_cancel_backend(pid); |