Design A Highly Available PostgreSQL Cluster With Patroni In GCP — Part 2 Bhuvanesh Follow Sep 16, 2019 · 10 min read

Credit: Zalando

This is the implementation part of Design A Highly Available PostgreSQL Cluster With Patroni In GCP along with ETCD, TCP, and HTTP Load balancers. Lets begin the setup. Here is my inventory of PostgreSQL, etcd and load balancers. (the load balancer IP you can create while creating the LB).

Configure ETCD cluster:

We’ll install the ETCD on both nodes. I strongly recommend keeping 3 nodes in the cluster for production servers. I used Ubuntu 19.04, you may follow other blogs to install it on CentOS or other OS.

-- On all etcd nodes

apt-get update

apt-get install etcd

On etcd Node 1 add the lines at the end

On etcd Node 2 add the lines at the end

start etcd on both nodes asap. Becuase if you start the node1 first, it’ll wait for few seconds to connect other nodes. At this time, other nodes are unavailable then, the etcd will not start. So open two terminals for both the nodes, and run the start command asap.

service etcd start

Check the cluster health:

-- run this command on any one of the nodes

etcdctl cluster-health

member a3e5382602a9a572 is healthy: got healthy result from

cluster is healthy member 74ea3293214c99ab is healthy: got healthy result from http://10.128.0.114:2379 member a3e5382602a9a572 is healthy: got healthy result from http://10.128.0.115:2379 cluster is healthy

Create an instance group:

Go to VM → Instance group → Unmanaged instance group.

Choose your region and network.

Named port: Port Name — etcd and Port Number 2379

Add your etcd instances.

If you have etcd instances in multiple zones, then create multiple instance groups.

Create HTTP Load balancer for ETCD:

Go to Network Services → Load Balancer → HTTP(S) Load balancer.

Choose Only between my vms.

Name, Region, Network — please select your relevant region name and network.

Backend Services → create new

Name: patroni-etcd-backend

Protocol: HTTP

Named Port: etcd

Timeout: 30 seconds

Backend type: Instance group

Under instance group, select your Vm group. If you have more VM group add all of them.

Port Numbers: 2379

Maximum RPS: (its your wish) I choose 100

Health Check → Create

Name: etcd-health-check

Protocol: http

Port: 2379

Request Path: /health

Leave the rest of the parameters as it is. (Or my config is 2 secs for all)

Click on Create button.

Fronend Configuration:

Name: patroni-etcd-frontend

Protocol: HTTP

Subnet your — Choose where you want to host this LB.

Internal IP: choose your reserved IP or Create a new one.

Port: 80 (or you can use 8080, it doesn’t matter)

Then create the LB.

This is my LB

Now lets move to the Patroni and PostgreSQL. For a change Im trying to convert my existing standalone PostgreSQL node to Patroni.

Install PostgreSQL On all nodes:

sudo apt-get install curl ca-certificates gnupg curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main">/etc/apt/sources.list.d/pgdg.list sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' sudo apt-get update && sudo apt-get -y install postgresql-9.6 postgresql-common

Change the data directory:

mkdir /pgdata/data

chown -R postgres:postgres /pgdata/data

chmod 0700 /pgdata/data root@bhuvi-psql# su postgres

/usr/lib/postgresql/9.6/bin/pg_ctl -D /pgdata/data/ initdb -- Make this change in posgresql.conf file

vi /etc/postgresql/9.6/main/postgresql.conf

data_directory = '/pgdata/data' service postgresql start

Now I have 1 PostgreSQL server standalone server. We’ll move this into Patroni cluster. You can do this without downtime, but you need the below parameters are already enabled. Else we need to restart once we moved into the Patroni.

wal_level hot_standby wal_keep_segments max_wal_senders max_replication_slots checkpoint_timeout

Install Patroni:

apt-get install -y python python-pip python-psycopg2

pip install --upgrade setuptools

pip install python-etcd

pip install psycopg2-binary

pip install patroni

Create a service for Patroni:

vi /etc/systemd/system/patroni.service [Unit]

Description=Runners to orchestrate a high-availability PostgreSQL

After=syslog.target network.target

[Service]

Type=simple

User=postgres

Group=postgres ExecStart=/usr/local/bin/patroni /etc/patroni.yml

KillMode=process

TimeoutSec=30

Restart=no [Install]

WantedBy=multi-user.targ

You need an environment variable on PostgreSQL user to determine the location of the data directory.

Replace /pgdata/data/ with your data directory location.

su postgres

vi ~/.bash_profile [ -f /etc/profile ] && source /etc/profile

PGDATA=/pgdata/data/

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

Add Patroni config file on the master node:

vi /etc/patroni.yml scope: postgres

namespace: /db/

name: bhuvi-psql restapi:

listen: 0.0.0.0:8008

connect_address: 10.128.0.18:8008 etcd:

host: 10.128.0.122:80 bootstrap:

dcs:

ttl: 100

loop_wait: 10

retry_timeout: 10

maximum_lag_on_failover: 1048576

postgresql:

use_pg_rewind: true

use_slots: true

parameters:

wal_level: replica

hot_standby: "on"

wal_keep_segments: 8

max_wal_senders: 5

max_replication_slots: 5

checkpoint_timeout: 30 initdb:

- encoding: UTF8

- data-checksums pg_hba:

- host replication replicator 127.0.0.1/32 md5

- host replication replicator 10.128.0.18/32 md5

- host replication replicator 10.128.0.98/32 md5

- host replication replicator 10.128.0.99/32 md5

- host all all 0.0.0.0/0 md5 users:

admin:

password: admin

options:

- createrole

- createdb postgresql:

listen: 0.0.0.0

connect_address: 10.128.0.18:5432

data_dir: /pgdata/data

config_dir: /pgdata/data

bin_dir: /usr/lib/postgresql/9.6/bin

pgpass: /tmp/pgpass

authentication:

replication:

username: replicator

password: rep-pass

superuser:

username: postgres

password: secretpassword

parameters:

unix_socket_directories: '/tmp' tags:

nofailover: false

noloadbalance: false

clonefrom: false

nosync: false log:

dir: /var/log/postgresql

level: INFO

Start Patroni:

service patroni start service patroni status ● patroni.service - Runners to orchestrate a high-availability PostgreSQL

Loaded: loaded (/etc/systemd/system/patroni.service; disabled; vendor preset: enabled)

Active: active (running) since Mon 2019-09-16 05:10:19 UTC; 2h 30min ago

Main PID: 9191 (patroni)

Tasks: 14 (limit: 4418)

Memory: 188.1M

CGroup: /system.slice/patroni.service

├─ 9191 /usr/bin/python /usr/local/bin/patroni /etc/patroni.yml

Check the cluster list:

patronictl -c /etc/patroni.yml list

But the configurations of the patroni is out of data, and the replication parameters are not enabled on this PostgreSQL server. So we need to restart this node. If you already enabled those parameters, then this step is not required. [my cluster name is postgres]

The pg_hba.conf file should be updated before the restart. Because now the postgresql will use the conf file in the /pgdata/data/ location.

vi / pgdata/data/ host replication replicator 127.0.0.1/32 md5

host replication replicator 10.128.0.18/32 md5

host replication replicator 10.128.0.98/32 md5

host replication replicator 10.128.0.99/32 md5

host all all 0.0.0.0/0 md5