Gaurav Koley Internet Lurker, Poet, Creator of Gratia, Actively and Pdfvuer

Backup and restore Postgresql database running on docker

I use docker a lot, so much so that I even run PostgreSQL and MySQL on docker rather than having them installed locally. This makes much more sense when you have a local and production setup like mine where both run around docker. And so, every once in a while, I encounter a situation where I have to backup my Postgres databases.

I use the standard official postgres docker image from Docker Hub.

This image comes with two handy tools (pg_dump and psql) which let us take easy backups and restore them with equal ease.

To backup, we use the pg_dump tool:

docker exec <postgres_container_name> pg_dump -U postgres <database_name> > backup.sql

This would create a text file named backup.sql containing all the data and schema of your database. You can then import this data back into postgres using the psql tool:

docker exec -i <postgres_container_name> psql -U postgres -d <database_name> < backup.sql

The -i flag is of particular importance here because the psql tool needs to be run interactively for it to be able to read from the backup.sql file.

Note: The above commands assume that you have postgres as the default user for the database. This is indeed the case for the standard postgres docker image.