Backups with Postgres 12

How to create and restore backups for PostgreSQL 12 databases.

Backups

Generate a custom-format backup (*.tar.gz).

pg_dump -Fc \
  -h localhost \
  -p 5432 \
  -U postgres \
  -f backup.tar.gz \
  database

Export to plain SQL (*.sql).

pg_dump \
  -h localhost \
  -p 5432 \
  -U postgres \
  database > database.sql

Where:

Restore Databases

First create an empty database (example: new_database) to load the data into.

createdb \
  -h localhost \
  -p 5432 \
  -U postgres \
  new_database

Restore from a *.tar.gz file.

pg_restore \
  -h localhost \
  -p 5432 \
  -U postgres \
  -d new_database \
  backup.tar.gz

When working with *.sql dumps, start a session as the postgres user.

sudo su postgres

Then run the following command:

psql \
  -h localhost \
  -p 5432 \
  -U postgres \
  -d new_database \
  -f database.sql

References

• PostgreSQL: Documentation: 12: pg_dump