Restore PostgreSQL database in Docker container
- 25 Apr 2018: Update image URLs (diff)
I have a backup file
~/tmp/backup.sql which was created using
pg_dump from a remote Postgres server. I now want to verify that the backup actually works, and that it can be imported into a new database.
Create a PostgreSQL server container, and mount the current folder containing the database backup.
$ cd ~/tmp/backup.sql $ docker run --rm -v $(pwd):/backup postgres
Still on the host machine, execute commands on the container with
docker exec. I created the dump without
CREATE DATABASE statement, so I will have to create a new target database first. Find the name of the new container with
docker ls. In this example, the name is determined_pare.
$ docker exec determined_pare psql -U postgres -c "CREATE DATABASE newdb"
You can verify that the database was created by listing all available databases:
$ docker exec determined_pare psql -U postgres -l
Restore the backed up database to the new target database using the
.sql file which was mounted earlier at
$ docker exec determined_pare psql -U postgres -d newdb -f /backup/backup.sql
Verify that the tables were indeed created
$ docker exec determined_pare psql -U postgres -d newdb -c "\dt"
I have also written a list of useful PostgreSQL commands that I want to remember.