1. Home
  2. Docs
  3. Backup Ninja
  4. How-To
  5. Restoring
  6. PostgreSQL/TimeScaleDB

PostgreSQL/TimeScaleDB

Restore a PostgreSQL/TimescaleDB database backup. There are 3 backup methods supported:

  • pg_dumpall – Logical backup
  • pg_basebackup – Physical backup
  • pgBackRest – Physical backup

Full pg_dumpall backup restore

Plain BackupEncrypted Backup

1. Decompress the backup:

$ cat /path/to/backup.tar.gz | gunzip > backup.tar.decompressed
Note

Ignore 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.decompressed

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 history
Note

If 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_decompressed

Physical backup restore using pg_basebackup/PgBackRest

Plain BackupEncrypted Backup

1. Decompress the backup:

$ cat /path/to/backup.tar.gz | gunzip > backup.tar.decompressed
Note

Ignore this step if the backup is not compressed.

2. Stop PostgreSQL service:

$ systemctl stop postgresql

3. 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_decompressed

5. Start PostgreSQL service:

$ systemctl start postgresql
Note

The 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 history
Note

If the backup is not compressed, remove the -x flag from the command above.

2 Stop PostgreSQL service:

$ systemctl stop postgresql

3.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_decompressed

5. Start PostgreSQL service:

$ systemctl start postgresql
Note

The service name might be different depending on the operating system, database package and database version.

Was this article helpful to you? Yes No