This guide outlines the steps to create a PostgreSQL container using Docker, back up a database to a tar file, and restore the data both to the default PostgreSQL database and a custom database.
Run the following command to create a tar backup of the PostgreSQL database running inside a Docker container. Replace <container-name>, <mydbUser>, and <mydbName> with the appropriate values:
docker exec <container-name> pg_dump -U <mydbUser> -F t <mydbName> > mydb.tar
This creates a tar file (mydb.tar) containing the backup of your database.
Transfer the mydb.tar file to the machine where you will restore the database.
To create a PostgreSQL container, use the official PostgreSQL image from Docker Hub. Replace <container-name> with your desired container name:
docker run --name <container-name> -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres
This command runs a PostgreSQL container with port 5432 exposed and sets the POSTGRES_PASSWORD to postgres.
After creating the container, use the docker cp command to copy the database dump file (in tar format) into the container. Replace mydb.tar with the path to your dump file.
docker cp <mydb.tar> <container-name>:/
This command copies the tar dump file into the root directory of the PostgreSQL container.
Once the dump file is copied, access the container’s shell using docker exec:
docker exec -it <container-name> /bin/bash
This command opens an interactive bash shell session inside the running container.
To restore the data from the dump file into the default postgres database, use the pg_restore command. Replace mydb.tar with the name of the dump file inside the container.
pg_restore --clean --verbose -U postgres -d postgres ./mydb.tar
The --clean flag drops the existing objects before recreating them, and --verbose provides detailed output during the restoration process.
You can check if the data has been restored by accessing the PostgreSQL database through the psql shell:
docker exec -it <container-name> psql -U postgres
Once inside the psql shell, you can run SQL queries to inspect the restored data.
In addition to restoring the dump into the default postgres database, you can create and restore the dump into a custom database.
First, create a custom database inside the container using the psql shell:
docker exec -it <container-name> psql -U postgres -c "CREATE DATABASE <db_name>;"
Replace <db_name> with your custom database name.
Now, copy your database dump file (in tar format) into the container. Replace mydb.tar with the path to your tar file and <container-name> with the name of your container:
docker cp mydb.tar <container-name>:/
This command copies the mydb.tar file into the root directory of the container.
To manually interact with the custom database and perform the restoration, enter the container’s bash shell :
docker exec -it <container-name> /bin/bash
While inside the container's bash shell, run the pg_restore command to restore the dump into the custom database. Replace <db_name> with your custom database name and mydb.tar with the name of your tar file inside the container :
pg_restore --clean --verbose -U postgres -d <db_name> ./mydb.tar
This command will restore the data into the custom database.
docker exec -it <container-name> psql -U postgres -d <db_name>
You can now run SQL queries to ensure the data has been successfully restored.
Thanks for reading !