CodeX
Published in

CodeX

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!

--

--

--

Everything connected with Tech & Code. Follow to join our 900K+ monthly readers

Recommended from Medium

Creating an API endpoint in Meteor/Vulcan

Learning Notes on Designing Data-Intensive Applications (viii)

Python Abstract Base Classes for Containers and Container Data Types

Install Kali Linux GUI win-KeX in windows WSL 2

Lisp, Smalltalk, and the Power of Symmetry

Apa itu Big O Notation

Quintics 101 — How to get your Binance API key?

Mini Project : Colour Detection using Pandas & OpenCV

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Andrew K.

Andrew K.

Backend Software Engineer

More from Medium

How To Debug Docker Build

Multi-containerized Django app with PostgreSQL and graphQl : A Starter template that works.

Webapp + Nginx and SSL in Docker Compose

How to insert markdowns in your FastAPI documentation