Database PostgreSQL

PostgreSQL Yüksek Kullanılabilirlik Mimarileri 3- Patroni Cluster Kurulumu

By SysDbaOps Haz 16, 2023 , , , , , , ,

Merhaba PostgreSQL Yüksek Kullanılabilirlik serisinin önceki bölümlerinde Merkezi Consul Kümesi ve Consul Client kurulumlarından bahsetmiştik. Yazı serisinin bu bölümünde Patroni Cluster Kurulumu ile devam ediyoruz.

Patroni Nedir?

Patroni, PostgreSQL veri tabanı kümesini yönetmek için kullanılan açık kaynaklı yüksek kullanılabilirlik aracıdır. Patroni’nin temel amacı, PostgreSQL kümesinin yüksek kullanılabilirlik sağlaması ve otomatik geçiş süreçlerini yönetmesidir. Bir düğümün arızalandığı durumlarda otomatik olarak devreye girebilir ve veri tabanının kesintisiz çalışmasını sağlayabilir. Ayrıca, otomatik geri yükleme işlemlerini kolaylaştırarak veri bütünlüğünü korur.

Patroni Kurulumu

Patroninin tek başına kurulumu PostgreSQL kümesine yük devretme ve yüksek kullanılabilirlik niteliği kazandırmaya yeterli değildir. Patroni ile birlikte Dağıtık Konfigürasyon Depolama araçlarından birine ihtiyaç vardır. Bu gerekliliği gidermek içim Merkezi Consul Kümesi ve Consul Client kurulumlarını gerçekleştirmiştik. Patroni düğümlerinin kurulumunu Consul Client sunucuları üzerinde gerçekleştireceğiz.

Kuruluma başlamadan önce Consul Düğümlerinin durumlarını görüntüleyelim.

root@patroniSrv-1:~# consul members --token 759618f7-0690-6f62-b201-67160ff4c700
Node          Address              Status  Type    Build   Protocol  DC         Partition  Segment
consulSrv-1   192.168.222.11:8301  alive   server  1.11.4  2         sysdbaops  default    <all>
consulSrv-2   192.168.222.12:8301  alive   server  1.11.4  2         sysdbaops  default    <all>
consulSrv-3   192.168.222.13:8301  alive   server  1.11.4  2         sysdbaops  default    <all>
patroniSrv-1  192.168.222.14:8301  alive   client  1.11.4  2         sysdbaops  default    <default>
patroniSrv-2  192.168.222.15:8301  alive   client  1.11.4  2         sysdbaops  default    <default>
patroniSrv-3  192.168.222.16:8301  alive   client  1.11.4  2         sysdbaops  default    <default>

Patroni Kümesi İçin PostgreSQL Kurulumu

PostgreSQL ve Patroni kurulumları Consul Client sunucuları üzerinde gerçekleştirilecektir. İlk olarak PostgreSQL reposunu çekerek PostgreSQL kurulumunu gerçekleştiriyoruz.

root@patroniSrv-1:~# sudo su -
root@patroniSrv-1:~# echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
root@patroniSrv-1:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
root@patroniSrv-1:~# apt update
root@patroniSrv-1:~# apt install postgresql-15
root@patroniSrv-1:~# sudo su -
root@patroniSrv-1:~# echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
root@patroniSrv-1:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
root@patroniSrv-1:~# apt update
root@patroniSrv-1:~# apt install postgresql-15
root@patroniSrv-1:~# sudo su -
root@patroniSrv-1:~# echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
root@patroniSrv-1:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
root@patroniSrv-1:~# apt update
root@patroniSrv-1:~# apt install postgresql-15

Ubuntu Sunucularda PostgreSQL instance’ı /var/lib/postgresql/15/main dizini altında default olarak oluşturulmaktadır. Cluster’ı Patroni ile oluşturacağımız için ilk olarak üç sunucu üzerinde de instance’ı siliyoruz.

root@patroniSrv-1:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 down   postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
root@patroniSrv-1:~# pg_dropcluster 15 main --stop
root@patroniSrv-1:~# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file

root@patroniSrv-2:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 down   postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
root@patroniSrv-2:~# pg_dropcluster 15 main --stop
root@patroniSrv-2:~# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file

root@patroniSrv-3:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 down   postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
root@patroniSrv-3:~# pg_dropcluster 15 main --stop
root@patroniSrv-3:~# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file

PostgreSQL instance kurulumunu gerçekleştireceğimiz dizini oluşturarak gerekli yetkilendirmeleri yapıyoruz.

root@patroniSrv-1:~ mkdir -p /var/lib/pgdata/15/main
root@patroniSrv-1:~ chown -R postgres:postgres /var/lib/pgdata
root@patroniSrv-1:~ chmod 700 -R /var/lib/pgdata

root@patroniSrv-2:~ mkdir -p /var/lib/pgdata/15/main
root@patroniSrv-2:~ chown -R postgres:postgres /var/lib/pgdata
root@patroniSrv-2:~ chmod 700 -R /var/lib/pgdata
root@patroniSrv-3:~ mkdir -p /var/lib/pgdata/15/main
root@patroniSrv-3:~ chown -R postgres:postgres /var/lib/pgdata
root@patroniSrv-3:~ chmod 700 -R /var/lib/pgdata

PostgreSQL kurulumları esnasında dikkat edilmesi gereken bir diğer husus ise Türkçe Dil Paketlerinin sunucu üzerinde aktif hale getirilmesidir. Dil paketlerini aktif hale getirelim.

root@patroniSrv-1: localectl list-locales
C.UTF-8
en_US.UTF-8
root@patroniSrv-1: vi /etc/locale.gen
   tr_TR.UTF-8 UTF-8
root@patroniSrv-1: locale-gen-gen
root@patroniSrv-1: localectl list-locales
C.UTF-8
en_US.UTF-8
tr_TR.UTF-8

root@patroniSrv-2: localectl list-locales
C.UTF-8
en_US.UTF-8
root@patroniSrv-2: vi /etc/locale.gen
   tr_TR.UTF-8 UTF-8
root@patroniSrv-2: locale-gen-gen
root@patroniSrv-2: localectl list-locales
C.UTF-8
en_US.UTF-8
tr_TR.UTF-8

root@patroniSrv-3: localectl list-locales
C.UTF-8
en_US.UTF-8
root@patroniSrv-3: vi /etc/locale.gen
   tr_TR.UTF-8 UTF-8
root@patroniSrv-3: locale-gen-gen
root@patroniSrv-3: localectl list-locales
C.UTF-8
en_US.UTF-8
tr_TR.UTF-8

PostgreSQL kurulumunun son adımı olarak postgres kullanıcısı için .bash_profile tanımlaması gerçekleştiriyoruz.

root@patroniSrv-1: vi /var/lib/postgresql/.bash_profile
      export PGDATA=/var/lib/pgdata/15/main
      export PATH=$PATH:/usr/lib/postgresql/15/bin

root@patroniSrv-2: vi /var/lib/postgresql/.bash_profile
      export PGDATA=/var/lib/pgdata/15/main
      export PATH=$PATH:/usr/lib/postgresql/15/bin
root@patroniSrv-3: vi /var/lib/postgresql/.bash_profile
      export PGDATA=/var/lib/pgdata/15/main
      export PATH=$PATH:/usr/lib/postgresql/15/bin

Patroni Kurulumu

İlk olarak Patroni için gerekli Python paketlerinin kurulumunu gerçekleştiriyoruz. 

root@patroniSrv-1:~# apt install python3-pip python3-psycopg2
root@patroniSrv-1:~# pip3 install -U pip
root@patroniSrv-1:~# pip3 install patroni[consul]
root@patroniSrv-1:~# patroni --version
patroni 3.0.2

root@patroniSrv-2:~# apt install python3-pip python3-psycopg2
root@patroniSrv-2:~# pip3 install -U pip
root@patroniSrv-2:~# pip3 install patroni[consul]
root@patroniSrv-2:~# patroni --version
patroni 3.0.2

root@patroniSrv-3:~# apt install python3-pip python3-psycopg2
root@patroniSrv-3:~# pip3 install -U pip
root@patroniSrv-3:~# pip3 install patroni[consul]
root@patroniSrv-3:~# patroni --version
patroni 3.0.2

Kuruluma Patroni için gerekli dizin ve dosyaları oluşturarak devam ediyoruz.

root@patroniSrv-1:~# mkdir /etc/patroni
root@patroniSrv-1:~# mkdir /var/log/patroni
root@patroniSrv-1:~# chown -R postgres: /etc/patroni
root@patroniSrv-1:~# chown -R postgres: /var/log/patroni
root@patroniSrv-1:~# vi /etc/profile.d/patroni.sh
     export PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml

root@patroniSrv-2:~# mkdir /etc/patroni
root@patroniSrv-2:~# mkdir /var/log/patroni
root@patroniSrv-2:~# chown -R postgres: /etc/patroni
root@patroniSrv-2:~# chown -R postgres: /var/log/patroni
root@patroniSrv-2:~# vi /etc/profile.d/patroni.sh
     export PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml
root@patroniSrv-3:~# mkdir /etc/patroni
root@patroniSrv-3:~# mkdir /var/log/patroni
root@patroniSrv-3:~# chown -R postgres: /etc/patroni
root@patroniSrv-3:~# chown -R postgres: /var/log/patroni
root@patroniSrv-3:~# vi /etc/profile.d/patroni.sh
     export PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml

Patroni servis dosyasını düzenleyelim.

root@patroniSrv-1:~# vi /etc/systemd/system/patroni.service
[Unit] 
Description=Runners to orchestrate a high-availability PostgreSQL 
After=network.target 
ConditionPathExists=/etc/patroni/patroni.yml 
 
[Service] 
Type=simple 
 
User=postgres 
Group=postgres 
 
Environment="PATRONI_CONFIGURATION_FILE=/etc/patroni/patroni.yml" 
 
EnvironmentFile=-/etc/patroni/env.conf 
 
ExecStart=/usr/local/bin/patroni ${PATRONI_CONFIGURATION_FILE} 
ExecReload=/bin/kill -s HUP $MAINPID 
KillMode=process 
TimeoutSec=30 
Restart=no 
LimitNOFILE=262144 
OOMScoreAdjust=-1000 
 
[Install] 
WantedBy=multi-user.target 
root@patroniSrv-2:~# vi /etc/systemd/system/patroni.service
[Unit] 
Description=Runners to orchestrate a high-availability PostgreSQL 
After=network.target 
ConditionPathExists=/etc/patroni/patroni.yml 
 
[Service] 
Type=simple 
 
User=postgres 
Group=postgres 
 
Environment="PATRONI_CONFIGURATION_FILE=/etc/patroni/patroni.yml" 
 
EnvironmentFile=-/etc/patroni/env.conf 
 
ExecStart=/usr/local/bin/patroni ${PATRONI_CONFIGURATION_FILE} 
ExecReload=/bin/kill -s HUP $MAINPID 
KillMode=process 
TimeoutSec=30 
Restart=no 
LimitNOFILE=262144 
OOMScoreAdjust=-1000 
 
[Install] 
WantedBy=multi-user.target 
root@patroniSrv-3:~# vi /etc/systemd/system/patroni.service
[Unit] 
Description=Runners to orchestrate a high-availability PostgreSQL 
After=network.target 
ConditionPathExists=/etc/patroni/patroni.yml 
 
[Service] 
Type=simple 
 
User=postgres 
Group=postgres 
 
Environment="PATRONI_CONFIGURATION_FILE=/etc/patroni/patroni.yml" 
 
EnvironmentFile=-/etc/patroni/env.conf 
 
ExecStart=/usr/local/bin/patroni ${PATRONI_CONFIGURATION_FILE} 
ExecReload=/bin/kill -s HUP $MAINPID 
KillMode=process 
TimeoutSec=30 
Restart=no 
LimitNOFILE=262144 
OOMScoreAdjust=-1000 
 
[Install] 
WantedBy=multi-user.target

Patroni ve PostgreSQL konfigürasyonlarının tutulduğu patroni.yml dosyasını düzenleyelim.

root@patroniSrv-1:~# vi /etc/patroni/patroni.yml
namespace: "/postgres/"
scope: "sysdbaops-01"

log:
  level: INFO
  dir: /var/log/patroni

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      remove_data_directory_on_rewind_failure: true
      remove_data_directory_on_diverged_timelines: true
      parameters:
        archive_command: '/bin/true'
        archive_mode: "on"
        password_encryption : 'scram-sha-256'
        shared_preload_libraries: pg_stat_statements
        wal_compression: "on"
        wal_log_hints: "on"
      pg_hba:
        - local all             postgres                            peer
        - host  all             all             127.0.0.1/32        scram-sha-256
        - host  replication     standby         127.0.0.1/32        scram-sha-256
        - host  replication     standby         192.168.222.15/32     scram-sha-256
        - host  replication     standby         192.168.222.14/32     scram-sha-256
        - host  replication     standby         192.168.222.16/32     scram-sha-256
        - host  all             postgres        192.168.222.14/32     scram-sha-256
        - host  all             postgres        192.168.222.15/32     scram-sha-256
        - host  all             postgres        192.168.222.16/32     scram-sha-256
        - host  all             +dba            0.0.0.0/0             scram-sha-256

  initdb:
    - data-checksums

  users:
    standby:
      password: 123
      options:
        - replication
        - login

consul:
  host: 127.0.0.1:8501
  url: https://patroniSrv-1:8501
  verify: false
  cert: "/etc/ssl/certs/ssl-cert-snakeoil.pem"
  key: "/etc/ssl/private/ssl-cert-snakeoil.key"
  token: 759618f7-0690-6f62-b201-67160ff4c700
  dc: sysdbaops
  register_service: true

postgresql:
  authentication:
    superuser:
      username: postgres
      password: 123
    replication:
      username: standby
      password: 123
  connect_address: "192.168.222.14:5432"
  data_dir: /var/lib/pgdata/15/main
  config_dir: /var/lib/pgdata/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  listen: "0.0.0.0:5432"
  use_unix_socket: true
  pgpass: /var/lib/postgresql/.pgpass.patroni

restapi:
  connect_address: 192.168.222.14:8009
  listen: 0.0.0.0:8009

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

root@patroniSrv-2:~# vi /etc/patroni/patroni.yml

name: "pgdb02"
namespace: "/postgres/"
scope: "sysdbaops-01"

log:
  level: INFO
  dir: /var/log/patroni

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      remove_data_directory_on_rewind_failure: true
      remove_data_directory_on_diverged_timelines: true
      parameters:
        archive_command: '/bin/true'
        archive_mode: "on"
        password_encryption : 'scram-sha-256'
        shared_preload_libraries: pg_stat_statements
        wal_compression: "on"
        wal_log_hints: "on"
      pg_hba:
        - local all             postgres                            peer
        - host  all             all             127.0.0.1/32        scram-sha-256
        - host  replication     standby         127.0.0.1/32        scram-sha-256
        - host  replication     standby         192.168.222.15/32     scram-sha-256
        - host  replication     standby         192.168.222.14/32     scram-sha-256
        - host  replication     standby         192.168.222.16/32     scram-sha-256
        - host  all             postgres        192.168.222.14/32     scram-sha-256
        - host  all             postgres        192.168.222.15/32     scram-sha-256
        - host  all             postgres        192.168.222.16/32     scram-sha-256
        - host  all             +dba            0.0.0.0/0             scram-sha-256

  initdb:
    - data-checksums

  users:
    standby:
      password: 123
      options:
        - replication
        - login

consul:
  host: 127.0.0.1:8501
  url: https://patroniSrv-2:8501
  verify: false
  cert: "/etc/ssl/certs/ssl-cert-snakeoil.pem"
  key: "/etc/ssl/private/ssl-cert-snakeoil.key"
  token: 759618f7-0690-6f62-b201-67160ff4c700
  dc: sysdbaops
  register_service: true

postgresql:
  authentication:
    superuser:
      username: postgres
      password: 123
    replication:
      username: standby
      password: 123
  connect_address: "192.168.222.15:5432"
  data_dir: /var/lib/pgdata/15/main
  config_dir: /var/lib/pgdata/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  listen: "0.0.0.0:5432"
  use_unix_socket: true
  pgpass: /var/lib/postgresql/.pgpass.patroni

restapi:
  connect_address: 192.168.222.15:8009
  listen: 0.0.0.0:8009

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

root@patroniSrv-3:~# vi /etc/patroni/patroni.yml
name: "pgdb03"
namespace: "/postgres/"
scope: "sysdbaops-01"

log:
  level: INFO
  dir: /var/log/patroni

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      remove_data_directory_on_rewind_failure: true
      remove_data_directory_on_diverged_timelines: true
      parameters:
        archive_command: '/bin/true'
        archive_mode: "on"
        password_encryption : 'scram-sha-256'
        shared_preload_libraries: pg_stat_statements
        wal_compression: "on"
        wal_log_hints: "on"
      pg_hba:
        - local all             postgres                            peer
        - host  all             all             127.0.0.1/32        scram-sha-256
        - host  replication     standby         127.0.0.1/32        scram-sha-256
        - host  replication     standby         192.168.222.15/32     scram-sha-256
        - host  replication     standby         192.168.222.14/32     scram-sha-256
        - host  replication     standby         192.168.222.16/32     scram-sha-256
        - host  all             postgres        192.168.222.14/32     scram-sha-256
        - host  all             postgres        192.168.222.15/32     scram-sha-256
        - host  all             postgres        192.168.222.16/32     scram-sha-256
        - host  all             +dba            0.0.0.0/0             scram-sha-256

  initdb:
    - data-checksums

  users:
    standby:
      password: 123
      options:
        - replication
        - login

consul:
  host: 127.0.0.1:8501
  url: https://patroniSrv-3:8501
  verify: false
  cert: "/etc/ssl/certs/ssl-cert-snakeoil.pem"
  key: "/etc/ssl/private/ssl-cert-snakeoil.key"
  token: 759618f7-0690-6f62-b201-67160ff4c700
  dc: sysdbaops
  register_service: true

postgresql:
  authentication:
    superuser:
      username: postgres
      password: 123
    replication:
      username: standby
      password: 123
  connect_address: "192.168.222.16:5432"
  data_dir: /var/lib/pgdata/15/main
  config_dir: /var/lib/pgdata/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  listen: "0.0.0.0:5432"
  use_unix_socket: true
  pgpass: /var/lib/postgresql/.pgpass.patroni

restapi:
  connect_address: 192.168.222.16:8009
  listen: 0.0.0.0:8009

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

Primary veri tabanı önce olmak üzere patroni servislerini başlatalım.

root@patroniSrv-1:~# systemctl start patroni.service
root@patroniSrv-2:~# systemctl start patroni.service
root@patroniSrv-3:~#systemctl start patroni.service

Son olarak Herhangi bir node üzerinden PostgreSQL Veri tabanlarımızın durumunu görüntüleyelim.

root@patroniSrv-1:~# su - postgres
postgres@patroniSrv-1:~# patronictl list

+ Cluster: sysdbaops-01 --+---------+---------+----+-----------+
| Member | Host           | Role    | State   | TL | Lag in MB |
+--------+----------------+---------+---------+----+-----------+
| pgdb01 | 192.168.222.14 | Leader  | running |  3 |           |
| pgdb02 | 192.168.222.15 | Replica | running |  3 |         0 |
| pgdb03 | 192.168.222.16 | Replica | running |  3 |         0 |
+--------+----------------+---------+---------+----+-----------+

Patroni ile PostgreSQL kümesi kurulumunu tamamlayarak bir yazının daha sonuna gelmiş olduk, yazı serisine ilk olarak KeepAlived ve HaProxy kurulumları ardından ise PgBouncer kurulumu ile devam edeceğiz. Ayrıca Sayfamızı Linkedin üzerinden de takip edebilirsiniz.

Clickhouse

ClickhouseDB Cluster Kurulumu

NoSQL

MongoDB Authorization Parametresi

Database PostgreSQL

PostgreSQL Yüksek Kullanılabilirlik Mimarileri 3- Patroni Cluster Kurulumu

Database PostgreSQL

PostgreSQL Yüksek Kullanılabilirlik Mimarileri 2- Consul Client Kurulumu

Paylaş
Facebook
X (Twitter)
LinkedIn
Mix
Email
Yazdır
Bağlantıyı kopyala
Powered by Social Snap
Bağlantıyı kopyala
KopyalaKopyalandı
Powered by Social Snap