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 adventureworks

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

Backup Entire Postgres Server


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

Backup Specific Postgres Database


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

docker exec -t postgres \
    pg_dumpall -c -U postgres \
    -W -F t adventureworks \
    > dump_adventureworks`date +%d-%m-%Y"_"%H_%M_%S`.sql

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


docker exec -i postgres /usr/bin/pg_dumpall \
 -U <postgresql_user> > postgres-backup.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

Connect to new db and check

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

# List Databases
\l

# Connect Database
\c pagila

# List Tables
\d

# Look inside one table
SELECT * FROM actor;

Kill the databases


docker-compose down
docker-compose -f postgres2.yml down

Sources