Dockerize & Backup A Postgres Database
Tags: web, docker, postgres
Published: 2021-11-17 01:06:57

---
title: Dockerize & Backup A Postgres Database 
published: true
description: Launch, use, backup and restore a dockerized Postgres database. 
tags: web, docker, postgres 
cover_image: https://i.ibb.co/vVFB495/white-church-half.png
---

## What is this?

This is a technical guide for doing common management tasks with a Postgres database running in Docker.

## Why make this?

As a quick reference for taking database backups and restoring them.

## Steps

### Run The Postgres Image

Docker makes it incredibly easy to get going with a Postgres database with persistent storage. Let's try it out.

- Let's just run the Postgres container pointing its volume location to a spot on disk we like. For me that's: _~/docker/volumes/postgres_.

```shell
# In your terminal
docker run \
 --name pg-db \
 -e POSTGRES_PASSWORD=shiny_metal_pass \
 -d \
 -p 6543:5432 \
 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data \
 postgres
```

- Let's dissect this line by line:
   - `docker run` -- Runs a specified image (in our case it's `postgres`, see last line of command). [Source](https://docs.docker.com/engine/reference/run/)
   - `--name pg-db` -- Sets the name of the container running the postgres image. [Source](https://docs.docker.com/engine/reference/run/#name---name)
   - `-e POSTGRES_PASSWORD=shiny_metal_pass` -- Sets an environment variable which will be the default password for the superuser. Note: This should be a secret 🤫. [Source](https://hub.docker.com/_/postgres)
   - `-d` -- Detach from the terminal instance. i.e. run in the background.
   - `-p 6543:5432` -- Map the local machine's port `6543` to the container's port `5432`. So from our machine to connect we will use 6543 which in turn will hit the port in the container running on 5432.
   - `-v $HOME/docker/volumes/postgres:/var/lib/postgresql/data` -- Map any data created in the container at `/var/lib/postgresql/data` to a desired location on local disk: `$HOME/docker/volumes/postgres`. [Source](https://hub.docker.com/_/postgres)
   - `postgres` -- The name of the image in Docker hub we want to create a container from. 

- When I run it I see the following

![create-container](https://i.ibb.co/2Y63BN4/dt-pgdb-001-create-container.png)

- When I check our volume location (note: for me this was an empty location prior to invoking the command above) I see this:

![check-mount](https://i.ibb.co/S5m3Bh1/dt-pgdb-002-check-mount.png)

- Finally, let's see if we can connect to our database. I am using [TablePlus](https://tableplus.com/) but the same concepts apply with any general purpose database IDE or `psql` ([found here](https://www.postgresql.org/docs/13/app-psql.html)). For me the connection looks like this:

![connect-sql](https://i.ibb.co/tx1DZZr/dt-pgdb-003-connect-sql.png)

- And when we connect we can run the following to get a lay of the land. 

```sql
-- From a SQL Editor (PSQL / TablePlus Session)
SELECT datname FROM pg_database;
-- RESULTS:
-- postgres
-- template1
-- template0
```

### Create Some Persistent Data

So, we've got a database! What now? __Well let's store some data in it!__

- Start by creating a new database called `todo_it_well`. You guessed it this, YATA (_yet another todo app_). This time we are doing it in SQL later we will also show you how to use the `createdb` tool.

```sql
-- From a SQL Editor
CREATE DATABASE todo_it_well;
-- Query 1 OK: CREATE DATABASE
```

- Switch to our new database. For me, in TablePlus, this just means editing my connection to use `todo_it_well` instead of `postgres` for the chosen database.

![connect-todo-sql](https://i.ibb.co/Ldt7ybj/dt-pgdb-004-connect-todo-sql.png)

- Next, create some tables. We will, for this demonstration, create a `users` table and `tasks` table so that users can track their tasks or "todos". The only _almost_ complex bit of this is declaring a relationship between tasks and users. This post is not about SQL so don't think too hard on these commands for now just know that they make a viable todo application.

```sql
-- From a SQL Editor
CREATE TABLE "user" (
   id SERIAL PRIMARY KEY NOT NULL,
   created_at TIMESTAMP NOT NULL DEFAULT NOW(),
   updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
   email TEXT NOT NULL,
   "password" TEXT NOT NULL,
   display_name TEXT
);

CREATE TABLE "task" (
   id SERIAL PRIMARY KEY NOT NULL,
   created_at TIMESTAMP NOT NULL DEFAULT NOW(),
   updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
   owner_id INT NOT NULL,
   description TEXT NOT NULL,
   label TEXT,
   completed_on TIMESTAMP,
   removed_on TIMESTAMP,
   CONSTRAINT fk_owner 
   	FOREIGN KEY(owner_id) 
   	REFERENCES "user"(id)
);
-- Query 1 OK: CREATE TABLE
-- Query 2 OK: CREATE TABLE
```

- Before we finish this step let's create some real data in here! Let's insert some users first and then some tasks for them!

```sql
-- From a SQL Editor
INSERT INTO "user"
  ("email", "password", "display_name")
VALUES 
  ('matt@email.fun', 'password-gasworld', 'matt-the-magic-monkey'),
  ('jennie@yahoo.fun', 'password-sassworld', 'dat-girl-jen'),
  ('timothy24@email.fun', 'pw-vw', 'yo-boi-tom-o-tee');
-- users created with ids: 1, 2 and 3
```

- Next, the tasks:

```sql
-- From a SQL Editor
INSERT INTO "task"
   ("owner_id", "description", "label")
VALUES 
   (1, 'read this awesome post', 'tech'),
   (1, 'do dishes', NULL),
   (2, 'read chapter of lotr', 'lit'),
   (2, 'sign up to volunteer', NULL),
   (3, 'call brother', NULL),
   (3, 'make grocery list', 'health'),
   (3, 'rake leaves', 'house');
```

- Validate that our data looks as expected:

```sql
-- From a SQL Editor
SELECT 
   u.display_name, 
   t.description
FROM 
   "user" u 
INNER JOIN
   "task" t ON u.id = t.owner_id
WHERE 
   u.id = 2
   AND t.completed_on IS NULL;

-- RESULTS:
-- display_name description
-- dat-girl-jen	read chapter of lotr
-- dat-girl-jen	sign up to volunteer
```

### Create A Backup

So here we are with some valuable customer data that we __really__ do not wanna lose. A good practice for any developer is to take regular (hopefully automated) database backups. Doing this in Postgres isn't too hard so let's get to it. I should state there are several ways to do this. This is just one of many ways to get this job done. I _think_ it is the most straightforward way; though, there are definitely faster ways.

This approach consists of issuing a set of commands to the container that could likely be CRON _job-ified_ without too much effort.

- First let's create a _backups_ directory at a spot that we share with our container's filesystem and our host machine:

```sh
# In your terminal
docker exec -t pg-db bash -c 'mkdir /var/lib/postgresql/data/backups'
```

- This command (run from the host machine) executes a command (`-c`) `mkdir` on the container and creates a directory called _backups_ which is located at the spot on the container that is shared with the host machine. You can verify this on the host by running:

```sh
# In your terminal
ls $HOME/docker/volumes/postgres | grep backups
# my machine outputs: backups
```

- Next, let's create our backup

```sh
# In your terminal
docker exec -t pg-db bash -c 'pg_dump todo_it_well -U postgres  --file=/var/lib/postgresql/data/backups/todo_it_well_db-$(date +%Y-%m-%d).sql'
```

Let's breakdown this command:

- `docker exec -t pg-db bash -c`
   - We tell Docker we want to run a command on the `pg-db` container
- `pg_dump todo_it_well -U postgres`
   - We invoke the `pg_dump` tool on the container telling it to target the `todo_it_well` db and run commands as the `postgres` user.
- `--file=/var/lib/postgresql/data/backups/todo_it_well_db-$(date +%Y-%m-%d).sql`
   - This is the spot on the container we want to place our back up at and as you can see it is the shared backups directory we just made. There is a little `date` logic that interpolates the current date as YEAR-MONTH-DAY.    

- If this went as planned we should have made our database backup and we can verify it on our host machine by running:

```sh
# In your terminal
ls $HOME/docker/volumes/postgres/backups
# my machine outputs: todo_it_well_db-2021-11-07.sql
cat $HOME/docker/volumes/postgres/backups/todo_it_well_db-2021-11-07.sql
# output includes: -- PostgreSQL database dump
```

- If you see some SQL in your output then __congratulations__! You have successfully created a database backup and your data strategy for your awesome app is just the tiniest bit more durable. 🎉


### Drop Data And Restore It

Great, so our data has been backed up! This means that you can restore folks back to a particular point in time when things were as they should be. So long as you have an application that can reliably serve this data (ruminates on articles using [Goose](https://github.com/pressly/goose) to accomplish database versioning) you can restore functionality and value to your users. 

BUT, backing up your data is only half the battle. Actually restoring it is another matter. Let's practice the act of restoring our data. 

First, I should confess that this article commits a sin of simplicity in that I wanted to get going as simply as possible. You more than likely __should not__ place your backups in the exact same spot as the rest of your mounted volume data, but it was useful in getting us going faster. 

- So to start, we are going to put our backup temporarily out of this spot so that we can _pretend_ we had mounted two volumes:   

```sh
# In your terminal
# Let's move our data to our home directory and pretend this is where we safely mounted our backups when starting the container, ok?
cp \
$HOME/docker/volumes/postgres/backups/todo_it_well_db-2021-11-07.sql \
$HOME/todo_it_well_db-2021-11-07.sql
```

- Next, verify that our data is where we expect it to be:

```sh
# In your terminal
ls $HOME | grep todo_it_well_db-2021-11-07.sql
# my machine outputs todo_it_well_db-2021-11-07.sql
```

- Ok cool, we are _pretending_ the we have a spot outside the normal Postgres files where we keep our data safely backed up. __Now, the fun part.__  OMG! We just got __attacked__ 😰. All records were destroyed by the evil hacker! Let's stop our container and delete all of our persistent data.

```sh
# In your terminal
# Stop the container forcibly
docker rm -f pg-db
# Delete our persistent data
rm -rf $HOME/docker/volumes/postgres
``` 

- Verify the data is indeed gone.

```sh
# In your terminal
ls $HOME/docker/volumes/postgres
# my machine outputs: No such file or directory
```

- Now we have discovered the attack and want to bring our application back online. So we start up our container as before:

```shell
# In your terminal
docker run \
 --name pg-db \
 -e POSTGRES_PASSWORD=shiny_metal_pass \
 -d \
 -p 6543:5432 \
 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data \
 postgres
```

- However, sadly, our data, including our database, is gone and we cannot connect to the __todo_it_well__ database. 

![connect-fail-sql](https://i.ibb.co/CvmWsXc/dt-pgdb-005-connect-fail-sql.png)

- __Fear not__, 🦸‍♀️ we have a backup! Simply copy the backup into the container:

```sh
# In your terminal
docker cp $HOME/todo_it_well_db-2021-11-07.sql pg-db:/var
```
- Next, you will need to recreate the database (so your backup data has a place to restore to):

```sh
# In your terminal
docker exec -t pg-db bash -c "createdb \
-h localhost \
-p 5432 \
-U postgres \
todo_it_well"
```

- Finally, run the `psql` SQL file command and take a deep breath, you are recovering from a __very stressful attack__ 😉.

```sh
docker exec -t pg-db bash -c \
"psql \
-U postgres \
-d todo_it_well \
-f /var/todo_it_well_db-2021-11-07.sql"
```

- Lastly, let's verify that our data was properly restored by running our query from earlier:

```sql
-- From a SQL Editor
SELECT 
   u.display_name, 
   t.description
FROM 
   "user" u 
INNER JOIN
   "task" t ON u.id = t.owner_id
WHERE 
   u.id = 2
   AND t.completed_on IS NULL;

-- RESULTS
-- display_name description
-- dat-girl-jen	read chapter of lotr
-- dat-girl-jen	sign up to volunteer
```

There you have it! You have done a ton! You set up a dockerized Postgres DB, you created your schemas and planted some data in your database, you took a back up of the data and then restored the data from back up after losing it. You rock 🎸. 

## Closing Thoughts

There are some issues with our set up and certainly there are some ease of use and durability improvements we could make, but this is a fully working solution. Some of the areas we should checkout:

- Custom Dockerfile that includes a backups location.
- A run command that references the previously mentioned backups location mount. 
- A cron job run on the host or on the container that creates backups.
- A secure, but accessible spot for these backups to stored. Maybe DigitalOcean Spaces, why not 🦀?

## Resources

- https://graspingtech.com/docker-compose-postgresql/
- https://simkimsia.com/how-to-restore-database-dumps-for-postgres-in-docker-container/
- https://stackoverflow.com/questions/40632228/input-file-appears-to-be-a-text-format-dump-please-use-psql
- https://www.tutorialspoint.com/postgresql/postgresql_create_database.htm