Skip to content

Docker Postgres with Backup and Restore

Run Postgres


mkdir postgres-docker-tutorial
cd postgres-docker-tutorial
wget https://gist.githubusercontent.com/dentropy/e408f86de7261a516af9bb43234ae343/raw/5b951082c5d22642377fa5d62d85a127b7adb93c/docker-compose.yml
cat docker-compose.yml
docker-compose up -d


e408f86de7261a516af9bb43234ae343

Create new databases

#!/bin/bash
docker exec -it postgres psql -U postgres

-- SQL
CREATE DATABASE pagila;
CREATE DATABASE Adventureworks;
CREATE DATABASE dvdrental;
\q

Load pagila database


git clone https://github.com/devrimgunduz/pagila.git
cat ./pagila/pagila-schema.sql | docker exec -i postgres psql -U postgres -d pagila
cat ./pagila/pagila-data.sql   | docker exec -i postgres psql -U postgres -d pagila

Load Adventureworks database


git clone https://github.com/lorint/AdventureWorks-for-Postgres.git
cat ./AdventureWorks-for-Postgres/install.sql | docker exec -i postgres psql -U postgres -d pagila

Load dvdrental database


wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
unzip dvdrental.zip
docker exec -i postgres pg_restore -U postgres -v -d dvdrental < ./dvdrental.tar

Generate Backups


docker exec -t postgres pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql
docker exec -t postgres pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql
docker exec -t postgres pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

Run Second Postgres Database


wget https://gist.githubusercontent.com/dentropy/e408f86de7261a516af9bb43234ae343/raw/5e764a89037921d5022f76963b516ba1fc133820/postgres2.yml
cat postgres2.yml
docker-compose -f postgres2.yml up -d


1ddd0147445d512c46b83a942f2302da

Restore your database


backup_file=$(ls dump* | head -1)
echo $backup_file
cat $backup_file | docker exec -i postgres2 psql -U postgres

Sources