How to persist and backup data of a PostgreSQL Docker container
Never lose your database information ever again!
Table of contents
- Persisting database information with a mounted volume
- Dumping and restoring database information
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
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.
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
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.
List of relations
Schema | Name | Type | Owner
public | accounts | table | myuser
mydb=# select * from accounts;
id | username
1 | rick
2 | morty
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
185 ALTER TABLE public.accounts OWNER TO myuser;
217 -- Data for Name: accounts; Type: TABLE DATA; Schema: public; Owner: myuser
220 COPY public.accounts (id, username) FROM stdin;
221 1 rick
222 2 morty
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!