How to persist and backup PostgreSQL Docker container.

Table of contents

• Introduction
• Persisting database information with a mounted volume
• Dumping and restoring database information

Introduction

Have you ever lose database information using a PostgreSQL docker container? We all know that we should never use a docker container to hold database data because when deleted, the data is gone with it, right? right???

OMG YOU DID.

And so did I. There was one time when I started learning how to use Docker containers. Docker containers are very handy and they are operating system independent. The world is covered with sunshine and rainbows. Shit happens when it comes to a database container.

At the time, I forgot to think about where the container holds its information. I was once use a postgresql container as a database instance for my application. Before deploying for production, I thought I should restart this once to refresh it. (Idk why but yea…) I typed docker-compose down followed with docker-compose up.

It was a good 1 minute of thinking that everything was ready for production and Imma leave and watch Netflix. And then, a panic comes in. I opened my application and found out that all my tables, all my configurations were gone into oblivion. The result was I had to reconfigure everything. Took me quite some time to get it working again. Good thing is at least it didn’t happen during production.

In this article, I will show you how to persist data of a postgresql docker container and how to restore them.

Persisting PostgreSQL database information with a mounted volume

We’ll first create our database using docker-compose. Please copy the following script into a docker-compose file then run docker-compose up.

version: '3.1'
services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydb
    volumes:
       - ./data:/var/lib/postgresql/data

Please notice on the volumes block. You can see that we mounted a volume data/ in the host machine to /var/lib/postgresql/data directory of the postgresql container. This is a necessary step to do in order to persist data on the host machine. Because when the container is deleted, this directory will continue to exist.

After you start the container, you’ll see a data/ directory showed up in your host machine. This directory is where all postgresql information is.

$ ll
total 16
drwxr-xr-x  3 siraphob-wsl-ubuntu siraphob-wsl-ubuntu 4096 Nov 21 16:29 ./
drwxr-xr-x  4 siraphob-wsl-ubuntu siraphob-wsl-ubuntu 4096 Nov 21 16:26 ../
drwx------ 19                 999 root                4096 Nov 21 16:29 data/                <-- HERE
-rw-r--r--  1 siraphob-wsl-ubuntu siraphob-wsl-ubuntu  232 Nov 21 16:28 docker-compose.yml

Let’s test that our theory is correct. Shell into the database container by running the following command.

$ docker exec -it <your-postgres-container-id> bash

Once you’re inside the container, run the following command to connect to the postgresql console.

$ psql -d mydb -U myuser

Let’s create a table and insert some data.

CREATE TABLE IF NOT EXISTS accounts (
    id serial PRIMARY KEY,
    username VARCHAR (255) UNIQUE NOT NULL
);
INSERT INTO accounts(username) VALUES ('rick'), ('morty') RETURNING *;

If you select rows from the table accounts, you’ll see the following result.

mydb=# select * from accounts;
  1 | rick
  2 | morty

After that, exit from the the container. And run docker-compose down to stop the container and remove it. Technically, everything should be gone. But as you can see that the data/ directory still persists.

# stop and remove container
$ docker-compose down
Stopping pg-persist-ex_db_1 ... done
Removing pg-persist-ex_db_1 ... done
Removing network pg-persist-ex_default
# container is gone
$ docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
# data directory still persists
$ ls
data  docker-compose.yml

Next, run docker-compose up again to start the database container. If you shell into the container and log in into the postgresql console. You can see that your table data isn’t lost.

mydb=# \dt
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+--------
 public | accounts | table | myuser
(1 row)
mydb=# select * from accounts;
 id | username
----+----------
  1 | rick
  2 | morty
(2 rows)

And that’s how you persist database information of a docker-container.

Dumping and restoring PostgreSQL database information
Another way to backup database information is to dump it out. Database dump is an export utility that helps you export database meta-data and data rows into a file. The dump file could later be imported into a new database.

Dumping allows you to export only meta-data (schemas, tables, relationships) or both meta-data and data rows. In this case, I’m going to show you how to export all information from a database.

To backup your postgres database from a docker container, please run the following command.

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

If you open the dump file, scroll down and you’ll see that it has our database information.

# file opened in vim
179 CREATE TABLE public.accounts (
180     id integer NOT NULL,
181     username character varying(255) NOT NULL
182 );
183
184
185 ALTER TABLE public.accounts OWNER TO myuser;
...
216 --
217 -- Data for Name: accounts; Type: TABLE DATA; Schema: public; Owner: myuser
218 --
219
220 COPY public.accounts (id, username) FROM stdin;
221 1   rick
222 2   morty
223 \.

If you want to restore database information from a dump file, please run the following command.

$ cat your_dump.sql | docker exec -i <your-postgres-container-id> psql -U myuser

You could try this on your own on a fresh postgres container to see the effect.

And that’s it for today. Now you’ll never lose your database information again. Happy coding!

Comments

[Tobias Nickel]

thanks, this is good practise

[Siraphob Kan]

Thank you!