It so happened that at the time the task was set, I did not have enough experience to develop and launch this solution on my own. So, I started googling.
I don’t know what the issue is, but for the umpteenth time, I find that even if I follow the tutorial step by step and prepare the same environment as the author, it still never works. I have no idea what’s going on, but when I faced this yet again, I decided — I will write my own tutorial when it works out. The one that will definitely work.
Online Guides
It so happens that the internet is not lacking in various guides, tutorials, step-by-steps, and similar resources. It so happens that I was tasked with developing a solution for efficiently organizing and building a fault-tolerant PostgreSQL cluster, with the main requirements being stream replication from the Master server to all replicas and automatic failover when the Master server fails.
At this stage, the stack of technologies to be used was determined:
- PostgreSQL as the DBMS
- as a clustering solution
- etcd as a distributed storage for Patroni
- HAproxy to organize a single entry point for applications using the database
Installation
Presenting — building a high-availability PostgreSQL cluster using Patroni, etcd, and HAProxy.
All operations were performed on virtual machines with Debian 10 OS installed.
etcd
I do not recommend installing etcd on the same machines where Patroni and PostgreSQL will be, as the disk load is very important for etcd. However, for training purposes, we will do just that.
Let's install etcd.
#!/bin/bash
apt-get update
apt-get install etcdAdd the content to the file /etc/default/etcd
[member]
ETCD_NAME=datanode1 # your machine's hostname
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ALL IP ADDRESSES SHOULD BE VALID. LISTEN PEER, CLIENT, etc. SHOULD BE SET TO HOST IP ADDRESS
ETCD_LISTEN_PEER_URLS="" # your machine's IP address
ETCD_LISTEN_CLIENT_URLS="" # your machine's IP address
[cluster]
ETCD_INITIAL_ADVERTISE_PEER_URLS="" # your machine's IP address
ETCD_INITIAL_CLUSTER="datanode1=" # addresses of all machines in the etcd cluster
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-1"
ETCD_ADVERTISE_CLIENT_URLS="" # your machine's IP address
Run the command
systemctl restart etcdPostgreSQL 9.6 + Patroni
The first thing to do is to set up three virtual machines to install the necessary software on. After setting up the machines, if you follow my tutorial, you can run this simple script that will (almost) do everything for you. It runs from root.
Please note that the script uses PostgreSQL version 9.6, which is due to our company's internal requirements. The solution has not been tested on other versions of PostgreSQL.
#!/bin/bash
apt-get install gnupg -y
echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main" >> /etc/apt/sources.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get install postgresql-9.6 python3-pip python3-dev libpq-dev -y
systemctl stop postgresql
pip3 install --upgrade pip
pip install psycopg2
pip install patroni[etcd]
echo "
[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
" > /etc/systemd/system/patroni.service
mkdir -p /data/patroni
chown postgres:postgres /data/patroni
chmod 700 /data/patroniпо
touch /etc/patroni.ymlNext, you need to place the following content into the newly created file /etc/patroni.yml, of course replacing the IP addresses everywhere with the ones you use.
Pay attention to the comments in this yaml. Change the addresses to your own on each machine in the cluster.
/etc/patroni.yml
scope: pgsql # must be the same on all nodes
namespace: /cluster/ # must be the same on all nodes
name: postgres1 # must be different on all nodes
restapi:
listen: 192.168.0.143:8008 # address of the node where this file is located
connect_address: 192.168.0.143:8008 # address of the node where this file is located
etcd:
hosts: 192.168.0.143:2379,192.168.0.144:2379,192.168.0.145:2379 # list all your nodes here, in case you are installing etcd on them
# this section (bootstrap) will be written into Etcd:///config after initializing new cluster
# and all other cluster members will use it as a `global configuration`
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: 5120
max_wal_senders: 5
max_replication_slots: 5
checkpoint_timeout: 30
initdb:
- encoding: UTF8
- data-checksums
- locale: en_US.UTF8
# init pg_hba.conf must contain the addresses of ALL machines used in the cluster
pg_hba:
- host replication postgres ::1/128 md5
- host replication postgres 127.0.0.1/8 md5
- host replication postgres 192.168.0.143/24 md5
- host replication postgres 192.168.0.144/24 md5
- host replication postgres 192.168.0.145/24 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: 192.168.0.143:5432 # address of the node where this file is located
connect_address: 192.168.0.143:5432 # address of the node where this file is located
data_dir: /data/patroni # this directory will be created by the script described above and will set the necessary permissions
bin_dir: /usr/lib/postgresql/9.6/bin # specify the path to your postgresql directory
pgpass: /tmp/pgpass
authentication:
replication:
username: postgres
password: postgres
superuser:
username: postgres
password: postgres
create_replica_methods:
basebackup:
checkpoint: 'fast'
parameters:
unix_socket_directories: '.'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: falseThe script must be executed on all three machines of the cluster, and the provided configuration must be placed in the file /etc/patroni.yml on all machines.
Once you have completed these operations on all machines of the cluster, execute the following command on any of them
systemctl start patroni
systemctl start postgresqlWait for about 30 seconds, then run this command on the other machines of the cluster.
HAproxy
We use the wonderful HAproxy to provide a single entry point. The master server will always be accessible at the address of the machine on which HAproxy is deployed.
To avoid making the machine with HAproxy a single point of failure, we will run it in a Docker container, which can later be deployed in a K8’s cluster to make our fault-tolerant cluster even more reliable.
Create a directory where you can store two files — Dockerfile and haproxy.cfg. Navigate to it.
Dockerfile
FROM ubuntu:latest
RUN apt-get update
&& apt-get install -y haproxy rsyslog
&& rm -rf /var/lib/apt/lists/*
RUN mkdir /run/haproxy
COPY haproxy.cfg /etc/haproxy/haproxy.cfg
CMD haproxy -f /etc/haproxy/haproxy.cfg && tail -F /var/log/haproxy.logBe careful, the last three lines of the haproxy.cfg file should list the addresses of your machines. HAproxy will communicate with Patroni, where the master server will always return 200 in the HTTP headers, and replicas will return 503.
haproxy.cfg
global
maxconn 100
defaults
log global
mode tcp
retries 2
timeout client 30m
timeout connect 4s
timeout server 30m
timeout check 5s
listen stats
mode http
bind *:7000
stats enable
stats uri /
listen postgres
bind *:5000
option httpchk
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server postgresql1 192.168.0.143:5432 maxconn 100 check port 8008
server postgresql2 192.168.0.144:5432 maxconn 100 check port 8008
server postgresql3 192.168.0.145:5432 maxconn 100 check port 8008
While in the directory where both of our files are located, let’s run the container packaging commands and start it with the necessary port forwarding:
docker build -t my-haproxy .
docker run -d -p5000:5000 -p7000:7000 my-haproxy Now, by opening your machine's address with HAproxy in the browser and specifying port 7000, you will see the statistics for your cluster.
The server that is the master will be in the UP state, while replicas will be in the DOWN state. This is normal; they are actually working but are displayed this way because they return 503 on requests from HAproxy. This lets us always know exactly which of the three servers is currently the master.
Conclusion
You are amazing! In just 30 minutes, you have set up an excellent fault-tolerant and high-performance database cluster with streaming replication and automatic failover. If you plan to use this solution, take a look , especially the part regarding the patronictl utility, which provides convenient access to manage your cluster.
Congratulations!
Source: habr.com
