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.
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.
Subscribe to get our best and freshest content