How to Restore PostgreSQL Database Using psql and pg_restore Inside a Docker Container

In this tutorial, I’ll walk you through how to:

• Start a PostgreSQL server using Docker
• Access the PostgreSQL shell inside the container
• Restore a PostgreSQL database using psql and pg_restore

Along the way, I’ll also answer some common questions developers have when dealing with PostgreSQL and Docker.

---

🤔 Why Restore a Backup File Inside a Docker Container?

Docker is super convenient — that's why many developers install only Docker on their machines and run databases like PostgreSQL, MySQL, or MongoDB inside containers. These containers expose their ports to your host machine, so you can interact with them just like you would with a locally installed service.

Now imagine you want to debug a production database locally. You’d likely receive a backup file and need to restore it inside your Dockerized PostgreSQL instance. That’s exactly what we’ll cover here.

---

🚀 Starting a PostgreSQL Server with Docker

Make sure Docker is installed and running, then execute the following command:

bashCopy
docker run -d \
  --name my-postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -v pgdata:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:16

What this command does:

• Runs a PostgreSQL container in detached mode
• Sets user and password to postgres
• Maps port 5432 of the container to your local machine
• Creates a volume pgdata so your data is persisted (important — otherwise, deleting the container means losing all data!)

To check if your container is running:

bashCopy
docker ps

---

🐚 Accessing the PostgreSQL Shell Inside the Container

The easiest way is via Docker Desktop:

1. Open Docker Desktop
2. Click on your running PostgreSQL container
3. Select "Open in Terminal"
   

Alternatively, use the CLI:

bashCopy
docker exec -it my-postgres bash

Once inside, confirm essential PostgreSQL tools are available:

bashCopy
which pg_dump
which pg_restore
which psql

---

💾 Restoring a Database Inside the Container

There are three main steps:

1. Copy the backup file into your container
2. Create the destination database
3. Restore the database using either psql (for .sql) or pg_restore (for .dump)

---

🔄 Step 1: Copy the Backup File into the Container

PostgreSQL supports two common backup formats:

• Plain SQL format (.sql) — human-readable and restored with psql
• Custom format (.dump) — flexible and used with pg_restore

Example backup files:

• Download periodic_table.sql
• Download periodic_table.dump

Copy files into the container (replace CONTAINER_ID with your actual container ID from docker ps):

bashCopy
docker cp ~/Downloads/periodic_table.sql CONTAINER_ID:/tmp/
docker cp ~/Downloads/periodic_table.dump CONTAINER_ID:/tmp/

Inside the container:

bashCopy
cd /tmp
ls

You should see both files listed.

---

🏗️ Step 2: Create the Databases

Let’s create two databases — one for each format.

bashCopy
createdb -U postgres periodic_table_sql_format_db && echo "Created SQL format DB"
createdb -U postgres periodic_table_dump_format_db && echo "Created Dump format DB"

---

📦 Step 3: Restore the Database

Restore .sql File with psql

bashCopy
psql -U postgres -d periodic_table_sql_format_db -f /tmp/periodic_table.sql

Restore .dump File with pg_restore

bashCopy
pg_restore -U postgres -d periodic_table_dump_format_db /tmp/periodic_table.dump --verbose

---

✅ Verifying the Restoration

You can use a GUI tool like DBeaver or TablePlus, or stick to the terminal:

Connect to the restored database:

bashCopy
psql -U postgres -d periodic_table_dump_format_db

List all tables:

bashCopy
\dt

View sample data:

bashCopy
SELECT * FROM periodic_table LIMIT 10;

---

🎉 Final Thoughts

You’ve now learned how to:

• Start PostgreSQL with Docker
• Access the shell and tools inside a container
• Restore both .sql and .dump backups using psql and pg_restore

This is especially useful for developers debugging production data, setting up test environments, or learning PostgreSQL locally without installing it on the host machine.

Feel free to bookmark this guide or share it with someone who’s just getting started with PostgreSQL and Docker!