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
| Code Block |
|---|
|
USER=foo
DB=bar
BACKUP=/var/backups/postgresql/20140412005701.residents.dump.tar.gz |
Create the user
| Code Block |
|---|
|
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
| Code Block |
|---|
|
sudo -u postgres createdb ${DB} -O ${USER} |
Restore
| Code Block |
|---|
|
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
| Code Block |
|---|
|
sudo -u postgres psql << EOF
ALTER ROLE ${USER} NOSUPERUSER;
EOF |