## What it is

`psql` is PostgreSQL's interactive terminal — a REPL that speaks both SQL and its own set of backslash meta-commands (`\d`, `\l`, `\du`) for inspecting and managing a database. It's the tool you drop into to check a schema before a migration, run a query file as part of a deploy, or take a backup with `pg_dump` before you ship a risky change.

This sheet covers the meta-commands and SQL you actually use day to day, plus the deployment-specific patterns generic psql references skip: migrating without locking a live table (`CREATE INDEX CONCURRENTLY`), backing up before a deploy, splitting migration privileges from runtime privileges, and triaging a slow query mid-release with `EXPLAIN (ANALYZE, BUFFERS)`.

## Quick reference

### Connecting

```bash
psql                                                   # connect as current OS user to same-named DB
psql -U appuser -d appdb -h db.internal -p 5432         # explicit user / db / host / port
psql -U appuser -d appdb -h db.internal -W             # force password prompt
psql "postgresql://appuser:secret@db.internal:5432/appdb?sslmode=require"   # connection URI
psql "host=db.internal dbname=appdb user=appuser sslmode=verify-full"       # keyword/value form
PGPASSWORD=secret psql -U appuser -d appdb              # password via env (avoid in shared shells)
```

`~/.pgpass` (chmod `0600`) holds credentials as `host:port:db:user:password` so scripts connect without inlining passwords. `PGHOST`, `PGPORT`, `PGUSER`, `PGDATABASE`, and `PGSSLMODE` env vars set defaults for every `psql`/`pg_dump` call in a shell.

### Meta-commands: navigation and inspection

```
\c dbname                    # connect to a different database
\c dbname user host port     # switch db + role + host in one go
\conninfo                    # who am I / where am I connected
\l        (\list)            # list all databases
\dn                          # list schemas
\dt                          # list tables in current schema
\dt schema.*                 # list tables in a specific schema
\dt+                         # tables WITH size and description
\d tablename                 # describe a table (columns, indexes, constraints, FKs)
\d+ tablename                # describe + storage, stats target, comments
\di                          # list indexes
\dv                          # list views
\df                          # list functions
\du                          # list roles and their attributes
\dp  (\z)                    # list table access privileges (GRANTs)
\ds                          # list sequences
\dx                          # list installed extensions
\sf funcname                 # show a function's source
```

### psql control commands

```
\x                           # toggle expanded output (one column per line — great for wide rows)
\x auto                      # expanded only when a row is too wide for the terminal
\timing                      # toggle query execution timing on/off
\pset null '∅'               # render NULL visibly instead of blank
\pset pager off              # stop piping output through less
\e                           # open the last query in $EDITOR
\i script.sql                # run a SQL file from inside the session
\ir script.sql               # run a file relative to the current script's dir
\watch 5                     # re-run the last query every 5 seconds
\copy ... to 'file'          # client-side COPY (see below)
\set VERBOSITY verbose       # full error detail incl. SQLSTATE
\q                           # quit
```

### Running SQL non-interactively (for scripts and deploys)

```bash
psql -d appdb -c "SELECT version();"                   # run one command, exit
psql -d appdb -f migrate.sql                           # run a whole file
psql -d appdb -Atc "SELECT count(*) FROM users"        # -A unaligned -t tuples-only → clean scalar
psql -v ON_ERROR_STOP=1 -f migrate.sql                 # ABORT on first error (essential in deploys)
psql -1 -v ON_ERROR_STOP=1 -f migrate.sql              # wrap the whole file in one transaction
psql -d appdb -f migrate.sql -L migrate.log            # tee session output to a log
```

`-v ON_ERROR_STOP=1` is the single most important flag for deploy scripts: without it, `psql` keeps executing after a failed statement and exits `0`, so a broken migration reports success.

### `\copy` — bulk import/export

```
\copy users TO 'users.csv' WITH (FORMAT csv, HEADER)
\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER)
\copy (SELECT id,email FROM users WHERE active) TO 'active.csv' WITH (FORMAT csv, HEADER)
```

`\copy` runs client-side (reads/writes files on your machine, over the existing connection). The SQL `COPY` command runs server-side and needs superuser plus a path the *server* can reach — `\copy` is what you want from a deploy runner.

### Roles, privileges, and GRANT

```sql
CREATE ROLE app_runtime LOGIN PASSWORD 'x';            -- a login role (a "user")
CREATE ROLE readonly NOLOGIN;                          -- a group role
GRANT CONNECT ON DATABASE appdb TO app_runtime;
GRANT USAGE ON SCHEMA public TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO analytics_user;                      -- grant a group role to a user

-- Make privileges apply to FUTURE tables too (the common gotcha):
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;

REVOKE INSERT ON payments FROM app_runtime;
```

### Indexes and EXPLAIN

```sql
CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at DESC);
CREATE INDEX idx_users_lower_email ON users (lower(email));      -- expression index
CREATE INDEX idx_orders_open ON orders (user_id) WHERE status = 'open';   -- partial index
DROP INDEX idx_users_email;

EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';             -- plan only, no execution
EXPLAIN ANALYZE SELECT ...;                                      -- actually runs it, shows real timing
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;                           -- + shared/read buffer counts
```

`EXPLAIN ANALYZE` executes the query — never run it on a mutating statement (`UPDATE`/`DELETE`) outside a rolled-back transaction.

### Session housekeeping

```sql
SELECT current_database(), current_user, version();
SELECT pg_size_pretty(pg_database_size('appdb'));               -- DB size, human-readable
SELECT pg_size_pretty(pg_total_relation_size('orders'));        -- table + indexes + toast
SELECT * FROM pg_stat_activity WHERE state != 'idle';           -- what's running right now
SELECT pg_terminate_backend(<pid>);                             -- kill a stuck backend
SHOW statement_timeout;                                         -- current session setting
```

---

## Deployment workflows (the moat)

### 1. Adding an index without locking the table: `CREATE INDEX CONCURRENTLY`

A plain `CREATE INDEX` takes a `SHARE` lock that blocks every write to the table for the entire build — on a large, live table that's a write outage for the whole deploy. `CREATE INDEX CONCURRENTLY` builds the index without blocking reads or writes:

```sql
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
```

The catch that trips up every ORM migration: **`CONCURRENTLY` cannot run inside a transaction block.** Most migration frameworks wrap each migration in `BEGIN/COMMIT` automatically, which produces:

```
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
```

Fixes per framework:

```ruby
# Rails — disable the automatic transaction for this migration
class AddIndexToOrders < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!
  def change
    add_index :orders, :user_id, algorithm: :concurrently
  end
end
```

```python
# Django — mark the migration non-atomic
class Migration(migrations.Migration):
    atomic = False
    operations = [migrations.AddIndex(...)]   # use AddIndexConcurrently from django.contrib.postgres
```

For raw-SQL migrations run via `psql`, split the concurrent index into its **own file run without `-1`** (the single-transaction flag), and verify it succeeded — a failed `CONCURRENTLY` build leaves an `INVALID` index behind:

```sql
-- Detect and clean up a failed concurrent build before retrying:
SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_orders_user_id;   -- then re-create
```

### 2. Back up before you deploy: `pg_dump` / `pg_restore` / `pg_dumpall`

Take a restorable snapshot as a pre-deploy gate. The custom format (`-Fc`) is the one to use — it's compressed and lets `pg_restore` do selective, parallel restores:

```bash
# Full logical backup in custom format (best for restore flexibility)
pg_dump -Fc -U appuser -h db.internal -d appdb -f "appdb-$(date -u +%Y%m%dT%H%M%SZ).dump"

# Schema-only (fast pre-migration snapshot of structure)
pg_dump -s -U appuser -d appdb -f schema.sql

# Just one table before a risky data migration
pg_dump -Fc -t payments -U appuser -d appdb -f payments-pre-deploy.dump

# Restore into a fresh DB, 4 parallel workers
createdb appdb_restore
pg_restore -j 4 -U appuser -d appdb_restore appdb-20260710T120000Z.dump

# Restore just one table into the live DB
pg_restore -U appuser -d appdb -t payments payments-pre-deploy.dump

# pg_dumpall — roles and global objects (NOT captured by pg_dump)
pg_dumpall -U postgres --roles-only -f roles.sql
```

Wire this as a pre-deploy command hook so the release aborts if the backup fails:

```bash
#!/usr/bin/env bash
set -euo pipefail

STAMP=$(date -u +%Y%m%dT%H%M%SZ)
DUMP="/var/backups/pg/appdb-${STAMP}.dump"

pg_dump -Fc -U "$PGUSER" -h "$PGHOST" -d "$PGDATABASE" -f "$DUMP"
# Verify the dump is readable — a truncated dump is worse than no dump
pg_restore --list "$DUMP" > /dev/null

echo "backup ok: $DUMP ($(du -h "$DUMP" | cut -f1))"
```

`pg_dump` captures one database's schema + data but **not** roles or tablespaces — those live at the cluster level and need `pg_dumpall --roles-only`. A restore plan that forgets roles fails on the first `GRANT`.

### 3. Least-privilege roles: separate the migration role from the runtime role

The account your app uses at runtime should not be able to `DROP TABLE`. Split privileges so a compromised app credential can't reshape the schema, and only the migration step (which runs briefly during deploy) holds DDL rights:

```sql
-- Migration role — owns the schema, runs DDL during deploys only
CREATE ROLE app_migrate LOGIN PASSWORD 'MIGRATE_SECRET';
GRANT ALL ON DATABASE appdb TO app_migrate;
GRANT ALL ON SCHEMA public TO app_migrate;

-- Runtime role — DML only, no DDL, no DROP
CREATE ROLE app_runtime LOGIN PASSWORD 'RUNTIME_SECRET';
GRANT CONNECT ON DATABASE appdb TO app_runtime;
GRANT USAGE ON SCHEMA public TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_runtime;

-- Critical: default privileges so tables created by app_migrate are usable by app_runtime.
-- Run this AS app_migrate (or set the FOR ROLE), since default privileges apply to the creating role.
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrate IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrate IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;
```

Deploy pipeline: run migrations with `PGUSER=app_migrate`, then boot the app with `PGUSER=app_runtime`. The runtime credential never has DDL rights, so an injection or a rogue release can't `ALTER`/`DROP` your schema. The `ALTER DEFAULT PRIVILEGES` line is the one everyone forgets — without it, every table a new migration creates is invisible to the runtime role until you re-`GRANT`.

### 4. Slow-query triage during a release: `EXPLAIN (ANALYZE, BUFFERS)`

When a deploy makes the app crawl, the cause is usually a new query hitting a sequential scan. Read the real plan, not a guess:

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT text)
SELECT * FROM orders WHERE user_id = 42 AND status = 'open';
```

What to look for in the output:

- **`Seq Scan` on a big table** where you expected an index → the index is missing, or the planner thinks the seq scan is cheaper (often stale stats — run `ANALYZE orders;`).
- **`actual time` far larger than `estimated`** → the planner's row estimate is wrong; re-run `ANALYZE`, or the query needs a better index.
- **`Buffers: shared read=N`** (high) → data isn't cached; large `read` counts mean disk I/O is the bottleneck.
- **`Rows Removed by Filter: N`** (high) → the index isn't selective enough; consider a composite or partial index.

Find the queries worth triaging with the `pg_stat_statements` extension (enable it in `postgresql.conf` / `shared_preload_libraries`):

```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT round(mean_exec_time::numeric, 1) AS avg_ms,
       calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;                                    -- the 10 queries burning the most time
```

### 5. Deploy-safe session settings: `statement_timeout` and `lock_timeout`

A migration that grabs an `ACCESS EXCLUSIVE` lock and then waits behind a long-running query can queue every other query behind it — a full outage from one `ALTER TABLE`. Cap how long migrations wait and run:

```sql
-- At the top of a migration script, or set per-role for the migration user:
SET lock_timeout = '5s';          -- give up acquiring a lock after 5s instead of blocking the world
SET statement_timeout = '30s';    -- abort any single statement that runs longer than 30s

-- Persist for the migration role so every migration inherits the guard:
ALTER ROLE app_migrate SET lock_timeout = '5s';
ALTER ROLE app_migrate SET statement_timeout = '60s';
```

With `lock_timeout` set, a blocked `ALTER TABLE` fails fast with `canceling statement due to lock timeout` — you retry during a quieter window instead of taking the site down. For the runtime role, a conservative `statement_timeout` (e.g. `15s`) stops a pathological query from pinning a connection forever. Also cap connections so a deploy that spins up extra workers doesn't exhaust the pool:

```sql
ALTER ROLE app_runtime CONNECTION LIMIT 40;
SHOW max_connections;                        -- cluster-wide ceiling (default 100)
```

### 6. Blue-green and logical-replication cutover basics

For a zero-downtime schema-or-version cutover, stand up a second database and switch traffic once it's caught up. Logical replication streams changes from the old primary to the new one:

```sql
-- On the SOURCE database:
CREATE PUBLICATION app_pub FOR ALL TABLES;

-- On the TARGET database (schema must already exist there):
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=old-primary dbname=appdb user=replicator password=x'
  PUBLICATION app_pub;

-- Watch replication lag on the source before cutting over:
SELECT application_name,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
FROM pg_stat_replication;
```

Cutover sequence: (1) let the subscription catch up until lag ≈ 0, (2) stop writes to the old primary (put the app in maintenance mode briefly), (3) confirm final lag is zero, (4) point the app's connection string at the new primary, (5) drop the subscription. Blue-green shines for major-version upgrades and large schema rewrites where an in-place `ALTER` would lock too long. The trade-off: logical replication doesn't copy schema (create it on the target first), sequences (advance them manually at cutover), or large objects — plan those steps explicitly.

---

## Common errors and fixes

| Error / symptom | Cause | Fix |
|---|---|---|
| `CREATE INDEX CONCURRENTLY cannot run inside a transaction block` | Migration framework auto-wraps in `BEGIN/COMMIT` | Disable the DDL transaction (`disable_ddl_transaction!` / `atomic = False`); run the file without `-1` |
| `FATAL: too many connections for role "app_runtime"` | Connection pool exhausted; often a deploy spawning extra workers | Add a pooler (PgBouncer), lower app pool size, raise `max_connections`, or set a per-role `CONNECTION LIMIT` |
| `FATAL: remaining connection slots are reserved for non-replication superuser connections` | Cluster hit `max_connections` | Use a connection pooler; kill idle backends via `pg_stat_activity` + `pg_terminate_backend` |
| `FATAL: role "appuser" does not exist` | Role never created, or `pg_dump` restored data but not roles | Create the role, or run `pg_dumpall --roles-only` and load it before the data dump |
| `FATAL: password authentication failed for user "appuser"` | Wrong password, or `pg_hba.conf` uses `peer`/`ident` not `md5`/`scram` | Fix the password; check `pg_hba.conf` auth method for that host/user; reload with `SELECT pg_reload_conf()` |
| `canceling statement due to lock timeout` | `ALTER TABLE` blocked behind a long transaction | Expected with `lock_timeout` set — retry in a quieter window; find the blocker in `pg_stat_activity` |
| `canceling statement due to statement timeout` | Query exceeded `statement_timeout` | Optimize the query (see workflow 4), add an index, or raise the timeout for that specific statement |
| `ERROR: deadlock detected` | Two transactions locked rows in opposite order | Acquire locks in a consistent order across the app; keep transactions short |
| `ERROR: relation "users" does not exist` | Wrong schema in `search_path`, or connected to the wrong DB | `\conninfo` to check DB; `SET search_path TO app, public;` or qualify as `schema.users` |
| `ERROR: permission denied for table users` | Runtime role never granted on this (often newly created) table | `GRANT` on the table, and set `ALTER DEFAULT PRIVILEGES` so future tables inherit (workflow 3) |
| `ERROR: could not serialize access due to concurrent update` | Serializable isolation conflict | Retry the transaction with backoff — expected under `SERIALIZABLE` |
| `psql: error: connection to server ... failed: SSL ... required` | Server enforces SSL, client didn't request it | Add `sslmode=require` (or `verify-full` with a CA) to the connection string |
| Migration reports success but a statement failed | `psql` continues past errors and exits `0` by default | Always run migrations with `-v ON_ERROR_STOP=1` |
| Index built but queries still `Seq Scan` | Planner statistics are stale after a big data load | Run `ANALYZE tablename;` (or `VACUUM ANALYZE`) so the planner sees the new index as worthwhile |

---

## Companion: full DeployHQ deploy workflow

A safe database deploy is a sequence, not a single command: back up (`pg_dump`), run migrations as a least-privilege migration role with `ON_ERROR_STOP=1` and a `lock_timeout`, add indexes with `CONCURRENTLY` outside a transaction, then boot the app on a DML-only runtime role. Ship the migration SQL in your repo so the schema change and the code that needs it arrive in the same release.

For DeployHQ deploys, run the backup and `psql -v ON_ERROR_STOP=1 -f migrate.sql` steps as pre-deploy and post-deploy SSH command hooks in your [automated build pipeline](https://www.deployhq.com/features/build-pipelines) — the release only proceeds if the backup verifies and every migration statement succeeds. Pair it with the [deploy from GitHub to your server](https://www.deployhq.com/deploy-from-github) flow so a merge to `main` triggers the migration-then-release sequence automatically.

[Start a free DeployHQ trial](https://www.deployhq.com/signup) to wire versioned migrations and pre-deploy backups into your pipeline.

---

## Related cheatsheets

- [Bash cheatsheet](https://www.deployhq.com/cheatsheets/bash) — for the `set -euo pipefail` scripts that wrap `pg_dump` and migration runs.
- [Cron and Crontab cheatsheet](https://www.deployhq.com/cheatsheets/cron) — for scheduling the nightly `pg_dump` backups these workflows depend on.
- [Docker cheatsheet](https://www.deployhq.com/cheatsheets/docker) — for running Postgres in a container and `docker exec`-ing into `psql`.
- [rsync cheatsheet](https://www.deployhq.com/cheatsheets/rsync) — for moving dump files off the database host to backup storage.
- [Cheatsheets hub](https://www.deployhq.com/cheatsheets) — every DeployHQ cheatsheet in one place.

---

Need help? Email [support@deployhq.com](mailto:support@deployhq.com) or follow [@deployhq on X](https://x.com/deployhq).