PostgreSQL High Availability installations Patroni

tayfun inam June 22, 2021 1 Comment

Hello, today I will explain the installation of high availability patroni.

1-PostgreSQL software installation
2-Patroni software installation
3-etcd software installation, etcd conf.
4-patroni conf.
5-pgbackrest setup and conf.
6-haproxy and keepalived
7-All other conf.

PostgreSQL Software

yum install -y postgresql12-server postgresql12-client postgresql12-contrib postgresql12-devel

Patroni Software

export PATH="$PATH:/usr/pgsql-12/bin"

yum install -y python3-pip-9.0.3-5.el7.noarch gcc python3-devel

pip3 install --upgrade pip wheel
pip3 install psycopg2-binary
pip3 install psycopg2>=2.5.4
pip3 install patroni[etcd]

etcd installation and conf

yum install -y etcd
--vi /etc/etcd/etcd.conf
##--pgdb-1
ETCD_INITIAL_CLUSTER="etcd1=http://xxx.xxx.xx.xx:2380,etcd2=http://xxx.xxx.xx.xx:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-01"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://xxx.xxx.xx.xx:2380"
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://xxx.xxx.xx.xx:2380"
ETCD_LISTEN_CLIENT_URLS="http://xxx.xxx.xx.xx:2379,http://127.0.0.1:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://xxx.xxx.xx.xx:2379"
ETCD_NAME="etcd1"

##--pgdb-2
ETCD_INITIAL_CLUSTER="etcd1=http://xxx.xxx.xx.xx:2380,etcd2=http://xxx.xxx.xx.xx:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-01"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://xxx.xxx.xx.xx:2380"
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://xxx.xxx.xx.xx:2380"
ETCD_LISTEN_CLIENT_URLS="http://xxx.xxx.xx.xx:2379,http://127.0.0.1:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://xxx.xxx.xx.xx:2379"
ETCD_NAME="etcd2"

systemctl enable etcd
systemctl start etcd
systemctl status etcd
systemctl stop etcd

Patroni conf.

The directory is created for the patroni yaml file. and postgres is authorized

mkdir /etc/patroni/
chown postgres:postgres /etc/patroni/

mkdir /postgres/
chown postgres:postgres /postgres/

–// yml file should be set according to server IP addresses and memory etc. according to server resources. settings must be made.

scope: pgdb-patroni
name: postgres1
restapi:
listen: xxx.xxx.xx.xx:8008
connect_address: xxx.xxx.xx.xx:8008
etcd:
hosts: 1xxx.xxx.xx.xx:2379,xxx.xxx.xx.xx:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
wal_level: hot_standby
hot_standby: "on"
wal_keep_segments: 8
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
max_connections: 200
shared_buffers: 1GB
effective_cache_size: 3GB
maintenance_work_mem: 256MB
checkpoint_completion_target: 0.7
wal_buffers: 16MB
default_statistics_target: 100
random_page_cost: 1.1
effective_io_concurrency: 300
work_mem: 5242kB
min_wal_size: 1GB
max_wal_size: 4GB
max_worker_processes: 2
max_parallel_workers_per_gather: 1
max_parallel_workers: 2
max_parallel_maintenance_workers: 1
archive_mode: "on"
archive_timeout: 1800s
archive_command: "pgbackrest --stanza=pgdb-patroni archive-push %p"
recovery_conf:
restore_command: "pgbackrest --stanza=pgdb-patroni archive-get %f %p"
standby_mode: "on"

initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator xxx.xxx.xx.xx/24 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: xxx.xxx.xx.xx:5432
connect_address: xxx.xxx.xx.xx:5432
data_dir: /postgres/data/
bin_dir: /usr/pgsql-12/bin
pgpass: /tmp/pgpass0
authentication:
replication:
username: replicator
password: rep-pass
superuser:
username: postgres
password: postgres
rewind:
username: rewind_user
password: rewind_password
parameters:
unix_socket_directories: '/var/run/postgresql/'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false

scope: pgdb-patroni
name: postgres2
restapi:
listen: xxx.xxx.xx.xx:8008
connect_address: xxx.xxx.xx.xx:8008
etcd:
hosts: xxx.xxx.xx.xx:2379,xxx.xxx.xx.xx:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
wal_level: hot_standby
hot_standby: "on"
wal_keep_segments: 8
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
max_connections: 200
shared_buffers: 1GB
effective_cache_size: 3GB
maintenance_work_mem: 256MB
checkpoint_completion_target: 0.7
wal_buffers: 16MB
default_statistics_target: 100
random_page_cost: 1.1
effective_io_concurrency: 300
work_mem: 5242kB
min_wal_size: 1GB
max_wal_size: 4GB
max_worker_processes: 2
max_parallel_workers_per_gather: 1
max_parallel_workers: 2
max_parallel_maintenance_workers: 1
archive_mode: "on"
archive_timeout: 1800s
archive_command: "pgbackrest --stanza=pgdb-patroni archive-push %p"
recovery_conf:
restore_command: "pgbackrest --stanza=pgdb-patroni archive-get %f %p"
standby_mode: "on"
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator xxx.xxx.xx.xx/24 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: xxx.xxx.xx.xx:5432
connect_address: xxx.xxx.xx.xx:5432
data_dir: /postgres/data/
bin_dir: /usr/pgsql-12/bin
pgpass: /tmp/pgpass0
authentication:
replication:
username: replicator
password: rep-pass
superuser:
username: postgres
password: postgres
rewind:
username: rewind_user
password: rewind_password
parameters:
unix_socket_directories: '/var/run/postgresql/'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
patroni service is created

 more /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/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no

[Install]
WantedBy=multi-user.target


systemctl enable patroni
systemctl start patroni
systemctl status patroni
systemctl stop patroni
journalctl -xe -f -u patron

The same confs are made in other servers and the service is run.

patronictl -d xxx.xxx.xx.xx list pgdb-patroni

+ Cluster: pgdb-patroni (xxx.xxx.xx.xx) -+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+-----------+---------------+--------+---------+----+-----------+
| postgres1 | xxx.xxx.xx.xx | Leader | running | 1 | |
| postgres2 | xxx.xxx.xx.xx | | running | 1 | 0 |
+-----------+---------------+--------+---------+----+-----------+

Note: Since we haven't installed pgbackrest until now, we may get an error when installing postgresql via patroni on 2.
If we restart patroni services after installing backrest, the problem will be solved. Or you can set up a backrest before starting these patrons.
patronictl -d xxx.xxx.xx.xx list pgdb-patroni

patronictl -d xxx.xxx.xx.xx reinit pgdb-patroni

patronictl -c /etc/patroni/patroni.yml list

About tayfun inam

Computer Engineer -PostgreSql-Oracle Database Administrator I am about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 8+years experience.I have OCA,PostgreSql senior Certificates I have worked 50+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.

One comment

  1. Terry
    September 20, 2021 at 11:18 am

    Where is the information regarding pgBackrest config – Item (5) – Could you post this please?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Powered by IT Tutorial | Designed by SysDBASoft IT
IT Tutorial © Copyright 2024, All Rights Reserved