How to Backup and Restore a PostgreSQL Database Using Docker

A client came to us needing to restore a PostgreSQL database without a recent backup to restore from. This is the backup and restore procedure we put in place afterwards.

Prerequisites

• Docker installed on your system
• A PostgreSQL database running in a Docker container
• Basic knowledge of PostgreSQL and Docker commands

Step 1: Backup Your PostgreSQL Database

Backing up a PostgreSQL database involves using the pg_dump utility, which creates a logical backup of your database. Here’s how to do it with Docker:

1. Identify Your Container: First, find the container ID or name of your running PostgreSQL instance.

   docker ps
   

2. Run the pg_dump Command: Use the docker exec command to run pg_dump inside the PostgreSQL container. Replace your_container_name with your actual container name, and your_database with the name of your database.

   docker exec -t your_container_name pg_dump -U postgres -F c your_database > /path/to/backup/your_database_backup.sql
   

   This command creates a custom-format dump file, which is a recommended practice for backups.

3. Verify the Backup: Check the backup file to ensure it has been created successfully.

   ls /path/to/backup
   

Step 2: Restore Your PostgreSQL Database

Restoring a PostgreSQL database from a backup involves using the pg_restore utility. Here’s how to restore your database:

1. Create a New Database: Before restoring, you need to create a new database in which the data will be restored. Access the PostgreSQL container:

   docker exec -it your_container_name psql -U postgres
   

   Create the new database:

   CREATE DATABASE your_new_database;
   

2. Run the pg_restore Command: Use the docker exec command to run pg_restore inside the PostgreSQL container. Replace your_new_database with the name of the new database.

   docker exec -i your_container_name pg_restore -U postgres -d your_new_database /path/to/backup/your_database_backup.sql
   

3. Verify the Restoration: Check the new database to ensure that the data has been restored correctly. Access the PostgreSQL container and list the tables:

   docker exec -it your_container_name psql -U postgres -d your_new_database \dt
   

Handling Common Issues

During the restoration process, you might encounter errors such as conflicts with existing schemas or sequences. Here are a few tips to handle such issues:

1. Use the --clean and --if-exists options. These drop existing objects before recreating them, which avoids the conflict entirely.

   pg_restore --clean --if-exists -U postgres -d your_new_database /path/to/backup/your_database_backup.sql
   

2. Drop the conflicting objects by hand. If only a few specific objects are causing problems, remove them before rerunning the restore.

   DROP SCHEMA IF EXISTS schema_name CASCADE; DROP SEQUENCE IF EXISTS sequence_name;
   

One thing worth doing before you need it

Run the restore once, against a throwaway database, before you are relying on it. An untested backup is a file, not a recovery plan, and the time to find that out is not the day you need it.

Full options are in the PostgreSQL backup documentation.