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