How to back up and restore Postgresql DB via pg_dump

Follow

This method involves connecting to the PostgreSQL db and using pg_dump, and thus, is recommended if you are migrating the database to another server.

You can back up and restore the database by copying the postgres pvc folder. See Back up and restore DataClarity Postgresql database (create a copy of the postgres pvc).

Create a backup

1. Connect to the VM where the DataClarity platform is installed, and then connect to the Postgres container:

   kubectl exec -it postgres-67fc4f87f8-vwvsf sh

   
   

   Note: The postgres container has a different name each time, but it always starts with ‘postgres’. While entering the command, after typing ‘post’, press the Tab key to autocomplete the container name.

   
   
   

2. Create a DB backup using pg_dump, where ‘dc_db’ is the main database and ‘dc_db_bk.sql’ is the filename of the backup, which you can change as needed:

   pg_dump -d dc_db -U postgres -p 5432 -f dc_db_bk.sql

3. Exit the container:

   exit

4. Copy the backup file to a place outside of the docker container:

   kubectl cp postgres-67fc4f87f8-vwvsf:/dc_db_bk.sql /home/user/pgbackup/dc_db_bk.sql

   
   

   Important: Do not skip this step because the backup file will be lost if, for any reason, the docker container is recreated.

Restore a backup

1. Copy the backup file inside the postgres container:

   kubectl cp /home/user/pgbackup/dc_db_bk.sql postgres-67fc4f87f8-vwvsf:/dc_db_bk.sql

2. Connect to the postgres container:

   kubectl exec -it postgres-67fc4f87f8-vwvsf sh

3. Connect to the PostgreSQL server:

   psql -U postgres

4. Terminate all the database connections, and then drop the dc_db database:

   SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'dc_db';
   DROP DATABASE dc_db;

   
   

   Note: If you get an error stating that the dc_db database is being accessed by other users, run the above command to terminate the connections again, immediately followed by the drop command.

5. Create the dc_db database:

   CREATE DATABASE dc_db;

6. Close the PostgreSQL connection:

   exit

7. Restore the database backup:

   psql -U postgres -d dc_db -1 -f dc_db_bk.sql