PostgreSQL 18: Backup and restore

Back up a database

Write a compressed custom-format dump that pg_restore can restore selectively.

pg_dump -U postgres -d mydb -F c -f mydb.dump

Back up as plain SQL

Write a SQL script you can read and replay with psql.

pg_dump -U postgres -d mydb -f mydb.sql

Restore a custom-format dump

Restore a dump into an existing, empty database.

pg_restore -U postgres -d mydb mydb.dump

Caution: Restoring into a database that already has data can fail or overwrite objects. Restore into a new database first if unsure.

Restore a plain SQL dump

Replay a SQL script and stop at the first error.

psql -U postgres -d mydb -v ON_ERROR_STOP=1 -f mydb.sql

Export a table to CSV

Write a table to a CSV file on your machine, from inside psql.

\copy users TO 'users.csv' WITH (FORMAT csv, HEADER)