How to Set Up a Highly Available PostgreSQL Cluster Using Patroni on Ubuntu 16.04

Join us at the Alibaba Cloud ACtivate Online Conference on March 5–6 to challenge assumptions, exchange ideas, and explore what is possible through digital transformation.

By Hitesh Jethva, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud’s incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

Patroni is an open source tool that can be used for creating and managing your own customized, high-availability cluster for PostgreSQL with Python. It can be used to handle tasks such as replication, backups and restorations. Patroni also provides an HAProxy configuration, giving your application a single endpoint for connecting to the cluster’s leader. If you are looking to quickly deploy HA PostgreSQL cluster in the data center, then Patroni is definitely worth considering.

In this tutorial, we will be configuring a highly available PostgreSQL cluster using Patroni on Alibaba Cloud Elastic Compute Service (ECS) with Ubuntu 16.04. We will need four ECS instances; we will use Instance1 as a master and Instance2 as a slave, configure replication from master to slaves, and configure automatically failover to the slave if the master goes down.

Prerequisites

  • Four fresh Alibaba cloud instance with Ubuntu 16.04 installed.
  • A static IP address is configured on each instance.
  • A root password is set up to your instance.

Launch Alibaba Cloud ECS Instance

First, log in to your Alibaba Cloud ECS Console. Create a new ECS instance, choosing Ubuntu 16.04 as the operating system with at least 2GB RAM. Connect to your ECS instance and log in as the root user.

Once you are logged into your Ubuntu 16.04 instance, run the following command to update your base system with the latest available packages.

Setup Environment

In this tutorial, we will be using the following setup:

InstanceApplicationIP AddressInstance1Postgres, Patroni192.168.0.105Instance2Postgres, Patroni192.168.0.104Instance3etcd192.168.0.103Instance4HAProxy192.168.0.102

Install PostgreSQL

First, you will need to install PostgreSQL on Instance1 and Instance2. By default, PostgreSQL is available in the Ubuntu 16.04 repository. You can install it by just running the following command:

Once the installation is completed, verify the status of PostgreSQL using the following command:

Output:

Next, stop the PostgreSQL service so that Patroni can manage it:

Next, you will need to create a symlinks from /usr/lib/postgresql/9.5/bin. Because, Patroni uses some tools that comes with PostgreSQL. You can create symlink with the following command:

Install Patroni

You will need to install Patroni on Instance1 and Instance2.

Before installing Patroni, you will need to install Python and Python-pip to your server. You can install them with the following command:

Next, install Patroni using the pip command:

Install Etcd and HAProxy

Etcd is a distributed key value store that provides a reliable way to store data across a cluster of machines. Here, we will use Etcd to store the state of the Postgres cluster. So, both Postgres nodes make use of etcd to keep the Postgres cluster up and running.

You can install Etcd on Instance3 with the following command:

HAProxy is free, open source software that provides a high availability load balancer and proxy server for TCP and HTTP-based applications that spreads requests across multiple servers.

Here, we will use HAProxy to forwards the connection Master or Slave node which is currently the master and online.

You can install HAProxy on Instance4 with the following command:

Configure Etcd

Etcd default configuration file is located at /etc/default directory. You will need to make some changes in etcd file.

Add the following lines:

Save and close the file, then restart Etcd service with the following command:

You can now check the status of Etcd with the following command:

Output:

Configure Patroni

Patroni uses YAML to store their configuration. So, you will need to create a configuration file for Patroni on Instance1 and Instance2:

Add the following lines:

Save and close the file, when you are finished.

Next, create a data directory for Patroni on Instance1 and Instance2:

Next, change the ownership and permissions of the data directory:

Next, you will need to create a startup script for Patroni to manage and monitor Patroni. You can do this with the following command:

Add the following lines:

Save and close the file. Then start PostgreSQL and Patroni service on both Instance1 and Instance2 with the following command:

Next, check the status of Patroni using the following command:

Output:

Note: Repeat all the above steps on both Instance1 and Instance2.

Configure HAProxy

PostgreSQL cluster is now up and running. It’s time to configure HAProxy to forward connection receive from PostgreSQL client to the Master node.

You can configure HAProxy by editing /etc/haproxy/haproxy.cfg file:

Add the following lines:

Save and close the file. Then, restart HAProxy with the following command:

Test PostgreSQL Cluster

Now, open your web browser and type the URL http://192.168.0.102:7000 (HAProxy Instance4 IP address). You will be redirected to the HAProxy dashboard as shown below:

Image for post
Image for post

In the above image, the postgresql_192.168.0.105_5432 row is highlighted in green. That means 192.168.0.105 is currently acting as the master.

If you shut down the primary node (Instance1), 192.168.0.104 (Instance2) should be acting as the master.

When you restart the Instance1, it will rejoin the cluster as a slave and sync up with the master.

Reference:https://www.alibabacloud.com/blog/how-to-set-up-a-highly-available-postgresql-cluster-using-patroni-on-ubuntu-16-04_594477?spm=a2c41.12584060.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store