Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Tiny script to backup and restore database with Docker
#!/usr/bin/env bash
echo "BACKUPING..."
docker run -i -e PGPASSWORD=<password> --rm postgres /usr/bin/pg_dump -p <port> -h <ip> -U <user> <db> | gzip -9 > backup.sql.gz
echo "BACKUPING...[END]"
if [ -n "$1" ] ; then
where="where pg_stat_activity.datname = '<db>' AND pid <> pg_backend_pid();"
fi
cat <<-EOF | docker exec -i <container_db_local> psql -h 127.0.0.1 -U <user_db> -d postgres
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
${where}
EOF
docker exec -i <container_db_local> psql -h 127.0.0.1 -U <user_db> -d postgres -c "drop database <db_local>"
docker exec -i <container_db_local> psql -h 127.0.0.1 -U <user_db> -d postgres -c "create database <db_local>"
docker exec -i <container_db_local> pg_restore -h 127.0.0.1 -U <user_db> -w -Fc -d <db_local> backup.sql.gz
rm -rf /scripts/backup.sql.gz
echo "RESTORED."
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment