How to Deploy PostgreSQL to a Docker Container Using ClusterControl

Docker has become the most common tool to create, deploy, and run applications by using containers. It allows us to package up an application with all of the parts it needs, such as libraries and other dependencies, and ship it all out as one package. Docker could be considered as a virtual machine, but instead of creating a whole virtual operating system, Docker allows applications to use the same Linux kernel as the system that they’re running on and only requires applications to be shipped with things not already running on the host computer. This gives a significant performance boost and reduces the size of the application.

In this blog, we’ll see how we can easily deploy a PostgreSQL setup via Docker, and how to turn our setup in a primary/standby replication setup by using ClusterControl.

How to Deploy PostgreSQL with Docker

First, let’s see how to deploy PostgreSQL with Docker manually by using a PostgreSQL Docker Image.

The image is available on Docker Hub and you can find it from the command line:

$ docker search postgres
NAME                                         DESCRIPTION                                     STARS               OFFICIAL            AUTOMATED
postgres                                     The PostgreSQL object-relational database sy…   6519                [OK]

We’ll take the first result. The Official one. So, we need to pull the image:

$ docker pull postgres

And run the node containers mapping a local port to the database port into the container:

$ docker run -d --name node1 -p 6551:5432 postgres
$ docker run -d --name node2 -p 6552:5432 postgres
$ docker run -d --name node3 -p 6553:5432 postgres

After running these commands, you should have this Docker environment created:

$ docker ps
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                 PORTS                                                                                     NAMES
51038dbe21f8        postgres                      "docker-entrypoint.s…"   About an hour ago   Up About an hour       0.0.0.0:6553->5432/tcp                                                                    node3
b7a4211744e3        postgres                      "docker-entrypoint.s…"   About an hour ago   Up About an hour       0.0.0.0:6552->5432/tcp                                                                    node2
229c6bd23ff4        postgres                      "docker-entrypoint.s…"   About an hour ago   Up About an hour       0.0.0.0:6551->5432/tcp                                                                    node1

Now, you can access each node with the following command:

$ docker exec -ti [db-container] bash
$ su postgres
$ psql
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.
postgres=#

Then, you can create a database user, change the configuration according to your requirements or configure replication between the nodes manually.

How to Import Your PostgreSQL Containers into ClusterControl

Now that you’ve setup your PostgreSQL cluster, you still need to monitor it, alert in case of performance issues, manage backups, detect failures and automatically failover to a healthy server.

If you already have a PostgreSQL cluster running on Docker and you want ClusterControl to manage it, you can simply run the ClusterControl container in the same Docker network as the database containers. The only requirement is to ensure the target containers have SSH related packages installed (openssh-server, openssh-clients). Then allow passwordless SSH from ClusterControl to the database containers. Once done, use the “Import Existing Server/Cluster” feature and the cluster should be imported into ClusterControl.

First, let’s Install OpenSSH related packages on the database containers, allow the root login, start it up and set the root password:

$ docker exec -ti [db-container] apt-get update
$ docker exec -ti [db-container] apt-get install -y openssh-server openssh-client
$ docker exec -it [db-container] sed -i 's|^PermitRootLogin.*|PermitRootLogin yes|g' /etc/ssh/sshd_config
$ docker exec -it [db-container] sed -i 's|^#PermitRootLogin.*|PermitRootLogin yes|g' /etc/ssh/sshd_config
$ docker exec -ti [db-container] service ssh start
$ docker exec -it [db-container] passwd

Start the ClusterControl container (if it’s not started) and forward port 80 on the container to port 5000 on the host:

$ docker run -d --name clustercontrol -p 5000:80 severalnines/clustercontrol

Verify the ClusterControl container is up:

$ docker ps | grep clustercontrol
7eadb6bb72fb        severalnines/clustercontrol   "/entrypoint.sh"         4 hours ago         Up 4 hours (healthy)   22/tcp, 443/tcp, 3306/tcp, 9500-9501/tcp, 9510-9511/tcp, 9999/tcp, 0.0.0.0:5000->80/tcp   clustercontrol

Open a web browser, go to http://[Docker_Host]:5000/clustercontrol and create a default admin user and password. You should now see the ClusterControl main page.

The last step is setting up the passwordless SSH to all database containers. For this, we need to know the IP Address for each database node. To know it, we can run the following command for each node:

$ docker inspect [db-container] |grep IPAddress
            "IPAddress": "172.17.0.6",

Then, attach to the ClusterControl container interactive console:

$ docker exec -it clustercontrol bash

Copy the SSH key to all database containers:

$ ssh-copy-id 172.17.0.6
$ ssh-copy-id 172.17.0.7
$ ssh-copy-id 172.17.0.8

Now, we can start to import the cluster into ClusterControl. Open a web browser and go to Docker’s physical host IP address with the mapped port, e.g http://192.168.100.150:5000/clustercontrol, click on “Import Existing Server/Cluster”, and then add the following information.

We must specify User, Key or Password and port to connect by SSH to our servers. We also need a name for our new cluster.

After setting up the SSH access information, we must define the database user, version, basedir and the IP Address or Hostname for each database node.

Make sure you get the green tick when entering the hostname or IP address, indicating ClusterControl is able to communicate with the node. Then, click the Import button and wait until ClusterControl finishes its job. You can monitor the process in the ClusterControl Activity Section.

The database cluster will be listed under the ClusterControl dashboard once imported.

Note that, if you only have a PostgreSQL master node, you could add it into ClusterControl. Then you can add the standby nodes from the ClusterControl UI to allow ClusterControl to configure them for you.

How to Deploy Your PostgreSQL Containers with ClusterControl

Now, let’s see how to deploy PostgreSQL with Docker by using a CentOS Docker Image (severalnines/centos-ssh) and a ClusterControl Docker Image (severalnines/clustercontrol).

First, we’ll deploy a ClusterControl Docker Container using the latest version, so we need to pull the severalnines/clustercontrol Docker Image.

$ docker pull severalnines/clustercontrol

Then, we’ll run the ClusterControl container and publish the port 5000 to access it.

$ docker run -d --name clustercontrol -p 5000:80 severalnines/clustercontrol

Now you can open the ClusterControl UI at http://[Docker_Host]:5000/clustercontrol and create a default admin user and password.

The severalnines/centos-ssh comes with, in addition to the SSH service enabled, an Auto Deployment feature, but it’s only valid for Galera Cluster. PostgreSQL is not supported yet. So, we’ll set the AUTO_DEPLOYMENT variable in 0 in the docker run command to create the databases nodes.

$ docker run -d --name postgres1 -p 5551:5432 --link clustercontrol:clustercontrol -e AUTO_DEPLOYMENT=0 severalnines/centos-ssh
$ docker run -d --name postgres2 -p 5552:5432 --link clustercontrol:clustercontrol -e AUTO_DEPLOYMENT=0 severalnines/centos-ssh
$ docker run -d --name postgres3 -p 5553:5432 --link clustercontrol:clustercontrol -e AUTO_DEPLOYMENT=0 severalnines/centos-ssh

After running these commands, we should have the following Docker environment:

$ docker ps
CONTAINER ID        IMAGE                         COMMAND             CREATED             STATUS                    PORTS                                                                                     NAMES
0df916b918a9        severalnines/centos-ssh       "/entrypoint.sh"    4 seconds ago       Up 3 seconds              22/tcp, 3306/tcp, 9999/tcp, 27107/tcp, 0.0.0.0:5553->5432/tcp                             postgres3
4c1829371b5e        severalnines/centos-ssh       "/entrypoint.sh"    11 seconds ago      Up 10 seconds             22/tcp, 3306/tcp, 9999/tcp, 27107/tcp, 0.0.0.0:5552->5432/tcp                             postgres2
79d4263dd7a1        severalnines/centos-ssh       "/entrypoint.sh"    32 seconds ago      Up 31 seconds             22/tcp, 3306/tcp, 9999/tcp, 27107/tcp, 0.0.0.0:5551->5432/tcp                             postgres1
7eadb6bb72fb        severalnines/clustercontrol   "/entrypoint.sh"    38 minutes ago      Up 38 minutes (healthy)   22/tcp, 443/tcp, 3306/tcp, 9500-9501/tcp, 9510-9511/tcp, 9999/tcp, 0.0.0.0:5000->80/tcp   clustercontrol

We need to know the IP Address for each database node. To know it, we can run the following command for each node:

$ docker inspect [db-container] |grep IPAddress
            "IPAddress": "172.17.0.3",

Now we have the server nodes up and running, we need to deploy our database cluster. To make it in an easy way we’ll use ClusterControl.

To perform a deployment from ClusterControl, open the ClusterControl UI at http://[Docker_Host]:5000/clustercontrol, then select the option “Deploy” and follow the instructions that appear.

When selecting PostgreSQL, we must specify User, Key or Password and port to connect by SSH to our servers. We also need a name for our new cluster and if we want ClusterControl to install the corresponding software and configurations for us.

After setting up the SSH access information, we must define the database user, version and datadir (optional). We can also specify which repository to use.

In the next step, we need to add our servers to the cluster that we are going to create.

When adding our servers, we can enter IP or hostname. Here we must use the IP Address that we got from each container previously.

In the last step, we can choose if our replication will be Synchronous or Asynchronous.

We can monitor the status of the creation of our new cluster from the ClusterControl activity monitor.

Once the task is finished, we can see our cluster in the main ClusterControl screen.

Conclusion

As we could see, the deploy of PostgreSQL with Docker could be easy at the beginning but it’ll require a bit more work to configure replication. Finally, you should monitor your cluster to see what is happening. With ClusterControl, you can import or deploy your PostgreSQL cluster with Docker, as well as automate the monitoring and management tasks like backup and automatic failover/recovery. Try it out.