How To Take a Postgres Backup and Restore From a Postgres Dump File?

There are situations where it’s good practice to take a backup of the Postgres database. Think of events such as during the upgrade process, to make sure you can go back to the previous working Postgres database if the upgrade corrupts Postgres.

To take a postgres dump (backup), use the following commands:

1. As Tamr functional user, stop Tamr. This is to make sure Tamr doesn’t access the Postgres database while we are backing up or restoring Postgres.

cd \<TAMR_HOME>/tamr

./stop-unify.sh

2. Execute the below command to take Postgres dump (backup). You should see the SQL file in the location you run this command from.

pg_dump -h localhost -p 5432 -U tamr -d doit > pg_dump.sql

To restore from a Postgres dump, execute the following commands. Clear the ‘doit’ database first before restoring from a Postgres dump file as we don’t want the database to be corrupted.

3. As Tamr functional user, stop Tamr. This is to make sure Tamr doesn’t access the Postgres database while we are backing up or restoring Postgres.

cd <TAMR_HOME>/tamr

./stop-unify.sh

4. Switch user to ‘postgres’

sudo su - postgres

5. Log into Postgres terminal

psql

6. Drop/Delete the ‘doit’ database

DROP DATABASE doit;

7. Create new ‘doit’ database with owner ‘tamr’

CREATE DATABASE doit WITH OWNER tamr;

8. Quit the Postgres terminal

\q

9. To restore the database, run the following command from the location where the sql file (dump file) exists.

psql -U tamr -d doit -p 5432 -h localhost -f pg_dump.sql