PostgreSQL’i balans yapmak için bir kaç türlü yöntem bulunuyor. Ben Pgpool ve HaProxy ile denedim.PgPool’dan performans açısından memnun kalmadım. HaProxy hem yapılandırması hem de kontrolü daha kolay ve performans açısından daha fazla performans aldım.

Kurulumları Centos 7 üzerine yapıyorum. Yapım şu şekilde;

Bir adet Centos 7 HaProxy Server, 5 adet Centos 7 PostgreSQL Server. Bir tanesi master olacak 4 adeti de slave sunucular olacak. Sırası ile sunucu ipleri aşağıdaki şekilde;

Haproxy : 192.168.100.10

Master : 192.168.100.11

Slave : 192.168.100.12 – 13 – 14 – 15

Öncelikle PostgreSQL kurulumları;

Ben kurulum için PostgreSQL’in 12 versiyonunu tercih ediyorum. Önce master sunucuda işlemleri yapıyoruz.

Rpm dosyasını çekelim;

yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Epel reposunu kuralım;

yum -y install epel-release yum-utils

Postgresql yum üzerinde aktif hale getirelim;

yum-config-manager --enable pgdg12

Postgresql Server’i ve postgresql client’i kuralım;

yum install postgresql12-server postgresql12 -y

initdb ile postgresql kurulumunu tamamlayalım;

/usr/pgsql-12/bin/postgresql-12-setup initdb

Postgresql servisini başlangıçta çalışacak şekilde aktif edelim.

systemctl enable --now postgresql-12

Servis çalışıyor mu diye kontrol edelim?

systemctl status postgresql-12

Servisimiz sağlıklı şekilde çalışıyor.

Slave sunucu kurulumları;

4 adet slave sunucuda aynı işlemleri yapacağız. Master sunucudan tek farkımız initdb çalıştırmamamız gerekiyor. Çünkü Postgresql’in datalarını master sunucudan çekeceğiz. Yukarıdaki /usr/pgsql-12/bin/postgresql-12-setup initdb harici tüm kurulumları slave için aynı şekilde.

Master da dahil tüm sunucularda repmgr kurmamız gerekiyor. Repmgr cluster yapımızı oluşturacak. Postgresql versiyonumuz 12 olduğundan repmgr versiyonumuzun da aynı olması gerekiyor.

Repmgr kurulumu;

yum -y  install repmgr12*

/var/lib/pgsql/12/data/ klasöründe postgresql.conf dosyasında ince ayarlar bulunuyor. Bu kısma daha sonra değinebiliriz. Fakat yine bu dizinde bulunan pg_hba.conf dosyasında bağlanacak istemciler ve slave sunucular için işlem yapmamız gerekiyor.

host    repmgr          repmgr       192.168.100.0/24              trust

Ben repmgr kullanıcının sadece repmgr veritabanına 192.168.100.0/24 subnetinden bağlanması için bu satırı ekledim. Dilerseniz;

host    all             all             192.168.100.0/24            trust
host    all             all             192.168.100.0/24            trust

bu şekilde komple bu subnet’e izin de verebiliriz.

Repmgr yapılandırma;

Yapılandırma dosyası /etc/repmgr/12/ dizininde repmgr.conf dosyası şeklinde. Favori text editörümüz ile dosyayı açalım;

nano /etc/repmgr/12/repmgr.conf

Aşağıdaki konfigürasyon şeklinde değiştirelim;

node_id=1               # A unique integer greater than zero
node_name=MasterNode    # An arbitrary (but unique) string; we recommend
                                 # using the server's hostname or another identifier
                                 # unambiguously associated with the server to avoid
                                 # confusion. Avoid choosing names which reflect the
                                 # node's current role, e.g. "primary" or "standby1",
                                 # as roles can change and it will be confusing if
                                 # the current primary is called "standby1".
conninfo='host=192.168.100.11 port=5432 user=repmgr dbname=repmgr'                         # Database connection information as a conninfo string.
data_directory='/var/lib/pgsql/10/data'          # The node's data directory. This is needed
                                                # by repmgr when performing operations when the PostgreSQL
                                                # instance is not running and there's no other way of determining
                                 # the data directory.
pg_bindir='/usr/pgsql-12/bin/'                          # Path to PostgreSQL binary directory (location
                                        # of pg_ctl, pg_basebackup etc.). Only needed
                                        # if these files are not in the system $PATH.
failover=automatic                      # one of 'automatic', 'manual'.
reconnect_attempts=3                    # Number attempts which will be made to reconnect to an unreachable
                                        # primary (or other upstream node)
reconnect_interval=5                    # Interval between attempts  to reconnect to an unreachable
                                        # primary (or other upstream node)
promote_command='/usr/pgsql-12/bin/repmgr standby promote -f /etc/repmgr/12/repmgr.conf'                        # command repmgrd executes when promoting a new prim$
follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /etc/repmgr/12/repmgr.conf'                  # command repmgrd executes when instructing a standby to fol$

Node_id ve node_name cluster yapımızda gözükecek ona görelim isim veriyoruz. conninfo satırında bağlantı dizimizi yazıyoruz. Benim master sunucumun ip adresi 192.168.100.11 şeklindeydi. Otomatik şekilde failover veriyoruz ve 3 defa tekrar bağlanmayı deniyoruz. Şimdi master sunucu üzerinde repmgr veritabanı ve repmgr kullanıcısını oluşturup yetki vermemiz gerekiyor;

öncelikle postgres kullanıcısına geçmemiz gerekiyor;

su - postgres

Sonra postgresql’in cli’sine düşelim;

psql

Veritabanı ve kullanıcıyı oluşturup, yetkilerini verelim;

create user repmgr;
create database repmgr with owner repmgr;
alter role repmgr with superuser;
\q

Artık master sunucumuz cluster’a register edebiliriz;

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf master register

Master sunucumuz register oldu mu diye bir kontrol edelim;

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf cluster show

Gördüğümüz üzere master sunucumuz cluster’a register olmuş durumda. Master sunucuda şimdilik işlemlerimiz tamamlandı. Slave sunucular üzerinde de repmgr kurulumlarını yapalım;

yum -y  install repmgr12*

repmgr.conf dosyasını her slave sunucuda ip adresi şeklinde yukarıdaki conf dosyası gibi ayarlayalım;

node_id=2               # A unique integer greater than zero
node_name=SlaveNode1     # An arbitrary (but unique) string; we recommend
                                 # using the server's hostname or another identifier
                                 # unambiguously associated with the server to avoid
                                 # confusion. Avoid choosing names which reflect the
                                 # node's current role, e.g. "primary" or "standby1",
                                 # as roles can change and it will be confusing if
                                 # the current primary is called "standby1".
conninfo='host=192.168.100.12 port=5432 user=repmgr dbname=repmgr'                         # Database connection information as a conninfo string.
data_directory='/var/lib/pgsql/12/data'          # The node's data directory. This is needed
                                                # by repmgr when performing operations when the PostgreSQL
                                                # instance is not running and there's no other way of determining
                                 # the data directory.
pg_bindir='/usr/pgsql-12/bin/'                          # Path to PostgreSQL binary directory (location
                                        # of pg_ctl, pg_basebackup etc.). Only needed
                                        # if these files are not in the system $PATH.
failover=manual                 # one of 'automatic', 'manual'.
reconnect_attempts=3                    # Number attempts which will be made to reconnect to an unreachable
                                        # primary (or other upstream node)
reconnect_interval=5                    # Interval between attempts  to reconnect to an unreachable
                                        # primary (or other upstream node)
promote_command='/usr/pgsql-12/bin/repmgr standby promote -f /etc/repmgr/12/repmgr.conf'                        # command repmgrd executes when promoting a new prim$
follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /etc/repmgr/12/repmgr.conf'                  # command repmgrd executes when instructing a standby to fol$

node_id, node_name ve bağlantı dizisini değiştirdik sadece. Postgresql kurulumlarını yapıp, initdb çalıştırmamıştık. Şimdi slave sunucumuzu, upstream’ı master olacak şekilde register etmemiz gerekiyor. Lakin önce postgresql’i çalıştırmalıyız. Bunun için de master sunucunun clone’u nu alacağız. Önce postgres kullanıcısına geçelim;

su - postgres

Sonra clone alalım;

/usr/pgsql-12/bin/repmgr -h 192.168.100.11 -p 5432 -U repmgr -d repmgr -D /var/lib/pgsql/12/data/ -f /etc/repmgr/12/repimgr.conf standby clone

Not : Eğer master sunucuya ulaşamayıp, connection refused verirse, yüksek ihtimal firewalld servisi çalışıyordur. Tüm sunucularda şimdilik firewalld servisini kapatabilirsiniz. Daha sonra 5432 portuna belirli iplerden izin verebilirsiniz. (systemctl firewalld stop)

Clone işlemi bitince, master sunucusunda bulunan tüm postgresql datasını slave sunucuya çekecektir. Artık postgresql servisini başlatabiliriz;

systemctl start postgresql-12.service

Şimdi slave sunucuyu register edelim;

/usr/pgsql-12/bin/repmgr -h 192.168.100.11 -U repmgr -d repmgr -f /etc/repmgr/12/repmgr.conf standby register

Register olurken hata verirse force parametresini de kullanabilirsiniz;

/usr/pgsql-12/bin/repmgr -h 192.168.100.11 -U repmgr -d repmgr -f /etc/repmgr/12/repmgr.conf standby register --force

Diğer slave sunucularda da konfigürasyonda ip değiştirerek aynı işlemleri yapıyoruz ve master sunucuya register ediyoruz.

Artık master sunucuya geçip, cluster’ı tekrar kontrol edebiliriz;

Tüm master ve slave sunucularımız, cluster yapımıza register olmuş durumda. id’si 5 olan slave sunucumuz primary gibi çalışıyor. Cluster yapımızı test edelim. Bunun için bir veritabanı oluşturmamız yetecektir. Master sunucuda su postgres diyerek postgres kullanıcısına geçelim ve postgresql’e bağlanalım;

psql

Aynı işlemi slave sunucularda da gerçekleştirelim ve \l diyerek veritabanlarını listeleyelim.

Şimdi master sunucu üzerinde bir veritabanı oluşturalım.

create user test_db;
create database test_db with owner test_db;

Master sunucuda \l yaparak veritabanlarını listeleyelim;

Master sunucuda test_db veritabanının oluştuğunu ve sahibinin test_db kullanıcısı olduğunu gördük. Şimdi slave sunucular üzerinde veritabanlarını listeleyelim.

Görüldüğü üzere master sunucuda oluşturduğumuz veritabanı anında diğer sunuculara da yazıldı. Şu anda cluster yapımızın doğru şekilde çalıştığını görmüş oldu. Sıra HaProxy ile load balance işlemine geldi.

HaProxy Kurulumu

Öncelikle yum update -y sistemimizi güncelleyelim. Sonra HaProxy için epel reposunu kuralım;

yum install epel-release -y

Sonra HaProxy kuralım;

yum install haproxy -y

Haproxy’nin default konfig dosyasını yedekleyelim;

cd /etc/haproxy/
mv haproxy.cfg  haproxy.cfg_yedek

Haproxy konfigürasyon dosyamızı aşağıdaki şekilde girelim;

nano /etc/haproxy/haproxy.cfg

global
        log 127.0.0.1 local0 notice
        maxconn 491520
        user haproxy
        group haproxy
        daemon

defaults
        log global
        mode http
        option httplog
        option dontlognull
        retries 3
        option redispatch
        timeout connect 60000
        timeout client 60000
        timeout server 60000
        fullconn 491520
        stats enable
        stats uri /stats
        stats realm Strictly\ Private
        stats auth haproxy:sifremiz

frontend http-in
        bind *:80
        bind *:443


listen  haproxy_postgresql_rw
        bind *:5432
        mode tcp
        timeout client  10800s
timeout server  10800s
        balance leastconn
        option tcp-check
        server master  192.168.100.11:5432 check


listen  haproxy_postgresql_ro
        bind *:5532
        mode tcp
        timeout client  10800s
        timeout server  10800s
        balance leastconn
        option tcp-check
        option allbackups
        server slave  192.168.100.12:5432 check
        server slave1  192.168.100.13:5432 check
        server slave2  192.168.100.14:5432 check
        server slave3 192.168.100.15:5432 check

HaProxy servisimizi restart edelim;

service haproxy restart

Eğer sunucularımızda bir problem yoksa, haproxy’nin stats sayfasında görebilmemiz gerekiyor. Stats sayfasına 192.168.100.10/stats diye gireceğiz. Yukarıda stats auth haproxy:sifremiz kısmında, kullanıcı adı ve şifre belirledik.

Master sunucumuzda ayakta. Slave sunuculara da bakalım;

Slave sunucularımızın da ayakta olduğu gözüküyor. Buradaki mantığımız şu şekilde;

Master sunucu rewrite yani yazılabilir sunucu. Bu sunucuya insert, update, delete gibi sorguları göndereceğiz. Slave sunucularımız ise read only yani sadece okuma yapacak. Bu sunuculara da sadece select sorgularını göndereceğiz. Buradaki round robin mantığını haproyx bizim için yapacak. Sql bağlantısı kurarken haproxy’e bağlanacağız ve yazma işlemi varsa 5432 portundan, okuma işlemi varsa 5532 portundan bağlanacağız. Yani sorguları connection string ile ayırmamız gerekiyor. Önce haproxy portları dinliyor mu ona bakalım;

netstat -ntlp | grep 5432
netstat -ntlp | grep 5532

Şimdi bir select sorgusu ile hangi sunucudan cevap verecek ona bakalım;

psql -h 192.168.100.10 -U postgres -d test_db -c "select inet_server_addr()"

Postgresql şifresini girdiğimizde bize sorguya cevap verdiği sunucuyu söyleyecek;

192.168.100.11

5432 portunu sadece master sunucuya ayırdığımızdan her master sunucudan cevap gelecektir. Sorguyu port ile değiştirelim;

psql -h 192.168.100.10 -p 5532 -U postgres -d test_db -c "select inet_server_addr()"

Artık diğer tüm slave sunuculardan cevap almamız gerekiyor. Ben tüm sunuculardan cevap aldım.

Notlar : firewalld servisini kontrol edilmeli ve sadece belirli portlar açılmalı. netstat ve telnet paketleri kurulup, dışarıdan ve direkt sunucudan portlar kontrol edilmeli. pgbench ile yük testi yapılmalı.

postgres veritabanı kullanıcısının şifresini değiştirmek için;

psql
\password postgres