ααΆααΆαααΎαα‘αΎαααΌα ααααααΆ αα αααααΆααααα αΆααΎαα‘αΎα αααα»ααα·αααΆααααα·ααααααααααααααΆαααααα»αααΆαα’αα·αααααα αα·αα αΆααααααΎααααααααααΆααααααααααΆααα―ααααααα α αΎααααααΆαααααααα»αα αΆααααααΎα Google α
αααα»ααα·αααΉαααΆααΆαα αΆαααααααΆα’αααΈααααα ααα»αααααααααΆααααΎαααΈααααα½ααααα»αααααααα»αααΉαααΆααα·ααααααΆαααααΈααΆαααα»αααααΎα’αααΈαααααααααΆααα½αααα αΆααααααααΌα αα αααα»ααααααααααα αααααα αααα·ααΆααΆαααΌα α’ααααα·ααααααα αααααααΆαα’αααΈαααααΆαααααα·αααααΆααααααα αααα»ααα·αααΉαααΆααΆαααΏαα’αααΈααααα ααα»αααααα αααααααααα»ααα½ααααα αΆαααααααααα αααα»αααΆααααααα α α·αααααΆαααα»αααΉααααααααΆαααααααααααΆαααααα½ααααααααα»α αα ααααααα’αααΈαααααΎαααΆαα αα½ααααααααΆααααΆααΉαααααΎαααΆαα
ααΆαααααΆααα ααΎα’ααΈαααΊαα·α
ααΆααΎαα‘αΎαααΌα ααααααΆα’ααΈαααΊαα·ααα·αααα½αααααΈααΆαααααααΆαααααΆαααααΆααααααα ααΆααααααα ααα αΆααααααα αΆα αα·αααααααααα ααΆααΆαααΎαα‘αΎαααΌα ααααααΆαααα»αααααΌαααΆαααααααααΆααα·α αα α±αααααααΎααααααααααΆααααααΆααααΆααααα ααααΆαααΆααααα½α αα·ααααααΎαα ααααα PostgreSQL ααααααΆααα αααααΆαααααΌαααΆαα αααααααααΆααααΆαααααΆαα ααααααΈαααΆαααΈααααα αααΆαααΈαα ααααααΆααα’αα αα·αααΆαααααααα»ααααα»ααααααααααααααααα·αα αααα»αααααΉαααα·ααΆααααααααΆαααΈαααααα αααΆαααα
αα ααααΆααααΆααααααααααα αα αααα·ααααΆαααααΆαααααΎααααΌαααΆαααααα:
- PostgreSQL ααΆ DBMS
αααΆααΌααΈ ααΆαααααααααΆαα ααααα- etcd ααΆααΆααααα»αα ααα αΆααααααΆαα Patroni
- HAproxy αααααΆαααααα αα ααα»α α αΌααααα½ααααααΆαααααααα·ααΈαααααααΎααΌαααααΆααα·αααααα
ααΆαααααα
αααααΆααααΆαααα α·ααααα»αααΆααααααα’ααα - ααΆααααΆαα ααααα PostgreSQL αααααΆαααααα·αααααααααααααΎ Patroni, etcd, HAProxy α
ααααα·ααααα·ααΆαααΆααα’ααααααΌαααΆαα’αα»αααααα ααΎαααΆαααΈααα·αααα·αααΆαα½αααΉα Debian 10 OS αααααΆαααα‘αΎαα
α
αααα»ααα·αααααΆαα±ααααα‘αΎα etcd αα
ααΎαααΆαααΈαααΌα
ααααΆααα patroni αα·α postgresql ααΉαααΆαααΈααΆαααα αααααααΆααααα»αααΆαααΆαααΆααααααΆααααααΆααααΆαααααααΆαα etcd α ααα»ααααβαααααΆααβαααβααααβα’αααα ααΎαβααΉαβααααΎβαααβαααα
αααααα‘αΎα αα
#!/bin/bash
apt-get update
apt-get install etcd
ααααααααΆαα·ααΆαα α―αααΆα /etc/default/etcd
[αααΆαα·α]
ETCD_NAME=datanode1 # ααααααααΆαααΈααααααΆαααΈαααααα’αααα
ETCD_DATA_DIR=β/var/lib/etcd/default.etcdβ
α’αΆααααααΆα IP ααΆααα’αααα½αααααΆααα»ααααΆαα LISTER PEER, Client ααΆααΎα αα½αααααααΌαααΆαααααααα α’αΆααααααΆα IP αααααααΆαααΈα
ETCD_LISTEN_PEER_URLS="
ETCD_LISTEN_CLIENT_URLS="
[α ααααα]
ETCD_INITIAL_ADVERTISE_PEER_URLS="
ETCD_INITIAL_CLUSTER=Β»datanode1=
ETCD_INITIAL_CLUSTER_STATE="ααααΈ"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-1β³
ETCD_ADVERTISE_CLIENT_URLS="
ααααα·ααααα·ααΆααααααααΆ
systemctl restart etcd
PostgreSQL 9.6 + patroni
ααΏαααααΌααααα’αααααααΌαααααΎααΊααα‘αΎααααΆαααΈααα·αααα·αα ααα½αααΈααΎααααΈααα‘αΎααααααα·ααΈα αΆαααΆα ααα ααΎαα½αααΆα αααααΆααααΈααα‘αΎααααΆαααΈαα αΎα ααααα·αααΎα’αααααααΎααΆαααΆααααααααααααααα»α α’αααα’αΆα ααααΎαααΆαααααααΈαααααΆααααααααααααΉα (ααααΎααα) ααααΎα’αααΈαααααααααΆααααααΆααα’αααα ααααΎαααΆαααΆ root α
ααΌαα αααΆαααΆααααααΈαααααΎ PostgreSQL αααα 9.6 αααααΊαααααΆααααααααΌαααΆααααααααα»ααααααααα»αα αα»αααααααΎαα αααααααααΆααα·αααααΌαααΆαααΆαααααααΎαααααααααααααα 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.yml
αααααΆαααα αα
αααα»αα―αααΆα /etc/patroni.yml αααα’αααααΎααααααααΎα α’αααααααΌαααΆααααΆαα·ααΆααΆαααααα ααΆααΆααα·αααΆαααααΆααααααΌαα’αΆααααααΆα IP αα
ααααααααααααα
α’αΆααααααΆααααα’αααααααΎα
ααα
α·ααααα»αααΆααα
ααααααα·ααααααα
αααα»α yaml αααα ααααΆααααααΌαα’αΆααααααΆααα
ααααΆαααααα½αααααα’ααααα
ααΎαααΆαααΈαααΈαα½αααα
αααα»αα
αααααα
/etc/patroni.yml
scope: pgsql # Π΄ΠΎΠ»ΠΆΠ½ΠΎ Π±ΡΡΡ ΠΎΠ΄ΠΈΠ½Π°ΠΊΠΎΠ²ΡΠΌ Π½Π° Π²ΡΠ΅Ρ
Π½ΠΎΠ΄Π°Ρ
namespace: /cluster/ # Π΄ΠΎΠ»ΠΆΠ½ΠΎ Π±ΡΡΡ ΠΎΠ΄ΠΈΠ½Π°ΠΊΠΎΠ²ΡΠΌ Π½Π° Π²ΡΠ΅Ρ
Π½ΠΎΠ΄Π°Ρ
name: postgres1 # Π΄ΠΎΠ»ΠΆΠ½ΠΎ Π±ΡΡΡ ΡΠ°Π·Π½ΡΠΌ Π½Π° Π²ΡΠ΅Ρ
Π½ΠΎΠ΄Π°Ρ
restapi:
listen: 192.168.0.143:8008 # Π°Π΄ΡΠ΅Ρ ΡΠΎΠΉ Π½ΠΎΠ΄Ρ, Π² ΠΊΠΎΡΠΎΡΠΎΠΉ Π½Π°Ρ
ΠΎΠ΄ΠΈΡΡΡ ΡΡΠΎΡ ΡΠ°ΠΉΠ»
connect_address: 192.168.0.143:8008 # Π°Π΄ΡΠ΅Ρ ΡΠΎΠΉ Π½ΠΎΠ΄Ρ, Π² ΠΊΠΎΡΠΎΡΠΎΠΉ Π½Π°Ρ
ΠΎΠ΄ΠΈΡΡΡ ΡΡΠΎΡ ΡΠ°ΠΉΠ»
etcd:
hosts: 192.168.0.143:2379,192.168.0.144:2379,192.168.0.145:2379 # ΠΏΠ΅ΡΠ΅ΡΠΈΡΠ»ΠΈΡΠ΅ Π·Π΄Π΅ΡΡ Π²ΡΠ΅ Π²Π°ΡΠΈ Π½ΠΎΠ΄Ρ, Π² ΡΠ»ΡΡΠ°Π΅ Π΅ΡΠ»ΠΈ Π²Ρ ΡΡΡΠ°Π½Π°Π²Π»ΠΈΠ²Π°Π΅ΡΠ΅ etcd Π½Π° Π½ΠΈΡ
ΠΆΠ΅
# this section (bootstrap) will be written into Etcd:/<namespace>/<scope>/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 Π΄ΠΎΠ»ΠΆΠ΅Π½ ΡΠΎΠ΄Π΅ΡΠΆΠ°ΡΡ Π°Π΄ΡΠ΅ΡΠ° ΠΠ‘ΠΠ₯ ΠΌΠ°ΡΠΈΠ½, ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌΡΡ
Π² ΠΊΠ»Π°ΡΡΠ΅ΡΠ΅
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 # Π°Π΄ΡΠ΅Ρ ΡΠΎΠΉ Π½ΠΎΠ΄Ρ, Π² ΠΊΠΎΡΠΎΡΠΎΠΉ Π½Π°Ρ
ΠΎΠ΄ΠΈΡΡΡ ΡΡΠΎΡ ΡΠ°ΠΉΠ»
connect_address: 192.168.0.143:5432 # Π°Π΄ΡΠ΅Ρ ΡΠΎΠΉ Π½ΠΎΠ΄Ρ, Π² ΠΊΠΎΡΠΎΡΠΎΠΉ Π½Π°Ρ
ΠΎΠ΄ΠΈΡΡΡ ΡΡΠΎΡ ΡΠ°ΠΉΠ»
data_dir: /data/patroni # ΡΡΡ Π΄ΠΈΡΠ΅ΠΊΡΠΎΡΠΈΡ ΡΠΎΠ·Π΄Π°ΡΡ ΡΠΊΡΠΈΠΏΡ, ΠΎΠΏΠΈΡΠ°Π½Π½ΡΠΉ Π²ΡΡΠ΅ ΠΈ ΡΡΡΠ°Π½ΠΎΠ²ΠΈΡ Π½ΡΠΆΠ½ΡΠ΅ ΠΏΡΠ°Π²Π°
bin_dir: /usr/lib/postgresql/9.6/bin # ΡΠΊΠ°ΠΆΠΈΡΠ΅ ΠΏΡΡΡ Π΄ΠΎ Π²Π°ΡΠ΅ΠΉ Π΄ΠΈΡΠ΅ΠΊΡΠΎΡΠΈΠΈ Ρ postgresql
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: false
ααααααΈαααααΌαααααααΎαααΆαααΎαααΆαααΈαααΆααααΈααα ααααα α αΎαααΆααααααααΆαααΎααααααΌαααααΆαααααα»αα―αααΆα /etc/patroni.yml αα ααΎαααΆαααΈαααΆααα’ααα
αα ααααααα’αααααΆααααα ααααααα·ααααα·ααΆαααΆααααααα ααΎαααΆαααΈαααΆααα’αααα αααα»αα ααααα ααΌαααααΎαααΆαααΆααααααααΆααΆαααααααα ααΎαα½αααΆααΆαα½αα
systemctl start patroni
systemctl start postgresql
αααα αΆααααα αα 30 αα·ααΆααΈ αααααΆααααααααΎαααΆαααΆααααααααΆααααα ααΎαααΆαααΈαααααα ααααααα»αα αααααα
HAproxy
ααΎαααααΎ HAproxy ααα’ααα αΆαααααΎααααΈαααααααΌαα ααα»α α αΌααααα½αα αααΆαααΈαααααααΉααααααααΆααα α’αΆααααααΆααααααααΆαααΈαααα HAproxy ααααΌαααΆαααΆααα±ααααααΎααααΆααα
ααΎααααΈαα»αα±αααααΆαααΈαααΆαα½α HAproxy ααααΆαααΆα ααα»α αααΆααααααα½α ααΎαααΉαααΎαααααΎαααΆαααΆαα αααα»α Docker container α αΎααα αααα’ααΆαα ααΆα’αΆα ααααΌαααΆαααΆααα±ααααααΎαααΆααα αααα»αα ααααααααα K8 αα·αααααΎα±αα cluster αααΆαααααααααΎαααΆαααααα½αα±αααα»αα α·αααα
αααααΎαααα―αααΆααααα’αααα’αΆα αααααΆαα»αα―αααΆαααΈα - Dockerfile αα·α haproxy.cfg α αα ααΆα
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.log
ααΌααααααααα αααααΆααααΈα α»ααααααααα―αααΆα haproxy.cfg αα½αααααΆααααααΈα’αΆααααααΆααααααΆαααΈαααααα’αααα HAproxy ααΉαααΆαααα Patroni αα αααα»αααααααΆ HTTP αααΆαααΈαααααΉαααααααααα‘αα 200 α αΎαααΆαα ααααααΉαααααααααα‘αα 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
ααααααα·ααα αααα»αααα―αααΆααααα―αααΆαααΆααααΈαααααααΎα "αα»α α" ααΌαααααα·ααααα·ααΆαααααΆαααααααααΌαααΆααααααααΆαααααΆααααΆαααα ααα αααα»αααΊααα ααααΌα ααΆααΆαααΎαααααΎαααΆαααΆααΆαα½αααΉαααΆααααααΌαααααα αααα αΆαααΆα αα
docker build -t my-haproxy .
docker run -d -p5000:5000 -p7000:7000 my-haproxy
α₯α‘αΌαααα αααααΎαα’αΆααααααΆααααΆαααΈαααααα’αααααΆαα½α HAproxy αα αααα»ααααααα·ααΈαα»ααα α αΎααααααΆααα ααα 7000 α’αααααΉαααΎααααα·αα·αα ααΎα αααααααααα’αααα
αααΆαααΈααααααααΆααααΉααααα·ααααα»αααααΆαααΆα UP α αΎαααΆαα ααααααΉααααα·ααα αααα»αααααΆαααΆαα α»ααααααα αααααΆααΏαααααααΆαα ααΆααα·ααα½αααααααΎαααΆα ααα»αααααα½αααΆααα α‘αΎαααΆαααααααα αααααΆααα½ααααααα‘αα 503 αααααΆααααααΎααΈ HAproxy α αααα’αα»ααααΆαα±ααααΎαααΉαααΆαα·α αα ααΆ ααΎαα½αααΆαααα»αα αααααααΆαααΈαααααΆααααΈααΆαααα αα α»ααααααα
ααα ααααΈααααα·ααααΆα
α’αααβααΊβαααααβααα’αΆαβ! αααα»αααααααααααΉααα 30 ααΆααΈααα»ααααα α’αααααΆαααΆαααααααΆαααΌαα
αααααααΌαααααΆααα·ααααααααααααααααΆαααΉαααα α»α αα·αααααα·αααααΆααααααααΆαα½αααΉαααΆαα
ααααααααααΈα αα·αααΆαααααααααααααααααααααααα·α ααααα·αααΎα’αααααΆαααααααααααΎαααααααααΆαααα ααΌααα·αα·αααααΎα
α’αα’αααΆαα!
ααααα: www.habr.com