The simplest way to back up and restore a PostgreSQL database us by using pg_dump for backup and pg_restore for restoration. For more information, see https://www.postgresql.org/docs/8.1/backup.html.

Backup your PostgreSQL Database

Backup your PostgreSQL database by running the following command: 

Where: 

• -U <your_username> specifies the PostgreSQL user.

• -d <your_database> specifies the name of the database to back up.

• -F -c specifies the format (c for custom, t for tar, p for plain text).

• -f backup.dump specifies the output file.

Restoring your PostgreSQL Database on a Same VM

1. Restore the backup and exit from the PostgreSQL console:

   pg_restore -U <your_username> -d <your_database> -F c backup.dump

2. Restart PostgreSQL: 

   systemctl restart postgresql

3. Login to the PostgreSQL server:

   sudo -u postgresql psql

4. Ensure the Entrust PSQL extension is created: 

   CREATE EXTENSION IF NOT EXISTS entrust_psql_extension;

Restoring your PostgreSQL Database on a Different VM

1. After the backup is complete, share the folder to the new backup location.

   scp backup.dump user@destination_host:/tmp/backup.dump

   Where user@destination_host is the new location for the backup.

2. Install the Policy Agent in the new directory and configure it. For more information, see Manually Installing and Registering the Policy Agent for PostgreSQL.

3. Enable TDE. For more information, see Enable TDE on PostgreSQL Database Server.

4. Create the database connector in the same Key Set that was used to encrypt the backed up data, then generate the access token and store it in a file. For more information, see: 

   • Configuring the PostgreSQL Database Connector

   • Configuring a Cryptographic Provider on the PostgreSQL Server

5. Restore your database: 

   pg_restore -U <your_username> -d <your_database> -F c /tmp/backup.dump

6. Restart PostgreSQL: 

   systemctl restart postgresql

   Login to the PostgreSQL server: 

   sudo -u postgresql psql

7. Ensure the Entrust PSQL extension is created: 

   CREATE EXTENSION IF NOT EXISTS entrust_psql_extension;