postgres_bck_restore.md

Backup database

# Connect to contaner shell
docker exec -it container_name /bin/bash
# create backup tar file
pg_dump -U postgres -W -F t postgres > /bb_backup.tar
# empty password
# copy file from container
docker cp container_name:file_name.tar .  # note . at the end for file location

One Liner

docker exec -it <container_name> pg_dump -U<user_name> --column-inserts --data-only <db_name> > backup.sql

Backup Example

# Connect to contaner shell
docker exec -it babybuddy_db /bin/bash
# create backup tar file
pg_dump -U postgres -W -F t postgres > /bb_backup.tar
# empty password
# copy file from container
docker cp babybuddy_db:/bb_backup.tar .

Restore database

# copy file to container
docker cp file_name.tar container_name:/.  # note . at the end for file location
# Connect to container shell
docker exec -it container_name /bin/bash
# restore db
pg_restore --dbname=postgres -U postgres --create --verbose /file_name.tar

Restore Example

# copy file to container
docker cp bb_backup.tar container_name:/.
# Connect to container shell
docker exec -it db_test /bin/bash
# restore db
pg_restore --dbname=postgres -U postgres --create --verbose /bb_backup.tar

Backup / Restore Method 2

# backup
docker exec -t your-db-container pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql
#restore
cat your_dump.sql | docker exec -i your-db-container psql -U postgres