How to Backup and Restore your Dockerized Postgres Database

Photos via Pexels

Introduction

With the increasing amount of company’s data, the task of protecting it becomes more challenging, especially in the cloud. As a result, the demand for reliable backup and recovery solutions has never been greater.

According to IBM, Backup and restore refers to technologies and practices for making periodic copies of data to a separate, secondary device and then using those copies to recover the critical company’s data in cases where data is lost or damaged due to different events such as power outage, cyberattack, human error or disaster.

The aim behind this article is to explain how to backup a Postgres database to an S3 Object Storage using backup scripts provided by the Postgres wiki and the s3fs-fuse project explained in my previous article. It also explains how to restore your database once its is backed up.

Postgres Database using Docker

Based on Docker’s official documentation. Docker is defined as an open platform for developing, shipping, and running applications. It provides the ability to package and run an application in an isolated environment called a container.

A container is a standard unit of software that packages up code and all its dependencies so the application runs quickly and reliably from one computing environment to another. A Docker container image is a lightweight, standalone, executable package of software that includes everything needed to run an application.

To install Docker, follow these steps for Ubuntu:

1. Install Docker Engine
2. Install Docker Compose
3. Setup Post-installation steps

Once this is done, you can create sudo mkdir /opt/docker-compose/; directory and create the docker-compose.yml file. In this file put the following :

Here, i have decided to define a postgres database image related to the Camunda platform, in that way, all the demo data available in the camunda platform will be stored on our postgres database. In such case, our initial database won’t be empty so we can start working with it directly.

Then, in the same /opt/docker-compose directory run the following command to create and run your containers:

In order to view the data stored in the database, you can use the CLI or DBeaver.

Option 1 : DBeaver

Install DBeaver as follows:

DBeaver installation

Then configure it to access your local postgres database.

DBeaver.

Finally, you can access you tables and view camunda’s data.

Option 2 : CLI

Access your postgres database using the following command:

docker exec -it postgres psql -U camunda

Then run \dtcommand to show all of the available tables.

Postgres Backup Scripts

As defined in the official postgres wiki. i will define two main files : pg_backup.config and pg_backup_rotated.sh.

pg_backup.config

Make sure to correctly specify :

BACKUP_DIR: where your backup will be stored

USERNAME: the username of your database (i.e. camunda)

pg_backup_rotated.sh

In order to execute the above backup scripts, run the following :

./opt/docker-compose/pg_backup_rotated.sh

Once this is done, you can access your backup directory to view your postgres backup.

If you want your backup to be stored on the Cloud, on an S3 Object Storage, you can check out my article on Mounting your Object Storage Bucket as a File System on your ECS Instance. Then, you need to adapt the pg_backup.config file to set the BACKUP_DIR directory to your mounted S3 bucket.

In order to program you backup to be performed every day at a specific time you can usecrontab -e and add the following line :

00 00 * * 1 cd /opt/docker-compose; /opt/docker-compose/pg_backup_rotated.sh -c /opt/docker-compose/pg_backup.config

Following this line, our database will be bucked up every monday at midnight.

Restore your Postgres Database

Our database backup will be stored in /backup/ as camunda.sql.gz . First, you need to unzip it which will give you camunda.sql .

gzip -d camunda.sql.gz

Then under /opt/docker-compose/ create initdb.d directory and put camunda.sql within it. Then, create a new postgres database

Make sure that the older postgres and camunda created in the previous docker-compose.yml are down or down and removed.

# If you want to stop and remove your stopped containers: 
docker stop $(docker ps -a -q) && docker rm $(docker ps -a -q)

Launch the new postgres container

docker-compose up -d 

At the creation process of the postgres container, the docker-entrypoint-initdb.d will be executed to populate the database and it contains the mounted camunda.sql .

Access your postgres database :

docker exec -it postgres psql -U camunda

Run \dt to verify that you have backed up all your lost tables.

Finally, you can run the following commands to stop and remove your docker containers for this tutorial :

docker stop $(docker ps -a -q) && docker rm $(docker ps -a -q) && docker ps 

References :

Automated Backup on Linux

How To Backup and Restore PostgreSQL Database using pg_dump and pg_restore

Backup, restore postgres in docker container

Database in a Docker container — how to start and what’s it about