How to persist and backup data of a PostgreSQL Docker container

Never lose your database information ever again!

Table of contents

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

Introduction

Have you ever lost 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. At the time, I forgot to think about where the container holds its information. I was once using 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. 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. The 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.

# docker-compose.yml
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 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 showing up in your host machine. This directory is where all PostgreSQL information is.

$ ll
total 16
drwxr-xr-x  3 ubuntu ubuntu 4096 Nov 21 16:29 ./
drwxr-xr-x  4 ubuntu ubuntu 4096 Nov 21 16:26 ../
drwx------ 19    999 root   4096 Nov 21 16:29 data/     
-rw-r--r--  1 ubuntu 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 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 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 login 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. A 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!