In one of a previous article we learned about how to locally backup Postgres database using
pg_dump tool. That works great when you are taking backups directly from a local instance or a remote instance.
But what if you are running your database in a container? How do you backup your database then? In this article, we will learn how to backup PostgreSQL database in Docker/Podman containers safely. We will also learn how to restore the database from the backup.
A docker or podman container running a PostgreSQL database. If you don’t have one, you can create one using the following command:
Note: I am writing all the commands in terms of
dockerhowever you can replace it with
podmanif you are using podman since the commands are almost the same.
First thing to do is to stop all the services which would be connecting to this database and doing any read/write operations. This is to ensure that the database is in a consistent state when we take the backup.
my_app with the name(s) of your application(s) which are connecting to the database.
Now we will use the
pg_dump tool to take the backup of the database by using
docker exec command.
my_db with the name of your database. This will create a file named
my_db.bak in the current directory.
Now, we have the backup file created but it is inside the container, we need to copy it to our local machine. We can do that using the
docker cp command.
Optionally we can verify our backup file by reading it
It should have content similar to this
At this point we have a backup of our database in our local machine. We can now start our application(s) again.
A backup is only good if we can restore it if something goes wrong. So let’s learn how we can restore our database from the backup we just created.
Once again, we will first stop all the services which are connecting to the database.
Now let’s say we have a new container which is running PostgreSQL, so first thing would be to create a new database in that container.
Once we have the database created, we will copy our backup file into the container and start the restore process.
This will copy the backup file in the container, now we will use
psql tool to restore the database.
This will restore the database from the backup file. We can verify that by connecting to the database and checking the tables.
This will open the
psql prompt, now we can check the tables by using
This will show us all the tables in the database.
We can now start our application(s) again.
Whenever we are working with multiple commands that are likely to be used by us again and again, it is a good idea to create a script for them.
If you are an old reader here, you might have seen in some other blogs, we try to convert our steps into a single script. So let’s create a script which will take care of all the steps we learned above.
And let’s create another script for restoring the database.
This way we can easily backup and restore our database without worrying about the commands. Now hook this up with a cron job or systemd timer and you have a fully automated periodic backup system.
Once again this is just the very basic script, you can modify and go crazy with it as much as you want.
For example what I personally prefer to do is to encrypt the backups with my gpg key and then upload them to my cloud storage. This way I can be sure that the backups are safe and secure. But this is optional and hence I haven’t included it in the script.
Let me know if you are interested in learning how to do that and I will write a separate article for that.
While in this article we learned how to backup PostgreSQL database in a containered environment, the fundamental approach remains the same for other databases as well.
So if you are using MySQL or MongoDB or any other database, you just need to replace the commands with the ones for your database and you are good to go.
To summarise it, these are the pointers we need to keep in mind while taking backups of databases in containers:
- Stop all the services which are connecting to the database.
- Take the backup of the database using the preferred tool for your database.
- Copy the backup file to local machine using
- Start all the services again.
In this article, we learned how to safely backup PostgreSQL database in Docker/Podman containers. We also learned how to restore the database from the backup.
While learning this, we also created some handy scripts which we can use to backup and restore our database without worrying about the commands.
I hope you found this article useful. If you have any questions or feedback, please feel free to reach out to me on X / Twitter ↗️.
Until next time 👋.