Backup & Restore

Barman

Kubernetes

Use custom format (recommended)

With this, you can use pg_restore with more options (like clean all DB objects before restoring).

Backup:

sudo -u postgres pg_dump --format=custom dbname > filename

Restore:

DROP DATABASE openproject;
CREATE DATABASE openproject;
pg_restore -d openproject /var/backups/postgres/openproject.pg_dump

Unfortunately, this didn't work:

pg_restore --create --clean -d openproject /var/backups/postgres/openproject.pg_dump

Convert custom dump format to sql

pg_restore openproject-2017-02-09-13\:17\:25.dump > openproject-2017-02-09-13:17:25.sql

Using plain sql dump format

https://www.postgresql.org/docs/9.4/static/backup-dump.html

Backup:

pg_dump dbname > outfile

Restore:

psql dbname < infile
psql --set ON_ERROR_STOP=on dbname < infile

Storing dumps in version control

akaihola/pgtricks Handy helpers for PostgreSQL users

Usage:

git clone https://github.com/akaihola/pgtricks.git
cd path/to/git/repo
pg_dump --clean openproject > openproject.sql
../pgtricks/pgtricks/pg_dump_splitsort.py openproject.sql