Use case: you have a database backup file, and need to import it to a running Postgres.

Debian automatically saves backups of all databases in /var/backup/postgres, let's restore them in a fresh instance of Postgres. This guide can thus be used when restoring service after a crash, migrating to another version of postgres or another server.

Setup the environment

USER=foo
DB=bar
BACKUP=/var/backups/postgresql/20140412005701.residents.dump.tar.gz

Create the user

sudo -u postgres createuser ${USER} -P

Notes:

• a password is prompted
• the user must be superuser to replay backups created as a tar file

Create the Database

sudo -u postgres createdb ${DB} -O ${USER}

Restore

gunzip -c ${BACKUP} | pg_restore -h localhost --username=${USER} -d ${DB} -W 

The user's password (entered just previously) needs to be entered again.

Remove the superuser privileges

sudo -u postgres psql << EOF
ALTER ROLE ${USER} NOSUPERUSER;
EOF