Performing Backup and Restore of PostgreSQL Database Running in a Docker Container

Overview

Learn how to perform backup and restore of your PostgreSQL database in Docker containers using shell commands.

Solution

$containerName = "my_postgres_container";
$dbUser = "postgres_user";
$database = "my_database";

docker exec -t $containerName pg_dumpall -c -U $dbUser > database-dump.sql

# stop the container and remove the data volume
# start the container normally

cat database-dump.sql | docker exec -i $containerName psql -U $dbUser -d $database

Detailed Explanation

Variables

Make sure you have the following variables set in your shell script or terminal:

  • $containerName The name of your Docker container where the PostgreSQL database is running.
  • $dbUser The username used to connect to your PostgreSQL database.
  • $database The name of your PostgreSQL database.

Step 1: Backup the PostgreSQL Database

To backup your PostgreSQL database, use the docker exec command. This command runs the pg_dumpall utility inside a running container to export all databases managed by PostgreSQL. The -c option adds commands to clean (drop) the databases before recreating them, and -U specifies the username.

docker exec -t $containerName pg_dumpall -c -U $dbUser > database-dump.sql

This command will create a dump file called database-dump.sql on the host machine, containing all the SQL commands needed to reconstruct the database.

Step 2: Restore the PostgreSQL Database

Before restoring the database, make sure the Docker container is running. If you have stopped or removed the container, start it normally and ensure the data volume is in place.

To restore the database from the dump file, pipe the contents of database-dump.sql to the PostgreSQL command-line utility psql using the following command:

cat database-dump.sql | docker exec -i $containerName psql -U $dbUser -d $database

This command sends the dump file to the psql utility, which executes the SQL commands and restores the database schema and data.

Additional Notes

While using these commands, ensure your Docker container has access to PostgreSQL client tools and that your user has the necessary permissions to perform database backups and restores.