How to install PostgreSQL on Docker

How to install PostgreSQL on Docker

Posted on 28 Jun 2018

This is the third article of the Getting started with Docker series. Here I want to show you how to use Docker to create a container where you can install PostgreSQL.

Create the Docker image with Dockerfile

The first step of this tutorial is to write the Dockerfile for our PostgreSQL application. This is the code of our Dockerfile.

FROM ubuntu:16.04
RUN apt-get update && apt-get install -y postgresql iputils-ping net-tools \
    netcat vim
RUN mkdir -p /home/postgres/data/postgres && \
    mkdir -p /home/postgres/data/logs && \
    mkdir -p /usr/local/bin/cluster/postgresql && \
    chown -R postgres:staff /home/postgres/ && \
    chmod 700 /home/postgres/data/postgres
USER postgres
COPY ./postgresql /usr/local/bin/cluster/postgresql
EXPOSE 5432
ENTRYPOINT /usr/local/bin/cluster/postgresql/entrypoint.sh

Line 1 specifies that Ubuntu 16.04 is the starting point for our image. On line 2 we have the RUN command that installs the PostgreSQL package plus some additional packages containing useful tools for developing and debugging (i.e. ping, nc, vim, and netstat). Line 4 contains another RUN command we will use to create the PostgreSQL data and log directories plus a folder that will contain the startup scripts. Line 9 has the USER command that switches the current user to postgres. From this moment on all the commands will be executed by the postgres user. On line 10 the COPY command copy all the startup scripts in the container folder /usr/local/bin/cluster/postgresql.  Line 11 the EXPOSE command exposes the 5432 port that is the port on which PostgreSQL will listen. Finally, line 12 the ENTRYPOINT command specifies the script that will be launched at startup.

To create the image we define the script build_image.sh that will use the “docker build” command to create the image. Here the content of the script.

docker build src -t postgresql

The image name is postgresql and you can see it with the “docker image ls” command. I created a second script called clean_image.sh to drop the image when it is not necessary anymore. Here the code of this script.

docker image rm -f postgresql

How to create the Docker container

The Docker container is created with the script start_containers.sh:

NODE1_NAME=node1
NODE1_PORT=5432
docker create -it --name ${NODE1_NAME} -p ${NODE1_PORT}:5432 postgresql /bin/bash
docker start ${NODE1_NAME}

The script uses the “docker create” command to create the container starting from the image postgresql. This command creates the container in a detached mode so you can access it or exit it without losing changes. The internal port 5432 on which PostgreSQL listens will be mapped on the same port on the host environment. The command “docker start” will start the container. The container can be destroyed by the script stop_container.sh that stop and remove the container.

NODE1_NAME=node1
docker stop ${NODE1_NAME}
docker rm ${NODE1_NAME}

The container is created and it is up and running, to access it use the following command.

docker exec -it CONTAINER_ID /bin/bash

As usual, you can get the CONTAINER_ID with the command “docker container ls” and use the one related to the postgresql image.

The startup and config scripts

When Docker starts the container, it executes the script /usr/local/bin/cluster/postgresql/entrypoint.sh. This script runs the /usr/local/bin/cluster/postgresql/bin/entrypoint.sh script and wait for its completion.

#!/bin/bash
set -e
echo '>>> STARTUP POSTGRESQL ...'
/usr/local/bin/cluster/postgresql/bin/entrypoint.sh & wait ${!}
EXIT_CODE=$?
echo ">>> POSTGRESQL TERMINATED WITH EXIT CODE: $EXIT_CODE"
exit $EXIT_CODE

The /usr/local/bin/cluster/postgresql/bin/entrypoint.sh script will be responsible to create the data directory (line 3), copy the configuration files (lines 5 and 6), and start PostgreSQL (line 8).

#!/bin/bash
echo ">>> CREATE DATA DIRECTORY"
/usr/lib/postgresql/9.5/bin/initdb -D /home/postgres/data/postgres
echo ">>> COPY CONFIGURATION FILES"
cp /usr/local/bin/cluster/postgresql/config/postgresql.conf /home/postgres/data/postgres
cp /usr/local/bin/cluster/postgresql/config/pg_hba.conf /home/postgres/data/postgres
echo ">>> START POSTGRESQL ON NODE $NODE_NAME"
/usr/lib/postgresql/9.5/bin/postgres -D /home/postgres/data/postgres > /home/postgres/data/logs/postgres.log 2>&1

Test the Docker Container

As a prerequisite to testing the Docker container, you need to install PostgreSQL client on your host system. I use Mac and the command to install it is:

brew install libpq

Windows and Linux systems have an equivalent command you can search by google. Build the image and start the container:

./build_image.sh
./start_containers.sh

Now you can access PostgreSQL with the command:

psql -h localhost -p 5432 -U postgres

Once you logged in PostgreSQL you can create a database.

CREATE DATABASE mydb;

You can check its creation with the command \list. To quit from psql command line use the command \q.

What’s next?

In this article, we created a simple Docker container where we installed a PostgreSQL database server.  You can download the sample code here in the postgresql folder. In the next article, I will explain how Docker networking works. This step is a prerequisite to creating our cluster.

Getting started with Docker