Sign in Subscribe
posgresql

High availability and Disaster recovery with Docker and Postgres part II

Seosamh Cahill

• 7 min read
High availability and Disaster recovery with Docker and Postgres part II

In part I we looked in depth at the services and libraries that are required to run a highly available postgres cluster on docker.

This article will step through the process of deploying a test stack on various different hosts.

You can find the referenced configuration file here.

Step 1 - Setting up the hosts.

I will be using docker swarm mode to manage a cluster of hosts. If you're looking for a kubernetes flavoured tutorial Zalando has more details on this as they currently deploy their postgres cluster using a Kubernetes setup called Spilo.

Note that the following swarm setups are for testing purposes only and have only one manager node. As a general rule adding manager nodes to a docker swarm increases fault tolerance but decreases performance - the docs recommend 3, 5 or 7 managers. You can read more about how to set up your swarm in production here.

A Single host.

The easiest way to try docker swarm mode is locally on a single node.

Make sure you have docker community edition installed on your platform and the docker daemon is running

$ docker --version
> Docker version 17.09.0-ce, build afdb6d4

To setup a single node swarm run

docker swarm init

and you're good to go!

If you're happy enough with this setup you can skip forward to Step 3.

A multi-node swarm

We will use docker-machine to provision three vm hosts with the docker daemon installed and running, using three different drivers: virtualbox; amazon web services; and digital ocean.

You can find the complete docker-machine documentation here.

For the rest of this article the term "node" will refer to both a physical or virtual machine running docker and also a node in a docker swarm.

The host-machines/nodes will be named db-1, db-2 and db-3.

The Virtualbox driver:

Create the hosts with the following command:

for vm in db-1 db-2 db-3; do 
    docker-machine create --driver virtualbox $vm;
done

N.B you should probably bump the docker memory / cpu core settings on your system as the default (2 cores and 2GB of RAM) is too likely low to run the stack.

run docker-machine ls after the provisioning is done to see the current state of the hosts:

db-1            -        virtualbox     Running   tcp://192.168.99.100:2376   
db-2            -        virtualbox     Running   tcp://192.168.99.101:2376   
db-3            -        virtualbox     Running   tcp://192.168.99.102:2376

You can find the virtualbox driver documentation here.

The Amazon driver

Create the hosts:

for vm in db-1 db-2 db-3; do 
    docker-machine create 
    --driver aws 
    --amazonec2-access-key ******* 
    --amazonec2-secret-key *******
    --amazonec2-default-region eu-west-1
    $vm;
done

You may need to add the default user to the docker security group. Run this command on each host to do so:

sudo usermod -a -G docker ubuntu

You may also need to make adjustments to the docker-machine security group to open up extra ports that are needed for docker swarm mode to function properly.

Log into your aws console and inspect the "docker-machine" security group in your EC2 control panel.

Ensure the following ports are open:

  • TCP port 2377 for cluster management communications
  • TCP and UDP port 7946 for communication among nodes
  • UDP port 4789 for overlay network traffic

I have automated the above tasks in a script which you can find here.

The full aws driver documentation is here.

The Digital Ocean driver

You need to create a personal access token under “Apps & API” in the Digital Ocean Control Panel and then pass it in when creating each node.

Something like this should work:

docker-machine create
    --driver digitalocean
    --digitalocean-access-token=$TOKEN
    --digitalocean-image=ubuntu-16-04-x64
    --digitalocean-region=ams-3
    --digitalocean-size=512mb
    --digitalocean-ipv6=false
    --digitalocean-private-networking=false
    --digitalocean-backups=false
    --digitalocean-ssh-user=root
    --digitalocean-ssh-port=22
   db-1

The digital ocean driver docs are here.

Step 2 - Setting up the swarm

The following steps apply regardless of your choice of driver.

Docker environment

You want to execute all following docker commands in the context of your swarm leader.

You could ssh into each individual machine or prepend docker-machine ssh name-of-machine to your commands but it's easier to target the remote machine directly.

eval "$(docker-machine env db-1)"

This will set the environment variables that docker cli uses to communicate with the docker daemon to point at the remote host "db-1".

If you run docker-machine ls you will see an asterix after db-1 which indicates that you are currently targeting its docker engine.

NAME            ACTIVE   DRIVER         STATE     URL                         
db-2            -        digitalocean   Running   tcp://192.x.x.x             
db-1            *        digitalocean   Running   tcp://193.x.x.x

To reset the environment to your local context run

$(docker-machine env --unset)

Create the swarm

Initiate the swarm on db-1 with:

docker swarm init

You may be prompted to choose an ip address on which to advertise swarm connections. Choose the leader node's external ip address and run the command again with the --advertise-addr flag e.g.

   docker swarm init --advertise-addr 192.168.99.100

The current machine will automatically become the swarm leader (you can change the leader later if you wish).

The last step is to add the db-2 and db-3 host-machines to the swarm as worker nodes.

Run docker swarm join-token worker and execute the command from the following prompt on db-2 and db-3:

docker-machine ssh db-2 "docker swarm join --token eladjfadf2342342 192.168.65.2:2377"

Finally run docker node ls to see your new swarm.

Step 3 - Deploy the cluster.

To deploy the test stack to your local or remote hosts run:

docker deploy -c ‘docker-stack.test.yml’ pg-cluster

Here 'pg_cluster' is an arbitrary name for the stack, you can call it whatever you want.

If you have images hosted in private repositories make sure you are currently authenticated in your local development environment and then add the flag --with-registry-auth when deploying. This will ensure that your remote hosts can pull the images it needs from your private registry.

All the images referenced in docker-stack.test.yml are hosted publicly on docker hub so you don't need to worry about authenticating in this instance.

To check that all your services are up and started run

docker service ps

You can check the logs of an individual service using the following pattern:

docker service logs stack_name_service_name [e.g. pg_cluster_haproxy]

Querying the cluster

First let's create a database and import some sample data. I'm going to use the pagila data set here as test data:

When accessing the database remotely use a non-superuser to make sure you don't interrupt patroni's cluster monitoring which requires superuser access.

Patroni created a user called 'admin' with a password of admin when it initialized postgres (review the .env file referenced in part I to see how) so let's authenticate using that user:

export PGPASSWORD=admin
psql -h $(docker-machine ip db-1) -U admin -p 5000 -c "create database pagila;"
psql -h $(docker-machine ip db-1) -U postgres -p 5000 pagila < test-data/pagila-schema.sql 
psql -h $(docker-machine ip db-1) -U postgres -p 5000 pagila < test-data/pagila-data.sql

Now check the replication status:

docker exec -ti $(docker ps -fq name=dbnode1) patronictl list pg_cluster

The last argument "pg_cluster" refers to the patroni cluster name not the docker stack name. The patroni cluster name is defined using the environment variable PATRONI_SCOPE.

You can also check the replication status using the api:

curl localhost:8008/patroni | jq .

If you prefer using sql or require more detailed statistics you can of course use psql to directly access a replica and query the pagila database that way.

Accessing running containers

A docker service running on a swarm comprises one or many tasks.

Tasks are docker containers based on the image referenced in the service definition.

To list all running tasks (containers in other words) across the stack use:

docker stack ps pg_cluster

From time to time you will need to access a running container to query its current state or to configure a running service on the fly. The docker command to open up an interactive terminal in a running container is

docker exec -ti [id-of-container] [command]

So to open a tty for etcd for example, you would type

docker exec -ti $(docker ps -fq name=etcd) /bin/sh

To view a containers logs use

docker logs container-id

To view the aggregate logs of a service you can use

docker service logs [service-id-or-name]

If you are using the .aliases file included in the test repo you will find helpful shortcuts for these and other commands listed in there.

Step 4 - Testing High Availability.

Planned failover

A controlled failover or 'switchover' can be performed using the patroni cli:

patronicli failover pg-cluster

or by sending a post request to /failover with leader and candidate params (Cf. stack_tests file).

If you use the CLI you will be prompted to select a node to promote and also a time to perform the failover.

When the failover is complete you can check the status of the cluster and any replication lag via the patroni's cli or api.

Unplanned failover

To simulate an unplanned failover situation kill the server instance that the new master is on:

docker service rm pg_cluster_dbnode2

Run docker service ls to make sure it's gone and then repeat the steps outlined for a controlled failover to see if everything has worked.

To bring the dead node back online simply redeploy the stack.

docker stack deploy pg-cluster

The "dbnode2" service should be recreated as a replica and the pool of available servers should be back to three and working as normal.

Monitoring failover:

You can specify callback scripts to run in config.yml file when initializing patroni.

The available callback events are:

  • on_reload: run this script when configuration reload is triggered.
  • on_restart: run this script when the cluster restarts;
  • on_role_change: run this script when a cluster member is being promoted or demoted;
  • on_start;
  • on_stop.

The following example configuration sets up a script to write callback event details to a logfile:

# example patroni.yml callback configuration
bootstrap:
  dcs:
    postgresql:
      callbacks:
        on_role_change: /home/postgres/notification.py

# /home/postgres/notification.py

#!/usr/bin/python

from subprocess import call
import sys

callback_name = sys.argv[1]
current_role = sys.argv[2]

with open('/home/postgres/test.sh', 'rb') as file:
    script = file.read()
rc = call([script, current_role, callback_name], shell=True)

# /home/postgres/test.sh
echo "$1 callback triggered by $0 on $HOSTNAME"> /var/log/patroni_event.log 2>&1

Wrap up and Part III

That's it for part II of this guide to running a highly available postgres cluster on docker.

In part III I'll be looking at more advanced configuration with an eye towards a production deployment.

Some of the topics covered will include:

  • persisting data volumes;
  • adding service constraints;
  • setting up a cron service;
  • setting up wal-e to ship logs to an s3 bucket;
  • automated testing of backups;
  • full disaster recovery;
  • connecting external applications.

Thanks for reading.

Sign up for more like this.

Enter your email
Subscribe