Table of Contents
Restore a PostgreSQL/TimescaleDB database backup. There are 3 backup methods supported:
- pg_dump – Logical backup
- pg_dumpall – Logical backup
- pg_basebackup – Physical backup
- pgBackRest – Physical backup
Full pg_dump/pg_dumpall backup restore
1. Decompress the backup:
$ cat /path/to/backup.tar.gz | gunzip > backup.tar.decompressedIgnore this step if the backup is not compressed.
2. Restore the backup
$ PGPASSWORD='{password}' psql -h127.0.0.1 -U{backup ninja agent DB user} postgres -f backup.tar.decompressed1. To decrypt a backup, it’s recommended to disable command history (using set +o history) when specifying the encryption key to decrypt and decompress the backup:
$ set +o history
$ bartender decrypt -k {the-encryption-key} -f /path/to/backup.tar.gz -x -o backup.tar.decrypted_decompressed
$ set -o historyIf the backup is not compressed, remove the -x flag from the command above.
2. Restore the backup:
$ PGPASSWORD='{password}' psql -h127.0.0.1 -U{backup ninja agent DB user} postgres -f backup.tar.decrypted_decompressedPhysical backup restore using pg_basebackup/PgBackRest
1. Decompress the backup:
$ cat /path/to/backup.tar.gz | gunzip > backup.tar.decompressedIgnore this step if the backup is not compressed.
2. Stop PostgreSQL service:
$ systemctl stop postgresql3. Wipeout or move the PostgreSQL data directory:
$ rm -rf /path/to/data-dir/main/*4. Restore the backup by copying the data into the data directory:
$ sudo -u postgres tar -xvC /path/to/data-dir/main -f backup.tar.decrypted_decompressed5. Start PostgreSQL service:
$ systemctl start postgresqlThe service name might be different depending on the operating system, database package and database version.
1. To decrypt a backup, it’s recommended to disable command history (using set +o history) when specifying the encryption key to decrypt and decompress the backup:
$ set +o history
$ bartender decrypt -k {the-encryption-key} -f /path/to/backup.tar.gz -x -o backup.tar.decrypted_decompressed
$ set -o historyIf the backup is not compressed, remove the -x flag from the command above.
2 Stop PostgreSQL service:
$ systemctl stop postgresql3.Wipe out of move the PostgreSQL data directory:
$ rm -rf /path/to/data-dir/main/*4. Restore the backup by copying the data into the data directory:
$ sudo -u postgres tar -xvC /path/to/data-dir/main -f backup.tar.decrypted_decompressed5. Start PostgreSQL service:
$ systemctl start postgresqlThe service name might be different depending on the operating system, database package and database version.