psql & Client Tools

psql: The Power Tool

psql is the interactive terminal for PostgreSQL. It ships with every Postgres installation and is the tool DBAs reach for first. GUIs have their place, but psql is where real work happens. It is scriptable, fast, and available on every server where Postgres runs.

Connecting

# Basic connection
psql -h localhost -U myuser -d mydb

# Connection string (URI format)
psql "postgresql://myuser:pass@db.example.com:5432/mydb?sslmode=require"

# Environment variables (avoid passwords on the command line)
export PGHOST=localhost
export PGUSER=myuser
export PGDATABASE=mydb
export PGPASSWORD=secret    # or use .pgpass file
psql

The .pgpass file is the proper way to store passwords for automated scripts:

# ~/.pgpass format: hostname:port:database:username:password
localhost:5432:mydb:myuser:secretpassword

chmod 600 ~/.pgpass

Postgres refuses to read .pgpass if the permissions are too open.

Essential Backslash Commands

Listing Objects

\l          List all databases
\l+         List databases with size and tablespace info
\dt         List tables in current schema
\dt+        List tables with size info
\dt myschema.*   List tables in a specific schema
\di         List indexes
\di+        List indexes with sizes
\ds         List sequences
\dv         List views
\df         List functions
\du         List roles (users)
\dn         List schemas

Describing Objects

\d users              Describe table: columns, types, indexes, constraints
\d+ users             Extended description: storage, stats, column comments
\d users_pkey         Describe an index

Example output:

mydb=# \d users
                                     Table "public.users"
   Column    |           Type           | Collation | Nullable |      Default
-------------+--------------------------+-----------+----------+-------------------
 id          | bigint                   |           | not null | generated always
 email       | text                     |           | not null |
 name        | text                     |           |          |
 created_at  | timestamp with time zone |           | not null | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE, btree (email)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

\c otherdb            Connect to a different database
\c otherdb otheruser  Connect as a different user
\conninfo             Show current connection info

Query Execution & Output

Timing

\timing on

SELECT count(*) FROM orders;

  count
---------
 1482937
(1 row)

Time: 142.385 ms

Always turn on timing when investigating performance. It becomes second nature.

Expanded Display

\x auto

When a result has many columns, expanded mode switches to vertical output automatically:

-[ RECORD 1 ]---+-----------------------------
id              | 1
email           | alice@example.com
name            | Alice Johnson
created_at      | 2024-03-15 09:22:11.432+00

This is far more readable than a 200-character-wide horizontal row.

Output Formatting

\pset format csv       Output results as CSV
\pset format html      Output results as HTML table
\pset format aligned   Default: aligned columns with borders
\pset null '(null)'    Display NULL values explicitly
\pset pager off        Disable the pager (useful in scripts)

Editing & History

Query Editing with \e

\e

Opens the last query in your $EDITOR (vim, nano, etc.). Write a complex query in a proper editor, save, and it executes on exit. This is invaluable for multi-line queries.

\e /tmp/my_query.sql

Opens a specific file for editing and executes its contents.

Input from Files

\i /path/to/script.sql       Execute a SQL file
\ir relative/path.sql         Execute relative to current script (useful for includes)

History

psql stores command history in ~/.psql_history. Use up/down arrows or Ctrl+R for reverse search, just like bash.

\s                    Show command history
\s /tmp/history.txt   Save history to file

Data Import & Export with \copy

\copy is a client-side command that reads/writes files on the client machine. It does not require superuser privileges, unlike the server-side COPY command.

Export to CSV

\copy (SELECT id, email, created_at FROM users WHERE created_at > '2024-01-01') TO '/tmp/users.csv' WITH CSV HEADER

Import from CSV

\copy users (email, name) FROM '/tmp/new_users.csv' WITH CSV HEADER

Large Data Loads

For bulk imports, \copy is dramatically faster than individual INSERT statements. A million-row CSV file loads in seconds, not hours.

\copy events FROM '/tmp/events.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', NULL '')

psql Variables & Scripting

Setting Variables

\set table_name users
SELECT count(*) FROM :table_name;

Conditional Execution

\set ON_ERROR_STOP on
\set ON_ERROR_ROLLBACK interactive

ON_ERROR_STOP is critical for scripts: psql exits immediately on error instead of silently continuing.

psqlrc Configuration

Create ~/.psqlrc to customize psql on startup:

\set QUIET 1
\pset null '(null)'
\pset pager off
\timing on
\x auto
\set HISTSIZE 10000
\set COMP_KEYWORD_CASE upper
\set ON_ERROR_ROLLBACK interactive
\set PROMPT1 '%[%033[1;32m%]%/%[%033[0m%] %R%# '
\unset QUIET

This gives you null visibility, auto-expanded output, timing, and a colored prompt in every session.

pgAdmin: Visual Management

pgAdmin 4 is the official GUI for PostgreSQL. It runs as a web application (local or remote) and provides:

Visual query builder and SQL editor with autocomplete
Server/database/schema browser
Table data viewer and editor
Dashboard with live server statistics
Backup and restore wizards
User and permission management

pgAdmin is useful for:

Exploring an unfamiliar database structure
Visually inspecting query plans (graphical EXPLAIN)
Managing permissions across many objects
Developers who prefer graphical interfaces

Install via your package manager or run in Docker:

docker run -d -p 5050:80 \
    -e PGADMIN_DEFAULT_EMAIL=admin@example.com \
    -e PGADMIN_DEFAULT_PASSWORD=admin \
    dpage/pgadmin4

DBeaver: The Universal Client

DBeaver is a free, cross-platform database tool that supports Postgres (and dozens of other databases). It offers:

Entity-relationship diagrams generated from schema
Advanced SQL editor with autocomplete
Data export to CSV, JSON, XML, SQL
Visual query plan analysis
SSH tunneling built in

DBeaver is a strong choice when you work with multiple database systems and want a single tool. It uses the JDBC driver, so it supports every Postgres feature.

pgcli: psql with Autocomplete

pgcli is a drop-in replacement for psql that adds intelligent autocomplete and syntax highlighting:

pip install pgcli
pgcli -h localhost -U myuser -d mydb

Features:

Context-aware autocomplete for tables, columns, functions, and keywords
Syntax highlighting with multiple color themes
Multi-line editing
Fuzzy matching on table and column names

pgcli is excellent for interactive exploration. For scripting, stick with psql since pgcli is not designed for non-interactive use.

Connection Strings

PostgreSQL supports two connection string formats:

URI Format

postgresql://user:password@host:port/dbname?sslmode=require&connect_timeout=10

Key-Value Format

host=db.example.com port=5432 dbname=mydb user=myuser password=secret sslmode=require

Common parameters:

sslmode=require          Encrypt the connection
connect_timeout=10       Fail after 10 seconds if server is unreachable
application_name=myapp   Identifies the application in pg_stat_activity
options=-c search_path=myschema   Set session parameters on connect

Both formats work with psql, application drivers (psycopg, node-postgres, JDBC), and most Postgres tools.

Useful psql One-Liners

Table Sizes

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

Active Queries

SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Blocking Queries

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_locks AS bl ON bl.pid = blocked.pid
JOIN pg_locks AS kl ON kl.locktype = bl.locktype
    AND kl.database IS NOT DISTINCT FROM bl.database
    AND kl.relation IS NOT DISTINCT FROM bl.relation
    AND kl.page IS NOT DISTINCT FROM bl.page
    AND kl.tuple IS NOT DISTINCT FROM bl.tuple
    AND kl.pid != bl.pid
    AND NOT kl.granted
JOIN pg_stat_activity AS blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;

Common Pitfalls

Not using \timing. Without timing, you are guessing about query performance. Turn it on permanently in your psqlrc.
Ignoring \x auto. Wide result sets are unreadable in horizontal format. Auto-expanded mode fixes this.
Using COPY instead of \copy. The server-side COPY command reads files on the server filesystem and requires superuser. The client-side \copy reads files from your local machine.
Not setting ON_ERROR_STOP in scripts. Without it, psql continues executing after errors, potentially running destructive statements against an inconsistent state.
Storing passwords in shell history. Use .pgpass or environment variables, not -W on the command line.
Skipping psqlrc configuration. Five minutes of setup saves hours of repetitive typing.

Key Takeaways

psql is the essential Postgres tool. Learn the backslash commands: \d, \dt, \di, \l, \c, \timing, \x, \copy.
Configure ~/.psqlrc with timing, expanded display, null visibility, and ON_ERROR_ROLLBACK.
Use \e to edit complex queries in your preferred editor.
\copy handles CSV import/export from the client side without superuser privileges.
pgcli adds autocomplete and syntax highlighting for interactive sessions.
pgAdmin and DBeaver are solid GUI options for visual exploration and schema browsing.
Connection strings work in URI or key-value format across all Postgres tools and drivers.