← Full reference

PostgreSQL utilities: createdb, pg_dump, pg_restore, pg_isready, pg_ctlcluster

Create a database, take and restore a dump, check that the server is ready, start and stop a cluster

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)

OptionWhat it setsEnvironment variable
-h hosthost or socket directory; without it — the Unix socket /var/run/postgresqlPGHOST
-p portport (5432)PGPORT
-U rolewhich role to connect as (default — the OS user name)PGUSER
-d dbdatabase to connect to (for pg_restore — where to restore)PGDATABASE
-W / -walways prompt for a password / never promptPGPASSWORD, ~/.pgpass

createdb, dropdb, createuser

Command and optionWhat it does
createdb namecreate a database
-O roledatabase owner
-E UTF8encoding; one different from the template requires -T template0 and a matching locale: -E LATIN1 -T template0 -l C
-T template0which template to copy from
-l locale, --lc-collate, --lc-ctypecollation and character class locale
-eshow the executed SQL
dropdb namedrop a database; --if-exists — no error if it doesn't exist; -f — terminate other connections
createuser rolecreate a role; -d — can create databases, -s — superuser, -P — prompt for a password

pg_dump: backing up one database

OptionWhat it does
-f filewhere to write (without it — stdout)
-F pplain format — a regular SQL script (the default); restored with psql
-F ccustom — a compressed archive; restored with pg_restore, objects can be selected
-F ddirectory — a directory with a file per table; the only format supporting parallel -j N
-F ttar archive
-j Ndump in N parallel jobs (-F d only)
-Z 0..9compression level
-s / -aschema only / data only
-t pattern / -T patternonly these tables / all but these; can be repeated, understand *
--exclude-table-data=patterntake the structure but not the table's data (logs, cache)
-n schema / -N schemaonly this schema / all but this one
-c + --if-existsadd DROP … IF EXISTS before creating objects
-Cadd CREATE DATABASE and a connection to it
-Odon't save owners (ALTER … OWNER) — for restoring under a different role
-xdon't save privileges (GRANT/REVOKE)
--inserts / --column-insertsdata as INSERT (with column names) instead of COPY — slower but portable
-vverbose progress

pg_dumpall dumps the whole server; pg_dumpall -g — only global objects: roles and tablespaces.

pg_restore: restoring from custom/directory/tar

OptionWhat it does
-d dbrestore into an existing database
-C -d postgrescreate the database first (with the original name), connecting to postgres
-c + --if-existsdrop objects before creating them
-lshow the archive's table of contents
-L listrestore only what remains in an edited table of contents
-t table, -n schema, -s, -achoose what to restore
-O, -xwithout owners, without privileges
-j Nin parallel (custom and directory)
-1everything in one transaction: all or nothing
-estop at the first error (by default it continues)
-f filedon't restore, generate an SQL script instead; -f - — to stdout

pg_isready

OptionWhat it does
-h, -pwhich server to check
-t sechow long to wait (default 3; 0 — forever)
-qprint nothing, only the exit code
CodeMeaning
0the server accepts connections
1the server responds but rejects connections (e.g. still starting up)
2no response
3no attempt was made (bad parameters)

pg_ctlcluster and pg_lsclusters (Debian/Ubuntu)

CommandWhat it does
pg_lsclusterslist of clusters: version, name, port, status (online/down), owner, data directory, log
pg_ctlcluster 16 main startstart; code 2 — already running
pg_ctlcluster 16 main stopstop (fast mode); -m smart|fast|immediate — another mode
pg_ctlcluster 16 main restartrestart
pg_ctlcluster 16 main reloadre-read configs without stopping
pg_ctlcluster 16 main statusis 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