Around psql there is a set of PostgreSQL command-line utilities for routine operations without SQL: createdb/dropdb — create and drop a database, createuser — a role, pg_dump/pg_restore/pg_dumpall — backups, pg_isready — check that the server accepts connections. pg_lsclusters and pg_ctlcluster are Debian/Ubuntu wrappers for managing server instances ("clusters").
Syntax
createdb [connection options] [-O owner] [-E encoding] [-T template] dbname
dropdb [connection options] [--if-exists] [-f] dbname
pg_dump [connection options] [options] dbname
pg_restore [connection options] [-d db] [options] file
pg_isready [-h host] [-p port] [-t sec] [-q]
pg_ctlcluster version cluster start|stop|restart|reload|status
pg_lsclusters
Connection (common to all)
| Option | What it sets | Environment variable |
|---|
-h host | host or socket directory; without it — the Unix socket /var/run/postgresql | PGHOST |
-p port | port (5432) | PGPORT |
-U role | which role to connect as (default — the OS user name) | PGUSER |
-d db | database to connect to (for pg_restore — where to restore) | PGDATABASE |
-W / -w | always prompt for a password / never prompt | PGPASSWORD, ~/.pgpass |
createdb, dropdb, createuser
| Command and option | What it does |
|---|
createdb name | create a database |
-O role | database owner |
-E UTF8 | encoding; one different from the template requires -T template0 and a matching locale: -E LATIN1 -T template0 -l C |
-T template0 | which template to copy from |
-l locale, --lc-collate, --lc-ctype | collation and character class locale |
-e | show the executed SQL |
dropdb name | drop a database; --if-exists — no error if it doesn't exist; -f — terminate other connections |
createuser role | create a role; -d — can create databases, -s — superuser, -P — prompt for a password |
pg_dump: backing up one database
| Option | What it does |
|---|
-f file | where to write (without it — stdout) |
-F p | plain format — a regular SQL script (the default); restored with psql |
-F c | custom — a compressed archive; restored with pg_restore, objects can be selected |
-F d | directory — a directory with a file per table; the only format supporting parallel -j N |
-F t | tar archive |
-j N | dump in N parallel jobs (-F d only) |
-Z 0..9 | compression level |
-s / -a | schema only / data only |
-t pattern / -T pattern | only these tables / all but these; can be repeated, understand * |
--exclude-table-data=pattern | take the structure but not the table's data (logs, cache) |
-n schema / -N schema | only this schema / all but this one |
-c + --if-exists | add DROP … IF EXISTS before creating objects |
-C | add CREATE DATABASE and a connection to it |
-O | don't save owners (ALTER … OWNER) — for restoring under a different role |
-x | don't save privileges (GRANT/REVOKE) |
--inserts / --column-inserts | data as INSERT (with column names) instead of COPY — slower but portable |
-v | verbose progress |
pg_dumpall dumps the whole server; pg_dumpall -g — only global objects: roles and tablespaces.
pg_restore: restoring from custom/directory/tar
| Option | What it does |
|---|
-d db | restore into an existing database |
-C -d postgres | create the database first (with the original name), connecting to postgres |
-c + --if-exists | drop objects before creating them |
-l | show the archive's table of contents |
-L list | restore only what remains in an edited table of contents |
-t table, -n schema, -s, -a | choose what to restore |
-O, -x | without owners, without privileges |
-j N | in parallel (custom and directory) |
-1 | everything in one transaction: all or nothing |
-e | stop at the first error (by default it continues) |
-f file | don't restore, generate an SQL script instead; -f - — to stdout |
pg_isready
| Option | What it does |
|---|
-h, -p | which server to check |
-t sec | how long to wait (default 3; 0 — forever) |
-q | print nothing, only the exit code |
| Code | Meaning |
|---|
0 | the server accepts connections |
1 | the server responds but rejects connections (e.g. still starting up) |
2 | no response |
3 | no attempt was made (bad parameters) |
pg_ctlcluster and pg_lsclusters (Debian/Ubuntu)
| Command | What it does |
|---|
pg_lsclusters | list of clusters: version, name, port, status (online/down), owner, data directory, log |
pg_ctlcluster 16 main start | start; code 2 — already running |
pg_ctlcluster 16 main stop | stop (fast mode); -m smart|fast|immediate — another mode |
pg_ctlcluster 16 main restart | restart |
pg_ctlcluster 16 main reload | re-read configs without stopping |
pg_ctlcluster 16 main status | is it running (code 3 — no) |
Cluster configs live in /etc/postgresql/16/main/, data in /var/lib/postgresql/16/main, the log in /var/log/postgresql/postgresql-16-main.log.
Examples
# all utilities — as the postgres system user (socket login without a password)
sudo -u postgres createdb -O app shop # or: su postgres -c 'createdb shop'
# is the server up? — good for waiting in scripts
until pg_isready -q; do sleep 1; done
# start a cluster without systemd and check its status
pg_ctlcluster 16 main start
pg_lsclusters
# dump to SQL and restore via psql, stopping at the first error
sudo -u postgres pg_dump shop > shop.sql
sudo -u postgres createdb shop_copy
sudo -u postgres psql -v ON_ERROR_STOP=1 -q -d shop_copy -f shop.sql
# dump in custom format and restore into a new database
sudo -u postgres pg_dump -Fc -f shop.dump shop
sudo -u postgres createdb shop_copy2
sudo -u postgres pg_restore -d shop_copy2 shop.dump
# what's inside the archive
pg_restore -l shop.dump
# restore over existing objects
sudo -u postgres pg_restore -c --if-exists -d shop_copy2 shop.dump
# one table only, data only, as INSERTs
sudo -u postgres pg_dump -t items -a --inserts shop
# structure of the whole database only
sudo -u postgres pg_dump -s shop > schema.sql
# parallel dump of a large database
sudo -u postgres pg_dump -Fd -j 4 -f shop_dir shop
# roles separately — pg_dump doesn't save them
sudo -u postgres pg_dumpall -g > roles.sql
# compressed dump on the fly
sudo -u postgres pg_dump shop | gzip > shop.sql.gz
Pitfalls
role "root" does not exist. The utilities connect as a role named after the current OS user. Run them as postgres (sudo -u postgres …, su postgres -c '…') or pass -U.
-h 127.0.0.1 is no longer the socket. Over TCP a password is usually required (fe_sendauth: no password supplied), even if everything worked without -h: pg_hba.conf rules for the socket and TCP differ.
psql < dump.sql doesn't stop on errors and returns 0 even if half the commands failed (relation … already exists). Add -v ON_ERROR_STOP=1 — then the code will be 3.
pg_restore doesn't stop either: by default it skips errors, prints errors ignored on restore: N at the end and returns 1. Need all-or-nothing — -1 or -e.
- The format determines the tool.
pg_restore won't take a plain dump (input file appears to be a text format dump. Please use psql), and feeding a custom archive to psql is pointless.
pg_restore -d into a nonexistent database fails. Run createdb first, or -C -d postgres — but then the database is created with the original name from the dump, not the one passed in -d.
-j in pg_dump works only with -F d (parallel backup only supported by the directory format).
pg_dump doesn't save roles and server-level privileges. On a new server restore pg_dumpall -g first, otherwise ALTER … OWNER TO app will fail. Or dump with -O -x.
pg_isready doesn't check login and database. It returns 0 for a nonexistent database and role — it only means "the server is listening".
systemctl doesn't work in a container without systemd. Use pg_ctlcluster or service postgresql start.
See also
psql · sqlite3 · redis-cli · gzip · sudo · cron