# Restore PostgreSQL with BURP

## Backup

Dump destination directory: `/var/lib/postgresql-backup`

The postgresql-pre-backup script uses pg_dump to export all PostgreSQL databases in a consistent data format before every BURP run, usually once a day. The destination files are named after the database name.

The default dump format used is "custom", it may be overriden by DUMP_FORMAT in `/etc/default/postgresql-pre-backup`

Example file list:

``````confluence.dump
postgres.dump``````

## Restore

Database dumps created in pg_dump custom format have to be processed using the pg_restore tool. The application generally operates in two modes: by directly importing the database dump via `-d database_name` parameter, or by exporting the dump to human readable sql text format by specifying the `-f output.sql` parameter. This file can then be altered with text editor and imported via psql tool.

 In the case of the direct restore, the connection parameter `-d database_name` is handled differently depending on whether the `--create/-C` parameter is used. If the `--create` parameter is set, the database specified in `-d database_name` is only used to connect to the database cluster, while all the restore operations are made to the database for which the dump was created for. If the `--create` parameter is not defined, the restore operations will be applied to the database defined to the `-d database_name`, allowing us to restore the dump into different database.

The following commands should be seen as examples. An actual restore procedure depends on the situation at hand (partial restore after accidental data removal or a full restore after catastrophic hardware failure).

 The operations done with pg_restore will only restore the database schema and the underlying data, but no roles will be recreated. Roles are supposed to be configured via puppet/hieradata.
1. Restore from BURP (see Restore Files from Backup for details and more options):

``````$sudo burp -a r -d /tmp/restore -r ^/var/lib/postgresql-backup/$ sudo chown -R postgres:postgres /tmp/restore``````
 If the service runs on an encrypted disk, ensure you use a restore location that is on an encrypted disk as well.
 The database being imported into has to be created in advance. The `--create` parameter only operates on the existing database that appears in the dump file.
1. Load backup into `-d db` database (tables will not be recreated, potentially destructive as the data could be re-imported!):

``$sudo -u postgres pg_restore ~/restore/db.dump -d db`` 2. Load backup into `-d db` database but first drop the existing objects (will drop existing tables - destructive!): ``$ sudo -u postgres pg_restore ~/restore/db.dump --clean -d db``
3. Load backup into existing database but first drop the database (destructive!). In this case the different connection database has to be supplied via `-d connect_db` parameter (default postgres database can be used):

``\$ sudo -u postgres pg_restore ~/restore/db.dump --clean --create -d postgres``