How to run Postgres on Docker part 3

In a previous post I deployed a PostgreSQL database in a container from a standard image. Now I’m going to look at deploying a PostgreSQL cluster. Well, kind of. PostgreSQL uses the term cluster to refer to “a collection of databases managed by a single PostgreSQL server instance”. I’m using it in the more general sense, to refer to High Availability.

Update: Also read the new blog How to Postgres on Kubernetes!

Basic replication using a pre-built project

PostgreSQL has had streaming replication since 9.0, where the WAL logs are transferred between the Master database and the read-only Slave (see SeveralNines blog for more details). It’s fairly easy to set up, so what can Docker add? Well, you can use the docker-compose command, and docker-compose.yml file, to control a number of Docker containers. So the Master and Slave database will be treated as one unit.

I’m going to base this on Hamed Momeni’s Medium post, but with a few changes.

First of all, we need to create a docker network so our containers can communicate amongst themselves:

docker network create bridge-docker

Next, pull down the files and directories from BitBucket:

git clone https://bitbucket.org/CraigOptimaData/docker-pg-cluster.git

cd into the newly created directory named docker-pg-cluster.

cd docker-pg-cluster

Then you simply have to run the docker-compose up command:

docker-compose up

You’ll need to wait a while as it has a few things to do whilst building the master and slave images. You’ll see a couple of red lines warning that it has to delay package configuration, but that is normal. Eventually it will finish building the images and actually run them. At this point it should display the logs from pg_master_1 and pg_slave_1 as they are initialized. If everything has gone well, the logs will stop scrolling up the screen.

If everything went well, both master and slave are up and running. But because we started the docker-compose command without the detached flag, so we could see any errors, we need to shut it down and start it up again in the background. Ctl-C will exit out of the container, and consequently stop it. Then run the docker-compose up command with the -d flag:

docker-compose up -d

The docker-compose files

If you have a look in the docker-pg-cluster directory, you will see the following structure:

Docker-pg-cluster:
docker-compose.yml

master:

Dockerfile
setup-master.sh

slave:

Dockerfile
setup-slave.sh

The main component is the docker-compose YAML file. As it’s a YAML file, you need to be careful of the formatting. It’s split in to three sections, the declarations for the master, the slave and lastly general settings. The data in green is the information docker-compose uses to create the containers. The container names break down thus:

Docker-compose.yml

version: "3"
services:

The build line denotes the subdirectory.

Environmental variables are shown in purple such as port numbers,



PostgreSQL users and passwords.


 

Then the name of the network (that we created at the start).

The restart line tells docker to restart the container if it stops.




I’ve given both containers their own volumes, with names, so it’s easy to find them later.
 

 

 

 

 

Master Dockerfile

FROM postgres:11
COPY ./setup-master.sh /docker-entrypoint-initdb.d/setup-master.sh
RUN chmod 0666 /docker-entrypoint-initdb.d/setup-master.sh
RUN apt-get update && apt-get --assume-yes install iputils-ping && apt-get install --assume-yes ssh && apt-get install --assume-yes net-tools

The master\Dockerfile is straightforward. It uses PostgreSQL 11 as there are some changes to replication in PostgreSQL 12. It copies setup-master.sh into the container and sets the correct permissions. Then it installs various packages that we will want to use such as iputils, net-tools and ssh. You could make the cluster with an Alpine image, which would have a much smaller footprint. But the last step I want to do is import it into ControlControl and for that I need ssh which is not easily available in Alpine. If you were doing this in a real environment you might want to set up an ssh server, for this demonstration I’m just adding ssh directly to the containers.

Setup-master.sh

#!/bin/bash
echo "host replication all 0.0.0.0/0 md5" >> "$PGDATA/pg_hba.conf"
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL CREATE USER $PG_REP_USER REPLICATION LOGIN CONNECTION LIMIT 100 ENCRYPTED PASSWORD '$PG_REP_PASSWORD';
CREATE USER postgres;
EOSQL
cat >> ${PGDATA}/postgresql.conf <
wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 8
wal_keep_segments = 8
hot_standby = on
EOF

The setup-master.sh file adds a line to the PostgreSQL pg_hba.conf file to allow replication. Again, for a real installation you might want to consider the security implications of this. It then adds a couple of postgres users, one taken from the environmental variables in the docker-compose.yml file, and the other a default postgres user. Then it adds some lines to the postgresql.conf file setting this up as the master.

Slave Dockerfile

FROM postgres:11
RUN apt-get update && apt-get --assume-yes install iputils-ping && apt-get install --assume-yes ssh && apt-get install --assume-yes net-tools && apt-get install --assume-yes gosu
COPY ./setup-slave.sh /setup-slave.sh
RUN chmod +x /setup-slave.sh
ENTRYPOINT ["/setup-slave.sh"]
CMD ["gosu","postgres","postgres"]

The slave\Dockerfile is similar to the master one.

Setup-slave-sh

#!/bin/bash
if [ ! -s "$PGDATA/PG_VERSION" ]; then

echo "*:*:*:$PG_REP_USER:$PG_REP_PASSWORD" > ~/.pgpass
chmod 0600 ~/.pgpass
until ping -c 1 -W 1 pg_master_1
do

echo "Waiting for master to ping..."
sleep 1s

done
until pg_basebackup -h pg_master_1 -D ${PGDATA} -U ${PG_REP_USER} -vP -W
do

echo "Waiting for master to connect..."
sleep 1s

done
echo "host replication all 0.0.0.0/0 md5" >> "$PGDATA/pg_hba.conf"
set -e
cat > ${PGDATA}/recovery.conf <
standby_mode = on
primary_conninfo = 'host=pg_master_1 port=5432 user=$PG_REP_USER password=$PG_REP_PASSWORD'
trigger_file = '/tmp/touch_me_to_promote_to_me_master'

EOF

chown postgres. ${PGDATA} -R
chmod 700 ${PGDATA} -R

fi
sed -i 's/wal_level = hot_standby/wal_level = replica/g' ${PGDATA}/postgresql.conf
exec "$@"

The setup-slave.sh file has a couple of things of note. It sets up a loop to check it can ping the master, and then another one to replicate data using the PG_REP_USER from docker-compose.yml. Ensure the name of the master matches that used for the service in the docker-compose.yml file.

So, having got your PostgreSQL cluster running and having looked at the files that created it, lets see it in action. You can, of course, log in to PostgreSQL and run SQL commands to see it working. But this blog is about Docker, not PostgreSQL, so lets create a pgAdmin4 container to view our cluster.

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

Docker containers don’t usually have GUIs, so to view pgAdmin4 open it in a browser window. The IP address is that of your docker-machine and the port is 80. Login with the email and password you used to create the container (user@domain.com and SuperSecret in my case).

Add both servers to pgAdmin. The only difference between them will be the names you give them and the port numbers. The IP address will again be that of your docker-machine.

Connection:
Host name/address: docker-machine IP
Port: 5445 or 5446
Maintenance database: postgres
Username: optima
Password: 123456

In the master databases, navigate to pg_stat_replication (Databases -> postgres -> Catalogs -> PostgreSQL Catalog (pg_catalog) -> Views) and view all rows. You’ll see a single row of data if it’s replicating properly. You can also add tables and data into the master and check that it’s copied over into the slave.

ClusterControl

Whilst we definitely have a working database cluster, it’s not the most visually impressive. So how about adding another container with SeveralNines’ ClusterControl, which comes with a 30 day trial enterprise licence before reverting to the community edition:

docker run -d --name clustercontrol --restart always --network bridge-docker -p 5000:80 severalnines/clustercontrol

Again I added a flag to restart the container if it stops, and put it on the same network as the other containers. Open another browser tab on the same IP as your docker-machine, and port 5000 (e.g. 192.168.99.105:5000/clustercontrol). It will ask you to supply an email and password to log on and for registration details but these don’t need to be valid. Before we can add the servers to ClusterControl we need to set up ssh on the servers.

Connect to the ClusterControl container to find its IP address and set the root password.

docker exec -it clustercontrol bash
passwd
ip addr show

Now log on to both of the servers

docker exec -it docker-pg-cluster_pg_master_1_1 bash
docker exec -it docker-pg-cluster_pg_slave_1_1 bash

To allow you to login as root run the following:

sed -i 's|^#PermitRootLogin.*|PermitRootLogin yes|g' /etc/ssh/sshd_config

then start ssh

service ssh start

Set the root password

passwd

and find the IP addresses of the servers

ifconfig

Create an ssh key on each of the servers

mkdir -p $HOME/.ssh
chmod 0700 $HOME/.ssh
ssh-keygen -t rsa

ssh-copy-id -i $HOME/.ssh/id_rsa.pub root@[ClusterControl IP address]
ssh root@[ClusterControl IP address]

On the ClusterControl container

ssh-copy-id [master IP address]
password
ssh [master IP address]
ssh-copy-id [slave IP address]
password
ssh [slave IP address]

In ClusterControl, add the cluster:

Import
PostgreSQL
SSH User:root
Cluster Name:blog3a
Server port: 5432 NOTE, NOT THE CONTAINER’S PORT
User: optima
Password: 123456
Version:11
Basedir: /var/lib/postgresql/data
Add Nodes:IP address of the master and slave, pressing after each one
Import

Wait a minute whilst the import job runs

On the Overview tab you can now easily see the replication status.

You’ve now got a 2-node PostgreSQL 11 database cluster with pgAdmin4 and ClusterControl running in 4 containers. Play around with starting and stopping them and see how easy it is to create new clusters.

Want to know more?

This is certainly not all that Docker can offer, but hopefully it’s enough to get you started. Do you want to know more or need help or advise by using or implementing PostgreSQL, Docker, Kubernetes and /or ClusterControl, feel free to contact us! We are happy to help you.

Blog How to run Postgres on Docker part 1

Blog How to run Postgres on Docker part 2

How to Postgres on Kubernetes (part 1)

Back to blogoverview