Database in Docker system

In this guide, we will show how the Docker system can be used to run the database system. The goal of the tutorial is to learn how the file system and network connection work in the application container. At the end, you should be able to work with the database through a web interface running in another container.

Container image documentation

First, familiarize yourself with the container image on Docker Hub. See the documentation for PostgreSQL server image Docker.

The container image contains the installed database along with all dependencies including the operating system. The documentation should include basic information on how to use the image - how to configure it, where the important files are located and on what port the service is available. Sometimes it happens that in order to understand the details, you will have to look at the ``Dockerfile'' and the basic documentation for the software that is in the application container.

By studying the documentation, you will find that:

• the database runs on port 5432
• username is postgres
• you set the password with an environment variable, e.g. -e POSTGRES_PASSWORD=mysecretpassword

Starting the container with the database

Start your PostgreSql database.

docker run --name postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres:10.4-alpine

The -d switch means that the application will run in the background. The switch `--name' assigns a symbolic name to the container so that we don't always have to look for its identification string.

The -e switch sets an environment variable named POSTGRES_PASSWORD. The application can read it and set a password to access the database according to it. With the help of environment variables, we can also change other properties of the application. A list of variables to configure is provided in the image documentation.

We can verify that it is really running in the background using:

docker ps

Connecting to the database

You can connect to the database using the psql command line client

Run the psql command line client in the container:

docker exec -it postgres psql -U postgres

For fun, try some simple SQL statements:

CREATE TABLE partytable (id INTEGER PRIMARY KEY, content TEXT);
INSERT INTO partytable values(1,'knock knock');
SELECT * FROM partytable;

You close the client by pressing Ctrl+D.

You can write down SQL commands in a file and send them to the database using standard input:

cat schema.sql | docker exec -i postgres psql -U postgres

The state of the container is temporary

in a cloud environment, it can easily happen that the container crashes and is restarted. Restarting the container may make the original state (database files) unavailable.

Try shutting down the container and see if the created record is still there after restarting. You will find that the run command still creates a new instance that does not contain the changes from the original instance.

docker stop postgres
docker ps
docker run -e POSTGRES_PASSWORD=mysecretpassword --name postgres -d postgres:10.4-alpine
docker exec -it postgres psql -U postgres
psql> SELECT * FROM partytable;

Volumes in the application container

In order to preserve the content of the database even after the container is terminated, it is necessary to create a new volume.

A volume can be:

• any local file system,
• any local directory,
• a remote file system or directory, connected using a network protocol supported by the operating system.

When starting a container, it is possible to create a mapping between directories in containers and volumes. Thus, the data will not be written to the file system container but to the volume. In this way, we will avoid the fact that the data will not survive the restart of the container, because they will remain in the volume.

So that we don't have to think about how the directories are organized on the server where Docker is running, we can create a named volume.

A named volume is a special directory and its creation and deletion is handled by Docker. Otherwise, it's an ordinary directory.

You create a new volume named pgdata with the command:

docker volume create pgdata

A new volume will be created on the local disk (in Docker Machine) (usually located in /var/lib/docker/volumes/) where we can save what we want to survive the container restart.

The list of volumes can be obtained from Mrlook around with:

docker volume ls

If we get tired of the named volume, we can delete it:

docker volume rm pgdata

Volume mapping

In order to be able to use the volume in the container, we need to create a mapping. By mapping, we determine which directory in the file system of the container will correspond to the volume.

Volume mapping is done using the -v switch. We will introduce a special string with a mapping. The part before the colon is the name of the named volume or local directory, the part after the colon is the name of the directory in the container.

If a container is running that uses the volume, it cannot be deleted to prevent data loss.

If we forgot which container is using the volume, we use the command

docker inspect pgdata

Let's start a new container, which will already contain a mapping to a permanent volume.

docker run
     --name postgres \
     -v pgdata:/var/lib/postgresql/data \
     -e POSTGRES_PASSWORD=mysecretpassword \
     -d postgres:10.4-alpine

From the inside, the container will look exactly the same, but after a reboot, the contents of the database will be preserved.

Creating a network connection

Although communication with the application using the command line is simple, but it has some limitations. In order to fully utilize the container, we must be able to connect it to our network.

Not everyone is comfortable with a command line interface. This is certainly not our problem, but the users of our application will definitely appreciate the luxurious pgadmin graphical interface. Let's try the Postgres relational database management web application pgadmin for a demonstration.

There are multiple pgadmin images on Docker Hub. We will study the most used dpage/pgadmin4.

With Docker, it's delightfully easy to run and install:

docker pull dpage/pgadmin4
docker run -p 8080:80 \
     --name pgadmin \
     -e 'PGADMIN_DEFAULT_EMAIL=user@domain.com' \
     -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret' \
     -d dpage/pgadmin4

By PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD environment variables we will set the login name and password for accessing the web application. These data are only used to start the interface and have nothing to do with the databases that we can manage using the web application.

The application in the container under normal conditions is not visible to the outside world. We must explicitly map the port it uses to communicate to a real port on the machine where Docker is running (virtual machine Docker Machine).

With the -p switch, we said that the application using port 80 will be accessible on port 8080 of our Docker Machine. When specifying ports, care must be taken not to cause a collision - that a maximum of one service runs on one port.

Connecting to a running container

First, we need to find out what IP address the web application is running on.

• If you are using Docker Machine: Find out the IP address of Docker Machine (e.g. docker-machine ls)
• If you use Docker Desktop: If you want to connect to the container using a web browser, you must use the IP of your machine (localhost). If you want to connect using a different WSL machine, use the symbolic name host.docker.internal. If you don't know, you can also open a web browser using the Docker Desktop Dashboard.

In a luxurious graphical environment, it will probably be a piece of cake to connect to the `postgres' database server on port 5432.

On the initial screen, click "Add New Server". Fill in any connection name ("Name"). In the "Connection" tab, try filling in the "Host Name/Address" item - DNS name or IP address of the database server.

But what IP should I fill in?

Virtual networks

Using port mapping, it is possible to connect to a running container from the outside, but it is not possible between containers. And it wouldn't even be safe. Containers should be separated from each other as much as possible so that foreign processes cannot be easily accessed. We need to explicitly say which containers will see which.

A virtual network is used to determine groups of containers that will be network friends (they will be visible to each other and will be able to communicate with each other).

We will create a `pgnet' virtual network for the database and the interface in a similar way to the container:

docker network create pgnet

When starting the container, enter the name of the virtual network to which the container will belong.

All containers within one virtual network will be accessible to each other using their name. Docker takes care of that in every running the appropriate DNS record was created for the container so that we do not have to worry about specific virtual IP addresses.

Adding containers in virtual networks

We will add the container to the virtual network at startup. It will probably be necessary to start over.

Shut down the postgres database and the pgadmin client.

# Is running
docker ps
docker stop postgres
docker rm postgres
docker stop pgadmin
docker rm pgadmin
# The sky is gone
docker ps

Let's check the list of networks:

docker network ls

We add the --network parameter when the containers are started again.

docker run
     -p 8080:80 \
     --name pgadmin \
     --network pgnet \
     -e 'PGADMIN_DEFAULT_EMAIL=user@domain.com' \
     -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret' \
     -d dpage/pgadmin4
docker run
     --name postgres \
     --network pgnet \
     -e 'POSTGRES_PASSWORD=mysecretpassword' \
     -v pgdata:/var/lib/postgresql/data \
     -d postgres:10.4-alpine

On the virtual network it will look like this:

 +----------+                 +---------+
 | postgres | <-5432 pgnet -> | pgadmin | 80 <--> localhost 8080
 +----------+                 +---------+

Connection between containers

In the virtual network, the container will be accessible under the DNS name the same as the name of the container (switch --name). In case the container is connected to several virtual networks, DNS names are distinguished using a network name suffix, e.g. postgres.pgnet.

Configure pgadmin to connect to the database in the postgres container.

On the initial screen, click "Add New Server". Fill in any connection name ("Name"). In the "Connection" tab, try filling in the "Host Name/Address" item - DNS name or IP address of the database server.

• General - Name: Name of the connection
• Connection - Host: URL on which the database server is running. In this case it will be the name of the container (postgres) or the name of the network and the name of the container pgnet.postgres.
• Connection - Port: Internal port on the virtual network on which the database runs.
• Connection - Username: username to access the database.
• Connection - Password: password to access the database.

Use the pgadmin4 graphical client to view the contents of the postgres database.

Cancellation of virtual network

When we get tired of the network, we delete it with the command:

docker network rm pgnet

The network cannot be deleted as long as there are containers using it.

Create helper scripts

In the script, write down all the commands needed to prepare, start and end the application.

• prepare-app.sh
• start-app.sh
• stop-app.sh

A script can start with commands:

#!/bin/bash
set -e

Previous Post Next Post