Open in app

Sign in

Write

Sign in

Postgresql Cluster Kurulumu( repmgr + haproxy )

Alparslan Ozturk
6 min readFeb 14, 2022

Bu senaryoda db4 ve db5 sunucularımızı Debian 11 kurduktan sonra yapılması gereken ayarlar anlatılacaktır. Ayrıca apt ile Postgresql 14 ve repmgr kurulmuştur.

cat /etc/hosts
1.1.1.24 db4.ornek.com db4
1.1.1.25 db5.ornek.com db5apt install -y postgresql-14-repmgr postgresql-14#db4 üzerinde
sudo pg_createcluster 14 main -d /pg_data/14/main -- --data-checksum
sudo systemctl daemon-reload 
sudo systemctl status postgresql@14-main.service 
sudo systemctl enable --now postgresql@14-main.service
#db5 üzerinde
sudo pg_createcluster 14 main -d /pg_data/14/main -- --data-checksum...start edilmeyecek...

2. Postgresql repmgr ayar dosyasi eklenmesi:

cat > /etc/postgresql/14/main/conf.d/postgresql.replication.conf <<EOF
listen_addresses = '*'
wal_level = 'logical'
archive_mode = 'on'
archive_command = '/bin/true'
shared_preload_libraries = 'repmgr'
EOF

3. repmgr kullanicisi, veritabani ve search path ayalari:

createuser  --superuser repmgr 
createdb repmgr --owner=repmgr 
psql -c “ALTER USER repmgr SET search_path TO repmgr, public;”

4. pg_hba.conf ayarlari: superuser all ile belirtilse bile replication ile ayni degildir, belirtilmelidir. Yukarıdan aşağıya doğru pg_hba.conf daki kurallar işlediği için en üste koyamanızda fayda var. ( örn: 1.1.1.0/24 yerel ağ)

örnek pg_hba.conf dosyasi
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 1.1.1.0/24 trustlocal repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 1.1.1.0/24 trust
bu yada sudo systemctl restart postgresql@14-main.service yapmayi unutmayin.

5. primary sunucuda ( db4);

cat > /etc/repmgr.conf <<EOF
node_id=1
node_name='db4'
conninfo='host=db4 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/pg_data/14/main'
pg_bindir='/usr/lib/postgresql/14/bin'
repmgr_bindir='/usr/lib/postgresql/14/bin'
EOF

5. standby sunucuda( db5);

cat > /etc/repmgr.conf <<EOF
node_id=2
node_name='db5'
conninfo='host=db5 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/pg_data/14/main'
pg_bindir = '/usr/lib/postgresql/14/bin'
repmgr_bindir='/usr/lib/postgresql/14/bin'
EOF

6. Bağlantıların test edilmesi;

psql ‘host=db4 user=repmgr dbname=repmgr connect_timeout=2’
psql -U repmgr -h db4 -d repmgr

7. Register primary server;

$ repmgr -f /etc/repmgr.conf primary register
INFO: connecting to primary database…
NOTICE: attempting to install extension “repmgr”
NOTICE: “repmgr” extension successfully installed
NOTICE: primary node record (ID: 1) registered$ sudo systemctl  status repmgrd.service
● repmgrd.service - LSB: Start/stop repmgrd
     Loaded: loaded (/etc/init.d/repmgrd; generated)...Feb 14 14:38:33 db4 systemd[1]: Starting LSB: Start/stop repmgrd...
Feb 14 14:38:34 db4 systemd[1]: Started LSB: Start/stop repmgrd.
repmgr cluster show
psql -U repmgr

8. Standby clone;

$ repmgr -h node1 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone — dry-run

Bu işlemden sonra pg_hba.conf yada posgresql.auto.conf vb. dosyaları manuel kopyalamak ve öyle start etmek isteyebilirsiniz. copy ve force komutlarını configurasyon dosyalarını taşımak için ekledim. böylece systemd ilede kontrol edilebilecektir.

repmgr -h db4 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone — copy-external-config-files — force
tüm dosyalar clone komuduyla gelmiş olduğu görünüyor. bu işlem basebackup ile yapılmış ve recovery.conf dosyası otomatik oluşturulmuş olması gerekir. şimdi “standby registr” yapılacak.

Debian Sunucuda tüm config dosyalarının da koplayndığı görünüyor.

otomatik recovery.conf ile açılışını buradan görebilirsiniz.

9. standby registry işlemi: “repmgr standby register”

10. Cluster HAproxy kurulumu ile kullanmak.

repmgr cluster show
apt install -y xinetd

a) xinetd servis tanımı:

postgres@db5:~$ cat /etc/xinetd.d/pgsqlchk
service pgsqlchk
{
        flags           = REUSE
        socket_type     = stream
        port            = 18080
        wait            = no
        user            = postgres
        server          = /opt/pgsqlchk
        log_on_failure  += USERID
        disable         = no
        only_from       = 0.0.0.0/0
        per_source      = UNLIMITED
}

b) bash script HTTP server

postgres@db5:~$ cat /opt/pgsqlchk
#!/bin/bashVALUE=$(psql -At -c "select pg_is_in_recovery()" 2>/dev/null)
if [ "$VALUE" = "f" ]
then
    echo -en "HTTP/1.1 200 OK\r\n"
    echo -en "Content-Type: text/plain\r\n"
    echo -en "Connection: close\r\n"
    echo -en "Content-Length: 9\r\n"
    echo -en "\r\n"
    echo -en "Primary\r\n"
    echo -en "\r\n"
    sleep 1elif [ "$VALUE" = "t" ]
then
    echo -en "HTTP/1.1 201 OK\r\n"
    echo -en "Content-Type: text/plain\r\n"
    echo -en "Connection: close\r\n"
    echo -en "Content-Length: 9\r\n"
    echo -en "\r\n"
    echo -en "Standby\r\n"
    echo -en "\r\n"
    sleep 1else
    echo -en "HTTP/1.1 503 Service Unavailable\r\n"
    echo -en "Content-Type: text/plain\r\n"
    echo -en "Connection: close\r\n"
    echo -en "Content-Length: 9\r\n"
    echo -en "\r\n"
    echo -en "DBDown\r\n"
    echo -en "\r\n"
    sleep 1fi
exit 0

c) Primary sunucular 5432 portu üzerinden dinleyecek. Standby sunucular 5433 portu üzerinden dinleyeceklerdir.

listen primary
 bind *:5432
 option httpchk GET / HTTP/1.1
 http-check expect status 200
 server db4 1.1.1.24:5432 check port 18080
 server db5 1.1.1.25:5432 check port 18080listen standby
 bind *:5433
 option httpchk GET / HTTP/1.1
 http-check expect status 201
 server db4 1.1.1.24:5432 check port 18080
 server db5 1.1.1.25:5432 check port 18080

Bazi cluster komutları;

$ repmgr cluster matrix
INFO: connecting to database
 Name | ID | 1 | 2
------+----+---+---
 db4  | 1  | * | *
 db5  | 2  | * | *$ repmgr cluster event
 Node ID | Name | Event            | OK | Timestamp           | Details
---------+------+------------------+----+---------------------+-----------------------------------------------------------------------------
 2       | db5  | standby_register | t  | 2022-02-14 16:03:32 | standby registration succeeded; upstream node ID is 1
 2       | db5  | standby_clone    | t  | 2022-02-14 15:54:50 | cloned from host "db4", port 5432; backup method: pg_basebackup; --force: Y
 1       | db4  | primary_register | t  | 2022-02-14 15:50:31 |
 1       | db4  | cluster_created  | t  | 2022-02-14 15:50:31 |
repmgr service pause
curl -i -v  http://db4:18080/
*   Trying 1.1.1.24...
* TCP_NODELAY set
* Connected to db4 (1.1.1.24) port 18080 (#0)
> GET / HTTP/1.1
> Host: db4:18080
> User-Agent: curl/7.61.1
> Accept: */*
>
< HTTP/1.1 200 OK
HTTP/1.1 200 OK
< Content-Type: text/plain
Content-Type: text/plain
< Connection: close
Connection: close
< Content-Length: 9
Content-Length: 9<
* Recv failure: Connection reset by peer
* Closing connection 0
curl: (56) Recv failure: Connection reset by peer

SWITCHOVER

Standby (db5 ) üzerinde aşağıdaki komudu veriyoruz. ve Haproxy den izliyoruz.

$ repmgr standby switchover
NOTICE: executing switchover on node "db4" (ID: 1)
NOTICE: attempting to pause repmgrd on 2 nodes
NOTICE: local node "db4" (ID: 1) will be promoted to primary; current primary "db5" (ID: 2) will be demoted to standby
NOTICE: stopping current primary node "db5" (ID: 2)
NOTICE: issuing CHECKPOINT on node "db5" (ID: 2)
DETAIL: executing server command "sudo systemctl stop postgresql@14-main.service"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/10000028
NOTICE: promoting standby to primary
DETAIL: promoting server "db4" (ID: 1) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "db4" (ID: 1) was successfully promoted to primary
NOTICE: node "db4" (ID: 1) promoted to primary, node "db5" (ID: 2) demoted to standby
NOTICE: switchover was successful
DETAIL: node "db4" is now primary and node "db5" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully
repmgr standby switchover

Yukarıdaki gibi düzgün çalışması için debian/ubuntuda; /etc/repmgr.conf dosyanızdaki serices*command aşağıdaki gibi olmalıdır.

$ cat /etc/repmgr.conf
node_id=1
node_name='db4'
conninfo='host=db4 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/pg_data/14/main'
pg_bindir='/usr/lib/postgresql/14/bin'
repmgr_bindir='/usr/lib/postgresql/14/bin'service_start_command   = 'sudo systemctl start postgresql@14-main.service'
service_stop_command    = 'sudo systemctl stop postgresql@14-main.service'
service_restart_command = 'suod systemctl restart postgresql@14-main.service'

Son

--

--

Alparslan Ozturk

Written by Alparslan Ozturk

19 Followers

System Admin

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams