If you find yourself needing to restore a PostgreSQL database in a running Docker container, this guide will walk you through the process.

This guide assumes you have a backup database stored in a file.

/home/matija/back_scraper_saas_18_2_2024.sql

• Docker
• Ubuntu
• Postgres

Steps to Restore

To begin, we need the container ID and the database credentials for the database we want to restore. This process is an in-place restore, meaning we are restoring the database directly in its current location. You can find the container ID by running the docker ps command.

For me, it was: 0dc49acc42d8

Next up we need to find the database credentials. You can extract them from your connection URI:

postgresql://admin:dummyPass@127.0.0.1:5432/scraper_saas?schema=public

We are mainly interested in the user and the database name.

1. Backup Verification

Before proceeding, ensure you have a reliable backup file. In this case, the backup file is:

/home/matija/back_scraper_saas_18_2_2024.sql

2. Copy the Backup File to the Docker Container

Firstly, we need to import the backup database in the container. Run the following command to copy the backup file into the container:

docker cp /home/matija/back_scraper_saas_18_2_2024.sql 0dc49acc42d8:/back_scraper_saas_18_2_2024.sql

Explanation:

• /home/matija/back_scraper_saas_18_2_2024.sql: Path to the backup file on the host machine.
• 0dc49acc42d8: The ID of the Docker container.
• /back_scraper_saas_18_2_2024.sql: Path inside the container where the backup will be placed.

3. Access the Docker Container

To interact with the database inside the container, start a Bash session:

docker exec -it 0dc49acc42d8 bash

4. Reset the Database

This step is optional. However, it is required if the database has existing data.

Execute these steps to reset:

I) Connect to the PostgreSQL instance:

psql -U admin -h 127.0.0.1 -p 5432 -d postgres

Replace admin with your database user and 127.0.0.1 with the host.

II) Terminate active connections:

This will kick out all connections to the database.

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'scraper_saas' AND pid <> pg_backend_pid();

III) Drop and recreate the database:

❗❗❗This is the point of no return. Once you run this command the database will be deleted for good. Before copy-pasting please make sure the backup is correct!

DROP DATABASE "scraper_saas"; CREATE DATABASE "scraper_saas";

5. Restore the Backup

After resetting the database, restore the backup:

psql -U admin -d scraper_saas -f /back_scraper_saas_18_2_2024.sql

If errors like the following appear when restoring:

psql:/back_scraper_saas_18_2_2024.sql:29: ERROR: type "ActivityStatus" already exists

This indicates remnants of the previous database schema. You must drop and recreate the database (retry step 4).

Explanation:

• -U admin: Specifies the database user.
• -d scraper_saas: Specifies the database to restore to.
• -f /back_scraper_saas_18_2_2024.sql: Path to the backup file inside the container.

6. Verification

Verify that the restoration was successful by checking the database:

psql -U admin -d scraper_saas

Run queries to confirm the expected data and schema are present.

Key Learnings

Always create backups before making schema changes.

Proper preparation can make the recovery process swift and seamless, often taking less than a minute.

Following these steps, you can restore your PostgreSQL database efficiently and avoid potential downtime in your development workflow.

Learn how to back up your database, check out the following guide.

Thanks,
Matija