Backup/Restore a dockerized PostgreSQL database
backup, database, docker, postgresql
Solution
Backup your databases
docker exec -t your-db-container pg_dumpall -c -U postgres > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql
Creates filename like `dump_2023-12-25_09_15_26.sql`
If you want a smaller file size, use gzip:
docker exec -t your-db-container pg_dumpall -c -U postgres | gzip > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql.gz
If you want even smaller file sizes use `brotli` or `bzip2`:
docker exec -t your-db-container pg_dumpall -c -U postgres | brotli --best > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql.br
or
docker exec -t your-db-container pg_dumpall -c -U postgres | bzip2 --best > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql.bz2
Restore your databases
cat your_dump.sql | docker exec -i your-db-container psql -U postgres
Problem
I'm trying to backup/restore a PostgreSQL database as is explained on the Docker website, but the data is not restored. The volumes used by the database image are: ``` VOLUME ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"] ``` and the CMD is: ``` CMD ["/usr/lib/postgresql/9.3/bin/postgres", "-D", "/var/lib/postgresql/9.3/main", "-c", "config_file=/etc/postgresql/9.3/main/postgresql.conf"] ``` I create the DB container with this command: ``` docker run -it --name "$DB_CONTAINER_NAME" -d "$DB_IMAGE_NAME" ``` Then I connect another container to insert some data manually: ``` docker run -it --rm --link "$DB_CONTAINER_NAME":db "$DB_IMAGE_NAME" sh -c 'exec bash' psql -d test -h $DB_PORT_5432_TCP_ADDR # insert some data in the db <CTRL-D> <CTRL-D> ``` The tar archive is then created: ``` $ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /etc/postgresql /var/log/postgresql /var/lib/postgresql ``` Now I remove the container used for the db and create another one, with the same name, and try to restore the data inserted before: ``` $ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar xvf /backup/backup.tar ``` But the tables are empty, why is the data not properly restored ?