Installing and Configuring PostgreSQL in Docker

Docker helps create an isolated environment for various services, including databases. In this article, we will describe how to install PostgreSQL with Docker, configure it, and set up a database connection.

Why should you run PostgreSQL in a Docker container?

The main advantage of containerization is that it creates an isolated environment that helps avoid any dependency conflicts.

Let's see what dependent packages are installed on a Debian server along with PostgreSQL.

We ordered a server from Hostman, connected to it via SSH, and now run this command to install PostgreSQL:

sudo apt install postgresql postgresql-contrib

When executing the command, pay attention to the lines at the beginning:

The following additional packages will be installed:
libllvm11 libpq5 libsensors-config libsensors5 libxslt1.1 libz3-4 postgresql-13 postgresql-client-13

Postgres download comes with six more libraries: libllvm11, libpq5, libsensors-config, libsensors5, libxslt1.1 and libz3-4.

Now let's look at the list of PostgreSQL 13 dependencies:

sudo apt-cache depends postgresql-13

We get a list of 25 dependencies, only one of which is recommended, the rest are required.

If there is no required package or we have the wrong version, it leads to conflicts. Postgres run in Docker helps eliminate most of the problems. All dependencies are stored inside the container. When we download the image, we get a fully compatible environment, ready to go.

This leads to another advantage: reproducibility. For example, a developer wants to test a new feature in a product. Using an isolated environment, he can deploy a working environment on a local machine in a few clicks. 

And these are not all the advantages of PostgreSQL in Docker. With containers, fault tolerance also increases. If the server stops working, you can run the container on another machine and not worry about configuring the parameters.

Installing PostgreSQL with a Docker Image

Let's see how PostgreSQL is installed on Ubuntu. First, let's install Docker.

Add a key to work with Docker Hub:

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

Add the repository to the list of local ones and install Docker:

sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt install docker-ce

Launch Docker and configure it to start at boot using the systemctl system utility:

sudo systemctl start docker
sudo systemctl enable docker

Next, create a Docker Postgres Volume, a directory for storing data.

mkdir -p $HOME/docker/volumes/postgres

Install the PostgreSQL image from Docker Hub using the docker pull command:

sudo docker pull postgres

PostgreSQL installation is complete.

Launching the container

Run the command:

sudo docker run --rm --name hostman-pgdocker -e POSTGRES_PASSWORD=hostman -e POSTGRES_USER=hostman -e POSTGRES_DB=hostman -d -p 5531:5531 -v $HOME/docker/volumes/postgres:/var/lib/ postgresql/datapostgres

Here we use the following flags:

• --rm tells the system to delete the container and its file system after the container is stopped. It helps saving server space.
• --name is the container name, which must be unique within one server, regardless of status.
• -e points to the environment variables: name and password of the superuser, default database name.
• -d launches the container in background (offline) mode. After the launch, control is returned to the user.
• -p binds the Postgres port to the server port.
• -v creates a mount point.

When executing the command, the error "Error starting userland proxy: listen tcp4 0.0.0.0:5531: bind: address already in use" may occur. In this case, check what process is occupying port 5531:

sudo ss -lptn' sport = :5531'

To terminate the process, use the kill command. Specify the PID of the process that occupies port 5531. In our case, the PID os 2592:

sudo kill 2592

After fixing the error, try to restart Docker:

sudo docker run --rm --name hostman-pgdocker -e POSTGRES_PASSWORD=hostman -e POSTGRES_USER=hostman -e POSTGRES_DB=hostman -d -p 5531:5531 -v $HOME/docker/volumes/postgres:/var/lib/ postgresql/datapostgres

However, the command is rarely used this way. It is much more convenient to store the configuration in a Dockerfile. There you can specify Docker ADD, CMD, LABEL, ENV and other instructions to be applied at startup.

Connecting to PostgreSQL

First, let's connect to the isolated environment using the psql utility. It is included in the postgresql-client. Run:

sudo apt install postgresql-client

Connect to the database and execute a test query:

psql -h 127.0.0.1 -U hostman -d hostman

The output should be:

Password for user hostman:
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1), server 13.2 (Debian 13.2-1.pgdg100+1))
Type "help" for help.
hostman=#

A prompt with a username indicates that the connection has been established correctly.

You can also connect to the container using docker exec with the following keys:

sudo docker exec -it hostman-pgdocker psql -U hostman

The result is the same: an invitation to enter queries.

psql (12.6 (Debian 13.2-1.pgdg100+1))
Type "help" for help.
hostman=#

In the command above we used the arguments:

• -i activates interactive work with the terminal.
• -t launches a pseudo-terminal.
• hostman-pogdocker is the name of the container to which queries will be made.
• psql launches the utility for connecting to the database.
• -U is a user name for connecting to the database.

To test the connection, let's create a new table, fill it with data and run the query:

hostman=# create table cities (name varchar(80));
CREATE TABLE
hostman=# insert into cities values ('Seattle');
INSERT 0 1
hostman=# select * from cities;
name
--------
Seattle
(1 row)

After completing the request, stop the container with the docker stop command:

sudo docker stop hostman-pgdocker

This is where we discover a security hole. The files and processes that the container creates are owned by the internal postgres user. Due to the lack of a namespace within the container, the UID and GID can be arbitrary.

The problem is that there may be other privileged users and groups on the server itself with the same UID and GID. This potentially leads to a situation where the user can access host directories and kill any processes. You can avoid this situation by specifying the USERMAP_UID and USERMAP_GID variables at startup.

Example command:

sudo docker run --rm --name hostman-pgdocker -e POSTGRES_PASSWORD=hostman -e POSTGRES_USER=hostman -e POSTGRES_DB=hostman -e USERMAP_UID=999 -e USERMAP_GID=999 -d -p 5432:5432 -v $HOME/docker /volumes/postgres:/var/lib/postgresql/data postgres

Installing PostgreSQL with Docker Compose

Let's consider another option for launching PostgreSQL: using docker-compose.

First, create a YAML file:

version: '3.1'
volumes:
   pg_hostman:
services:
   pg_db:
     image:postgres
     restart: always
     environment:
       - POSTGRES_PASSWORD=hostman
       - POSTGRES_USER=hostman
       - POSTGRES_DB=hostman
     volumes:
       - pg_project:/var/lib/postgresql/data
     ports:
       - ${POSTGRES_PORT:-5531}:5531

Then install Docker Compose:

sudo apt install docker-compose

And run it:

sudo docker-compose up -d

Check that you can connect to the database:

psql -h 192.168.0.4 -U hostman -d hostman

Output example:

Password for user hostman:
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1), server 13.2 (Debian 13.2-1.pgdg100+1))
Type "help" for help.
hostman=#

If a prompt to enter a request appears in the terminal, then the connection was successful.

To stop the container, run:

sudo docker-compose stop

Checking container status

To quickly find out that something is wrong with the isolated environment, we use healthcheck. 

Let's set up an automatic restart when problems are detected.

Example PostgreSQL settings in a YAML file:

version: "3.9"
services:
   postgres:
     image: postgres:13.3
     environment:
       POSTGRES_DB: "twdb"
       POSTGRES_USER: "twpguser"
       POSTGRES_PASSWORD: "785564tw"
       PGDATA: "/var/lib/postgresql/data/pgdata"
     volumes:
       - ../2. Init Database:/docker-entrypoint-initdb.d
       - .:/var/lib/postgresql/data
     ports:
       - "5531:5531"
     healthcheck:
       test: ["CMD-SHELL", "pg_isready -U twpguser -d twdb"]
       interval: 15s
       timeout: 10s
       retries: 7
       start_period: 12s
     restart: unless-stopped
     deploy:
       resources:
         limits:
           cpus: '1'
           memory: 4GB

Pay attention to the resources section. It allows you to limit resources for the database. This approach makes sense for local launch and experimentation.

Conclusion

In this guide, we studied the Docker and Docker Compose methods to run PostgreSQL within a Docker container. We also discussed deploying the container and connecting to the local machine.

Depending on the application architecture, you can deploy the database using Docker or Docker Compose. The second scenario will be more common, so pay more attention to the configuration that needs to be described in the docker-compose.yaml file.