datenbank:ha-postgresql-cluster
Dies ist eine alte Version des Dokuments!
Inhaltsverzeichnis
HA-PostgreSQL-Cluster
Systembeschreibung
Es soll ein hochverfügbarer PostgreSQL‑Cluster mit Patroni, EtcE, HAProxy und KeepaliveD auf drei Systemen installiert werden.
Wichtige Unterschiede zwischen diesem HA-PostgreSQL-Cluster mit 3 Systemen, gegenüber einem HA-PostgreSQL-Cluster mit 5 Systemen sind, dass hier alle Pakete auf allen 3 Systemen installiert werden müssen. Und weil PostgreSQL und HAProxy auf dem selben Host laufen, muß der Datenbank-Zugriff über den Cluster-Zugang (HAProxy) auf einen anderen Port laufen. In diesem Fall laufen die PostgreSQL-Instanzen aus dem Standard-Port (5432) und der HAProxy jeweils auf einen neuen Port (5433).
In einem HA-PostgreSQL-Cluster mit 5 Systemen laufen EtcE, HAProxy und KeepaliveD auf 2 separate Systeme und dann können PostgreSQL und HAProxy auf dem gleichen Port laufen.
Installation
> locale-gen de_DE.UTF-8 > apt update > apt install vim screen mc csync2 links python3 python3-psycopg2 jq curl wget net-tools etcd-server etcd-client haproxy keepalived postgresql postgresql-contrib patroni > echo "export EDITOR=/usr/bin/vim" > ~/.bash_profile
Csync2
- /etc/csync2.cfg
group Loadbalancer #group name, we can have multiple groups { host pg-knoten-01; #master server host pg-knoten-02; #slave server host pg-knoten-03; #slave server key /etc/csync2_ssl.key; include /etc/csync2.cfg; #include /etc/hosts.allow; #include /root/.ssh; include /root/bin/; include /etc/haproxy/haproxy.cfg; backup-directory /var/backups/csync2; backup-generations 10; auto none; #no automatic sync }
EtcD
- Knoten 1 – /etc/default/etcd
## etcd(1) daemon options ## See "/usr/share/doc/etcd-server/op-guide/configuration.md.gz" ## for available options. ## ## Use environment to override, for example: ETCD_NAME=default ETCD_NAME="etcd1" ETCD_DATA_DIR="/var/lib/etcd" # ### Peer-Kommunikation # ETCD_LISTEN_PEER_URLS -> nur IP, keine Hostnamen! ETCD_LISTEN_PEER_URLS="http://10.145.43.31:2380" ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.145.43.31:2380" # ### Client-Kommunikation # ETCD_LISTEN_CLIENT_URLS -> nur IP, keine Hostnamen! ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379" ETCD_ADVERTISE_CLIENT_URLS="http://10.145.43.31:2379" # ### Cluster-Definition ETCD_INITIAL_CLUSTER="etcd1=http://10.145.43.31:2380,etcd2=http://10.145.43.32:2380,etcd3=http://10.145.43.33:2380" # Bootstrap - initialer Start, um einen neuen Cluster mit neuer ID zu generierenetcdctl endpoint health #ETCD_INITIAL_CLUSTER_STATE="new" # Start, wenn Cluster bereits existiert ETCD_INITIAL_CLUSTER_STATE="existing" # ETCD_INITIAL_CLUSTER_TOKEN="pg-cluster-0"
- Knoten 2 – /etc/default/etcd
## etcd(1) daemon options ## See "/usr/share/doc/etcd-server/op-guide/configuration.md.gz" ## for available options. ## ## Use environment to override, for example: ETCD_NAME=default ETCD_NAME="etcd2" ETCD_DATA_DIR="/var/lib/etcd" # ### Peer-Kommunikation # ETCD_LISTEN_PEER_URLS -> nur IP, keine Hostnamen! ETCD_LISTEN_PEER_URLS="http://10.145.43.32:2380" ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.145.43.32:2380" # ### Client-Kommunikation # ETCD_LISTEN_CLIENT_URLS -> nur IP, keine Hostnamen! ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379" ETCD_ADVERTISE_CLIENT_URLS="http://10.145.43.32:2379" # ### Cluster-Definition ETCD_INITIAL_CLUSTER="etcd1=http://10.145.43.31:2380,etcd2=http://10.145.43.32:2380,etcd3=http://10.145.43.33:2380" # Bootstrap - initialer Start, um einen neuen Cluster mit neuer ID zu generieren ETCD_INITIAL_CLUSTER_STATE="new" # Start, wenn Cluster bereits besteht #ETCD_INITIAL_CLUSTER_STATE="existing" # ETCD_INITIAL_CLUSTER_TOKEN="pg-cluster-0"
- Knoten 3 – /etc/default/etcd
## etcd(1) daemon options ## See "/usr/share/doc/etcd-server/op-guide/configuration.md.gz" ## for available options. ## ## Use environment to override, for example: ETCD_NAME=default ETCD_NAME="etcd3" ETCD_DATA_DIR="/var/lib/etcd" # ### Peer-Kommunikation # ETCD_LISTEN_PEER_URLS -> nur IP, keine Hostnamen! ETCD_LISTEN_PEER_URLS="http://10.145.43.33:2380" ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.145.43.33:2380" # ### Client-Kommunikation # ETCD_LISTEN_CLIENT_URLS -> nur IP, keine Hostnamen! ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379" ETCD_ADVERTISE_CLIENT_URLS="http://10.145.43.33:2379" # ### Cluster-Definition ETCD_INITIAL_CLUSTER="etcd1=http://10.145.43.31:2380,etcd2=http://10.145.43.32:2380,etcd3=http://10.145.43.33:2380" # Bootstrap - initialer Start, um einen neuen Cluster mit neuer ID zu generieren ETCD_INITIAL_CLUSTER_STATE="new" # Start, wenn Cluster bereits besteht #ETCD_INITIAL_CLUSTER_STATE="existing" # ETCD_INITIAL_CLUSTER_TOKEN="pg-cluster-0"
- Starten
systemctl restart etcd systemctl enable etcd
- Cluster testen
> etcdctl --endpoints="http://pg-knoten-01:2379,http://pg-knoten-02:2379,http://pg-knoten-03:2379" endpoint health http://pg-knoten-01:2379 is healthy: successfully committed proposal: took = 3.710884ms http://pg-knoten-02:2379 is healthy: successfully committed proposal: took = 5.53501ms http://pg-knoten-03:2379 is healthy: successfully committed proposal: took = 7.426682ms
Hinweis
Solange der Cluster unhealthy ist, sind Verwaltungsoperationen wie etcdctl member add absichtlich verboten.
> etcdctl --endpoints="http://10.145.43.31:2379,http://10.145.43.32:2379,http://10.145.43.33:2379" endpoint health {"level":"warn","ts":"2026-05-08T13:30:39.78628+0200","caller":"clientv3/retry_interceptor.go:62","msg":"retrying of unary invoker failed","target":"etcd-endpoints://0xc000279340/10.145.43.31:2379","attempt":0,"error":"rpc error: code = DeadlineExceeded desc = context deadline exceeded"} http://10.145.43.32:2379 is healthy: successfully committed proposal: took = 6.664014ms http://10.145.43.33:2379 is healthy: successfully committed proposal: took = 7.184567ms http://10.145.43.31:2379 is unhealthy: failed to commit proposal: context deadline exceeded Error: unhealthy cluster > etcdctl member add etcd1 --peer-urls=http://10.145.43.31:2380 {"level":"warn","ts":"2026-05-08T13:33:25.070576+0200","caller":"clientv3/retry_interceptor.go:62","msg":"retrying of unary invoker failed","target":"etcd-endpoints://0xc0003ae700/127.0.0.1:2379","attempt":0,"error":"rpc error: code = Unavailable desc = etcdserver: unhealthy cluster"} Error: etcdserver: unhealthy cluster
- Status
> service etcd status > journalctl -u etcd --no-pager -n 200 > etcdctl endpoint health 127.0.0.1:2379 is healthy: successfully committed proposal: took = 4.611357ms > etcdctl member list 4803cb28e6fab7cd, started, etcd1, http://10.145.43.31:2380, , false 48ca69f515dfab05, started, etcd3, http://10.145.43.33:2380, http://10.145.43.33:2379, false a7cd8d4ed3d24ea8, started, etcd2, http://10.145.43.32:2380, http://10.145.43.32:2379, false
- ETCD-Cluster reparieren
> etcdctl member remove 4803cb28e6fab7cd Member 4803cb28e6fab7cd removed from cluster e2a96af5103eb757 > etcdctl member list 48ca69f515dfab05, started, etcd3, http://10.145.43.33:2380, http://10.145.43.33:2379, false a7cd8d4ed3d24ea8, started, etcd2, http://10.145.43.32:2380, http://10.145.43.32:2379, false > etcdctl member add etcd1 --peer-urls=http://10.145.43.31:2380 Member 576d85c36663da50 added to cluster e2a96af5103eb757 > etcdctl member list 48ca69f515dfab05, started, etcd3, http://10.145.43.33:2380, http://10.145.43.33:2379, false 576d85c36663da50, unstarted, , http://10.145.43.31:2380, , false a7cd8d4ed3d24ea8, started, etcd2, http://10.145.43.32:2380, http://10.145.43.32:2379, false
- etcd1 restarten
> service etcd stop ; rm -fr /var/lib/etcd/* ; vim /etc/default/etcd ; systemctl daemon-reload ; service etcd restart > etcdctl member list 48ca69f515dfab05, started, etcd3, http://10.145.43.33:2380, http://10.145.43.33:2379, false 576d85c36663da50, started, etcd1, http://10.145.43.31:2380, http://10.145.43.31:2379, false a7cd8d4ed3d24ea8, started, etcd2, http://10.145.43.32:2380, http://10.145.43.32:2379, false
HAProxy
- /etc/haproxy/haproxy.cfg
global log /dev/log local0 maxconn 5000 stats socket /run/haproxy/admin.sock mode 660 level admin defaults mode tcp timeout connect 5s timeout client 30s timeout server 30s timeout check 5s log global option tcplog retries 3 listen stats bind 0.0.0.0:8404 mode http stats enable stats uri /stats stats refresh 10s stats auth admin:admin # ============================ # SCHREIBZUGRIFFE (nur Leader/Primary) # ============================ frontend postgres_write #bind *:5432 bind *:5433 default_backend pg_leader backend pg_leader mode tcp option httpchk http-check send meth GET uri /primary ver HTTP/1.1 hdr Host localhost http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg1 pg-knoten-01:5432 check port 8008 maxconn 100 server pg2 pg-knoten-02:5432 check port 8008 maxconn 100 server pg3 pg-knoten-03:5432 check port 8008 maxconn 100 # ============================ # LESEZUGRIFFE (alle, lastverteilung) # ============================ # [~]# curl -s "http://admin:admin@localhost:8404/stats;csv" | grep -E 'pg_leader|pg_replica' | awk -F',' '{print $2, $18, $56}' # pg1 UP -1 # pg2 DOWN -1 # pg3 DOWN -1 # BACKEND UP -1 # pg1 UP -1 # pg2 UP -1 # pg3 UP -1 # BACKEND UP -1 frontend postgres_read bind *:6543 default_backend pg_replica backend pg_replica mode tcp option httpchk balance leastconn # Verteilt nach geringster Last http-check send meth GET uri /liveness ver HTTP/1.1 hdr Host localhost http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg1 pg-knoten-01:5432 check port 8008 maxconn 100 server pg2 pg-knoten-02:5432 check port 8008 maxconn 100 server pg3 pg-knoten-03:5432 check port 8008 maxconn 100
<hidden /etc/haproxy/haproxy.cfg_Version_1>
- /etc/haproxy/haproxy.cfg_Version_1
global log /dev/log local0 maxconn 5000 stats socket /run/haproxy/admin.sock mode 660 level admin defaults mode tcp timeout connect 5s timeout client 30s timeout server 30s listen stats bind 0.0.0.0:8404 #bind 127.0.0.1:8404 mode http stats enable stats uri /stats stats refresh 10s stats auth admin:admin frontend postgresql # Weil HAProxy und PostgreSQL auf dem selben host laufen, # muß der HAProxy einen anderen Port verwenden! #bind *:5432 bind *:5433 default_backend pg_patroni_knoten # HAProxy überprüft nicht den PostgreSQL-Port, # sondern den Patroni-Port! # Es genügt also nicht, die DB zu starten (was man auch nicht tun sollte), # es muß Patroni laufen, der startet dann PostgreSQL. # [~]# curl -s "http://admin:admin@localhost:8404/stats;csv" | grep "pg_patroni_knoten" | awk -F',' '{print $2, $18, $56}' # pg1 UP 909 # pg2 UP 899 # pg3 UP 1211 # BACKEND UP 902 backend pg_patroni_knoten http-check send meth GET /liveness http-check expect status 200 server pg1 pg-knoten-01:5432 check port 8008 server pg2 pg-knoten-02:5432 check port 8008 server pg3 pg-knoten-03:5432 check port 8008
</hidden>
<hidden /etc/haproxy/haproxy.cfg_Version_2>
- /etc/haproxy/haproxy.cfg_Version_2
global log /dev/log local0 maxconn 5000 stats socket /run/haproxy/admin.sock mode 660 level admin defaults mode tcp timeout connect 5s timeout client 30s timeout server 30s timeout check 5s log global option tcplog retries 3 listen stats bind 0.0.0.0:8404 mode http stats enable stats uri /stats stats refresh 10s stats auth admin:admin # ============================ # SCHREIBZUGRIFFE (nur Leader/Primary) # ============================ frontend postgres_write #bind *:5432 bind *:5433 default_backend pg_leader backend pg_leader mode tcp option httpchk http-check send meth GET uri /primary ver HTTP/1.1 hdr Host localhost http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg1 pg-knoten-01:5432 check port 8008 maxconn 100 server pg2 pg-knoten-02:5432 check port 8008 maxconn 100 server pg3 pg-knoten-03:5432 check port 8008 maxconn 100 # ============================ # LESEZUGRIFFE (alle, lastverteilung) # ============================ # [~]# curl -s "http://admin:admin@localhost:8404/stats;csv" | grep -E 'pg_leader|pg_replica' | awk -F',' '{print $2, $18, $56}' # pg1 UP -1 # pg2 DOWN -1 # pg3 DOWN -1 # BACKEND UP -1 # pg1 UP -1 # pg2 UP -1 # pg3 UP -1 # BACKEND UP -1 frontend postgres_read bind *:6543 default_backend pg_replica backend pg_replica mode tcp option httpchk balance leastconn # Verteilt nach geringster Last http-check send meth GET uri /liveness ver HTTP/1.1 hdr Host localhost http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg1 pg-knoten-01:5432 check port 8008 maxconn 100 server pg2 pg-knoten-02:5432 check port 8008 maxconn 100 server pg3 pg-knoten-03:5432 check port 8008 maxconn 100
</hidden>
<hidden /etc/haproxy/haproxy.cfg_Version_3>
- /etc/haproxy/haproxy.cfg_Version_3
global log /dev/log local0 maxconn 5000 stats socket /run/haproxy/admin.sock mode 660 level admin defaults mode tcp timeout connect 5s timeout client 30s timeout server 30s timeout check 5s log global option tcplog retries 3 listen stats bind 0.0.0.0:8404 mode http stats enable stats uri /stats stats refresh 10s stats auth admin:admin # ============================ # SCHREIBZUGRIFFE (nur Leader/Primary) # ============================ frontend postgres_write #bind *:5432 bind *:5433 default_backend pg_leader backend pg_leader mode tcp option httpchk http-check send meth GET uri /primary ver HTTP/1.1 hdr Host localhost http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg1 pg-knoten-01:5432 check port 8008 maxconn 100 server pg2 pg-knoten-02:5432 check port 8008 maxconn 100 server pg3 pg-knoten-03:5432 check port 8008 maxconn 100 # ============================ # LESEZUGRIFFE (nur Replicas, lastverteilung) # ============================ # [~]# curl -s "http://admin:admin@localhost:8404/stats;csv" | grep -E 'pg_leader|pg_replica' | awk -F',' '{print $2, $18, $56}' # pg1 UP -1 # pg2 DOWN -1 # pg3 DOWN -1 # BACKEND UP -1 # pg1 DOWN -1 # pg2 UP -1 # pg3 UP -1 # BACKEND UP -1 frontend postgres_read bind *:6543 default_backend pg_replica backend pg_replica mode tcp option httpchk balance leastconn # Verteilt nach geringster Last http-check send meth GET uri /primary ver HTTP/1.1 hdr Host localhost http-check expect status 503 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg1 pg-knoten-01:5432 check port 8008 maxconn 100 server pg2 pg-knoten-02:5432 check port 8008 maxconn 100 server pg3 pg-knoten-03:5432 check port 8008 maxconn 100
</hidden>
> service haproxy restart ... May 08 17:47:16 pg-knoten-03 systemd[1]: Starting haproxy.service - HAProxy Load Balancer... May 08 17:47:16 pg-knoten-03 haproxy[1219172]: [NOTICE] (1219172) : New worker (1219177) forked May 08 17:47:16 pg-knoten-03 haproxy[1219172]: [NOTICE] (1219172) : Loading success. May 08 17:47:16 pg-knoten-03 systemd[1]: Started haproxy.service - HAProxy Load Balancer. May 08 17:47:16 pg-knoten-03 haproxy[1219177]: [WARNING] (1219177) : Server pg_patroni_knoten/pg1 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 2 ac> May 08 17:47:17 pg-knoten-03 haproxy[1219177]: [WARNING] (1219177) : Server pg_patroni_knoten/pg2 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 ac> > ss -antp | grep -F LISTEN | grep -F haproxy LISTEN 0 4096 0.0.0.0:5433 0.0.0.0:* users:(("haproxy",pid=995927,fd=9))
- Patroni REST API (für "option httpchk GET")
### https://patroni.readthedocs.io/en/latest/rest_api.html > curl -i http://localhost:8008/readiness HTTP/1.0 200 OK > curl -i http://localhost:8008/liveness HTTP/1.0 200 OK > curl -i http://localhost:8008/patroni HTTP/1.0 200 OK Server: BaseHTTP/0.6 Python/3.12.3 Date: Fri, 08 May 2026 15:36:46 GMT Content-Type: application/json {"state": "unknown", "role": "replica", "cluster_unlocked": true, "dcs_last_seen": 1778254602, "database_system_identifier": "7637152973093863500", "patroni": {"version": "3.2.2", "scope": "pgcluster", "name": "pg-knoten-01"}} # Gibt nur "200" zurück, wenn der abgefragte Host "Leader" ist! > curl -i http://localhost:8008/leader HTTP/1.0 200 OK Server: BaseHTTP/0.6 Python/3.12.3 Date: Sun, 10 May 2026 11:59:18 GMT Content-Type: application/json {"state": "running", "postmaster_start_time": "2026-05-08 21:07:24.936732+02:00", "role": "master", "server_version": 160013, "xlog": {"location": 22277144}, "timeline": 2, "dcs_last_seen": 1778414356, "database_system_identifier": "7637598528541987072", "patroni": {"version": "3.2.2", "scope": "pgcluster", "name": "pg-knoten-01"}} > curl -i http://localhost:8008/metrics HTTP/1.0 200 OK Server: BaseHTTP/0.6 Python/3.12.3 Date: Fri, 08 May 2026 15:37:38 GMT Content-Type: text/plain # HELP patroni_version Patroni semver without periods. # TYPE patroni_version gauge patroni_version{scope="pgcluster",name="pg-knoten-01"} 030202 ... > curl -i http://localhost:8008/cluster HTTP/1.0 200 OK Server: BaseHTTP/0.6 Python/3.12.3 Date: Fri, 08 May 2026 15:40:49 GMT Content-Type: application/json {"members": [{"name": "pg-knoten-01", "role": "replica", "state": "running", "api_url": "http://pg-knoten-03:8008/patroni", "host": "pg-knoten-03", "port": 5432, "lag": "unknown"}], "scope": "pgcluster"} > curl -i http://localhost:8008/history HTTP/1.0 200 OK Server: BaseHTTP/0.6 Python/3.12.3 Date: Fri, 08 May 2026 15:41:34 GMT Content-Type: application/json []
- HAProxy-Stats
> links -ssl.certificates 0 -dump http://admin:admin@localhost:8404/stats
KeepaliveD
- Knoten 1 – /etc/keepalived/keepalived.conf
global_defs { script_user root enable_script_security no_email_faults vrrp_no_swap } vrrp_script chk_dienst_vip { script "/root/bin/check_haproxy.sh" interval 2 weight 2 fall 2 } vrrp_instance VIP { interface bond2 state BACKUP virtual_router_id 203 priority 100 advert_int 1 track_script { chk_dienst_vip } authentication { auth_type PASS auth_pass RaBoo9as } virtual_ipaddress { 10.145.43.30/24 dev vlan203 } unicast_src_ip 10.145.43.31 unicast_peer { #10.145.43.31 10.145.43.32 10.145.43.33 } }
- Knoten 2 – /etc/keepalived/keepalived.conf
global_defs { script_user root enable_script_security no_email_faults vrrp_no_swap } vrrp_script chk_dienst_vip { script "/root/bin/check_haproxy.sh" interval 2 weight 2 fall 2 } vrrp_instance VIP { interface bond2 state BACKUP virtual_router_id 203 priority 100 advert_int 1 track_script { chk_dienst_vip } authentication { auth_type PASS auth_pass RaBoo9as } virtual_ipaddress { 10.145.43.30/24 dev vlan203 } unicast_src_ip 10.145.43.32 unicast_peer { 10.145.43.31 #10.145.43.32 10.145.43.33 } }
- Knoten 3 – /etc/keepalived/keepalived.conf
global_defs { script_user root enable_script_security no_email_faults vrrp_no_swap } vrrp_script chk_dienst_vip { script "/root/bin/check_haproxy.sh" interval 2 weight 2 fall 2 } vrrp_instance VIP { interface bond2 state BACKUP virtual_router_id 203 priority 100 advert_int 1 track_script { chk_dienst_vip } authentication { auth_type PASS auth_pass RaBoo9as } virtual_ipaddress { 10.145.43.30/24 dev vlan203 } unicast_src_ip 10.145.43.33 unicast_peer { 10.145.43.31 10.145.43.32 #10.145.43.33 } }
- /root/bin/check_pg.sh
#!/bin/bash #==============================================================================# # Dieses Skript prüft den Zugriff auf die einzelnen DB-Knoten. # Es wird erst ein Fehler ausgegeben, wenn kein DB-Knoten mehr erreichbar ist. #==============================================================================# ### Wenn PostgreSQL+Patroni nicht mit ETCD, HAProxy und KeepaliveD auf dem selben Host liegen #AUSGABE="$(for DBIP in $(awk '/[[:space:]][0-9]+[.][0-9]+[.][0-9]+[.][0-9]+[:]/{gsub("[:]"," "); print $3}' /etc/haproxy/haproxy.cfg) #do # echo "\l" | psql -U postgres -h ${DBIP} >/dev/null 2>/dev/null && echo "${DBIP}" #done)" ### Nur wenn PostgreSQL+Patroni, ETCD, HAProxy und KeepaliveD auf dem selben Host liegen AUSGABE="$(sudo -u postgres psql -c '\l' >/dev/null 2>/dev/null && hostname -s)" # Test #echo "AUSGABE='${AUSGABE}'" if [ x = "x${AUSGABE}" ] ; then echo "CRIT: kein DB-Knoten ist erreichbar" exit 2 else echo "OK: ${AUSGABE}" | tr -s '\n' ' '; echo exit 0 fi
- /root/bin/check_haproxy.sh
#!/bin/bash #==============================================================================# # Dieses Skript prüft den Zugriff auf die einzelnen DB-Knoten. # Es wird erst ein Fehler ausgegeben, wenn kein DB-Knoten mehr erreichbar ist. #==============================================================================# HA_PORT="$(awk '/^[[:space:]]*bind[[:space:]]/{gsub("[:]"," "); print $3}' /etc/haproxy/haproxy.cfg)" SS_AUSGABE="$(ss -antp | grep -F LISTEN | grep -F "haproxy" | grep -E ":${HA_PORT}[[:space:]]")" HA_AUSGABE="$(links -ssl.certificates 0 -dump http://admin:admin@localhost:8404/stats | grep -E 'pg1|pg2|pg3' | wc -l)" if [ x = "x${SS_AUSGABE}" ] ; then echo "CRIT: Port nicht offen" exit 2 else if [ 3 -eq "${HA_AUSGABE}" ] ; then echo "OK" exit 0 else echo "CRIT: haproxy antwortet nicht richtig" exit 2 fi fi
- Starten
systemctl enable keepalived systemctl restart keepalived
- VIP testen
> ping 10.145.43.30
PostgreSQL
- PostgreSQL installieren
> apt install postgresql postgresql-contrib
- STOP
> service postgresql stop
- Netzwerkzugriffe erlauben
> echo "listen_addresses = '*'" >> /etc/postgresql/16/main/postgresql.conf > echo "host all all 0.0.0.0/0 scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf > echo "host all all ::/0 scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf
- START
> service postgresql start
- offene DB-Ports zeigen
> ss -antp | grep -F LISTEN | grep -F postgres LISTEN 0 200 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=40606,fd=6)) LISTEN 0 200 [::]:5432 [::]:* users:(("postgres",pid=40606,fd=7))
- DB-Benutzer anzeigen
> echo '\du' | sudo -u postgres psql List of roles Role name | Attributes -----------+------------------------------------------------------------ postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
- Datenbanken anzeigen
> echo '\l' | sudo -u postgres psql List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges -----------+----------+----------+-----------------+---------+---------+------------+-----------+----------------------- postgres | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | template0 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres (3 rows)
Benutzer mit allen Rechten, jedoch ohne Benutzer anlegen oder verändern zu können
# Benutzer anlegen CREATE ROLE dbadmin LOGIN CREATEDB PASSWORD 'Passwort02'; # nicht! zuviele Rechte #ALTER ROLE dbadmin CREATEROLE; #ALTER ROLE dbadmin SUPERUSER; # Rechte für eine Datenbank GRANT CONNECT ON DATABASE deine_datenbank TO dbadmin; GRANT USAGE, CREATE ON SCHEMA public TO dbadmin; # Rechte auf eine bestimmte Tabelle erteilen GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE deine_tabelle TO dbadmin; # Rechte auf alle vorhandenen Tabellen IN "public" erteilen GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO dbadmin; # Rechte auf alle neuen Tabellen IN "public" erteilen ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO dbadmin; # (Optional) Rechte auf Sequences (IDs!) GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dbadmin; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO dbadmin;
Benutzer mit reduzierten Rechten, kann nicht einmal Datenbanken anlegen
# Benutzer anlegen CREATE ROLE dbuser LOGIN PASSWORD 'Passwort01'; # Rechte für eine Datenbank GRANT CONNECT ON DATABASE deine_datenbank TO dbuser; GRANT USAGE, CREATE ON SCHEMA public TO dbuser; # Rechte auf eine bestimmte Tabelle erteilen GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE deine_tabelle TO dbuser; # Rechte auf alle vorhandenen Tabellen IN "public" erteilen GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO dbuser; # Rechte auf alle neuen Tabellen IN "public" erteilen ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO dbuser;
Patroni
- Knoten 1 /etc/patroni/config.yml
scope: pgcluster # name auf Host anpassen name: pg-knoten-01 restapi: listen: 0.0.0.0:8008 # connect_address auf Host anpassen connect_address: pg-knoten-01:8008 etcd3: hosts: - pg-knoten-01:2379 - pg-knoten-02:2379 - pg-knoten-03:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true parameters: wal_level: replica hot_standby: "on" max_wal_senders: 10 max_replication_slots: 10 initdb: - auth-host: scram-sha-256 - auth-local: trust - encoding: UTF8 - locale: de_DE.UTF-8 postgresql: listen: 0.0.0.0:5432 # connect_address auf Host anpassen connect_address: pg-knoten-01:5432 bin_dir: /usr/lib/postgresql/16/bin data_dir: /var/lib/postgresql/16/main pgpass: /var/lib/postgresql/pgpass authentication: superuser: username: postgres password: my_pg_super_pass replication: username: repl password: AejohTe6 pg_hba: - host postgres dbcheck 0.0.0.0/0 trust - host postgres dbcheck ::/0 trust - host all all 0.0.0.0/0 scram-sha-256 - host all all ::/0 scram-sha-256
- Knoten 2 /etc/patroni/config.yml
scope: pgcluster # name auf Host anpassen name: pg-knoten-02 restapi: listen: 0.0.0.0:8008 # connect_address auf Host anpassen connect_address: pg-knoten-02:8008 etcd3: hosts: - pg-knoten-01:2379 - pg-knoten-02:2379 - pg-knoten-03:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true parameters: wal_level: replica hot_standby: "on" max_wal_senders: 10 max_replication_slots: 10 initdb: - auth-host: scram-sha-256 - auth-local: trust - encoding: UTF8 - locale: de_DE.UTF-8 postgresql: listen: 0.0.0.0:5432 # connect_address auf Host anpassen connect_address: pg-knoten-02:5432 bin_dir: /usr/lib/postgresql/16/bin data_dir: /var/lib/postgresql/16/main pgpass: /var/lib/postgresql/pgpass authentication: superuser: username: postgres password: my_pg_super_pass replication: username: repl password: AejohTe6 pg_hba: - host postgres dbcheck 0.0.0.0/0 trust - host postgres dbcheck ::/0 trust - host all all 0.0.0.0/0 scram-sha-256 - host all all ::/0 scram-sha-256
- Knoten 3 /etc/patroni/config.yml
scope: pgcluster # name auf Host anpassen name: pg-knoten-03 restapi: listen: 0.0.0.0:8008 # connect_address auf Host anpassen connect_address: pg-knoten-03:8008 etcd3: hosts: - pg-knoten-01:2379 - pg-knoten-02:2379 - pg-knoten-03:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true parameters: wal_level: replica hot_standby: "on" max_wal_senders: 10 max_replication_slots: 10 initdb: - auth-host: scram-sha-256 - auth-local: trust - encoding: UTF8 - locale: de_DE.UTF-8 postgresql: listen: 0.0.0.0:5432 # connect_address auf Host anpassen connect_address: pg-knoten-03:5432 bin_dir: /usr/lib/postgresql/16/bin data_dir: /var/lib/postgresql/16/main pgpass: /var/lib/postgresql/pgpass authentication: superuser: username: postgres password: my_pg_super_pass replication: username: repl password: AejohTe6 pg_hba: - host postgres dbcheck 0.0.0.0/0 trust - host postgres dbcheck ::/0 trust - host all all 0.0.0.0/0 scram-sha-256 - host all all ::/0 scram-sha-256
- Kontrolle über den PostgreSQL-Dienst an Patroni abgeben
> service postgresql stop > systemctl stop postgresql > systemctl disable postgresql > cp /etc/postgresql/16/main/postgresql.conf /var/lib/postgresql/16/main/ > mkdir /var/lib/postgresql/16/main/conf.d > chown -R postgres:postgres /var/lib/postgresql/16/main > chmod 700 /var/lib/postgresql/16/main/conf.d > systemctl daemon-reload
- Patroni Teststart (kann übersprungen werden)
> patroni /etc/patroni/config.yml &
- Clusterstatus prüfen
> curl http://pg-knoten-01:8008 > curl http://pg-knoten-02:8008 > curl http://pg-knoten-03:8008
- Patroni starten
> systemctl restart patroni
- rol
> echo "SELECT * FROM pg_authid;" | sudo -u postgres psql oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------+-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------+--------------- 10 | postgres | t | t | t | t | t | t | t | -1 | | 6171 | pg_database_owner | f | t | f | f | f | f | f | -1 | | 6181 | pg_read_all_data | f | t | f | f | f | f | f | -1 | | 6182 | pg_write_all_data | f | t | f | f | f | f | f | -1 | | 3373 | pg_monitor | f | t | f | f | f | f | f | -1 | | 3374 | pg_read_all_settings | f | t | f | f | f | f | f | -1 | | 3375 | pg_read_all_stats | f | t | f | f | f | f | f | -1 | | 3377 | pg_stat_scan_tables | f | t | f | f | f | f | f | -1 | | 4569 | pg_read_server_files | f | t | f | f | f | f | f | -1 | | 4570 | pg_write_server_files | f | t | f | f | f | f | f | -1 | | 4571 | pg_execute_server_program | f | t | f | f | f | f | f | -1 | | 4200 | pg_signal_backend | f | t | f | f | f | f | f | -1 | | 4544 | pg_checkpoint | f | t | f | f | f | f | f | -1 | | 4550 | pg_use_reserved_connections | f | t | f | f | f | f | f | -1 | | 6304 | pg_create_subscription | f | t | f | f | f | f | f | -1 | | 16388 | dbadmin | f | t | f | t | t | f | f | -1 | SCRAM-SHA-256$4096:YUFqi+cAZscxHSeXOvzN6A==$pbrqmbIRxqeVNMt9QK4mCP/oFnOZh14y61DC8oHljGw=:SRLJIxF246EpXUFlTm92q3+a5G7zaAKAfsHMvxNsgmg= | 16391 | dbuser | f | t | f | f | t | f | f | -1 | SCRAM-SHA-256$4096:RpnqCLAvwGLKyys/e4+WXA==$ZuKyfVh8FjVMraSLdy5qt5oyBcTk7fVaCQ60afrWK1A=:viPwJxwuxp6lFsDWsvmFXtOJ0l8fUMP3L8PlMZiehrI= | 16418 | dbcheck | f | t | f | f | t | f | f | -1 | | (18 ROWS)
- rol repl
> echo "SELECT * FROM pg_authid WHERE rolname = 'repl';" | sudo -u postgres psql oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------+---------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------+--------------- 16384 | repl | f | t | f | f | t | t | f | -1 | SCRAM-SHA-256$4096:a3FFUXfRHdRwBurdeZHByw==$jea/6EPBkq/8faheWKxKApzTvSqCEIJ7r5BXReDRvMI=:DM5rLR0JWh0D0VMcHHQgYHFs/z/fl/MqLgpjNHmt3QE= | (1 ROW)
- reload_conf
# echo "SELECT pg_reload_conf();" | psql -U postgres pg_reload_conf ---------------- t (1 ROW)
- Einen Slave-Knoten zum Master machen
> echo "SELECT pg_is_in_recovery();" | sudo -u postgres psql pg_is_in_recovery ------------------- t (1 row) > ls -lha /var/lib/postgresql/16/main/standby.signal -rw------- 1 postgres postgres 0 May 8 18:39 /var/lib/postgresql/16/main/standby.signal > sudo pg_ctlcluster 16 main promote > echo "SELECT pg_is_in_recovery();" | sudo -u postgres psql pg_is_in_recovery ------------------- f (1 row) > ls -lha /var/lib/postgresql/16/main/standby.signal ls: cannot access '/var/lib/postgresql/16/main/standby.signal': No such file or directory > echo "CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD 'AejohTe6';" | sudo -u postgres psql CREATE ROLE > echo "ALTER ROLE repl SET synchronous_commit = off;" | sudo -u postgres psql ALTER ROLE > echo "SELECT rolname, rolreplication FROM pg_roles WHERE rolreplication = true;" | sudo -u postgres psql rolname | rolreplication ----------+---------------- postgres | t repl | t (2 rows)
- Patroni-Status
> curl -s http://10.145.43.33:8008/patroni | jq . { "state": "unknown" "role": "replica" "cluster_unlocked": true "dcs_last_seen": 1778262385 "database_system_identifier": "7637152973093863500" "patroni": { "version": "3.2.2" "scope": "pgcluster" "name": "pg-knoten-01" } } > patronictl -c /etc/patroni/config.yml list + Cluster: pgcluster (7637152973093863500) -------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +--------------+--------------+---------+---------+----+-----------+ | pg-knoten-01 | pg-knoten-01 | Replica | running | | unknown | +--------------+--------------+---------+---------+----+-----------+
- den Master umschalten
> patronictl -c /etc/patroni/config.yml failover Current cluster topology + Cluster: pgcluster (7637152973093863500) ---------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +--------------+--------------+---------+---------+----+-----------+ | pg-knoten-01 | pg-knoten-01 | Replica | running | | unknown | +--------------+--------------+---------+---------+----+-----------+ Candidate ['pg-knoten-01'] []: pg-knoten-01 Are you sure you want to failover cluster pgcluster? [y/N]: y
SQL
- /root/bin/User+DB+Tab_zum_testen_anlegen.sh
#!/bin/bash #------------------------------------------------------------------------------# # Test: User+Admin anlegen, DB+Tabelle anlegen, Test-INSERT #------------------------------------------------------------------------------# HA_IP="10.145.43.30" HA_PORT="5433" DB_ROOT="postgres" DB_ADMIN="dbadmin" DB_A_PW="Passwort02" DB_USER="dbuser" DB_U_PW="Passwort01" DATENBANK="testdb" TABELLE="testtab" grep -F 'password: ' /etc/patroni/config.yml #==============================================================================# # User, Tabelle und Datenbank entfernen echo "# ${DB_ROOT}" echo " -- Aktive Verbindungen beenden SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename IN ('${DB_ADMIN}', '${DB_USER}'); -- Datenbank entfernen DROP DATABASE IF EXISTS ${DATENBANK}; -- Berechtigungen auf allen Datenbanken widerrufen: DO \$\$ DECLARE db RECORD; BEGIN FOR db IN SELECT datname FROM pg_database WHERE datistemplate = false LOOP EXECUTE format('REVOKE ALL PRIVILEGES ON DATABASE %I FROM ${DB_ADMIN}, ${DB_USER};', db.datname); END LOOP; END \$\$; -- Rechte auf Schema-Ebene widerrufen: REVOKE ALL ON SCHEMA public FROM ${DB_ADMIN}, ${DB_USER}; -- Rechte auf allen Objekten widerrufen: REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ${DB_ADMIN}, ${DB_USER}; REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM ${DB_ADMIN}, ${DB_USER}; REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM ${DB_ADMIN}, ${DB_USER}; -- Default Privileges für diese User auf alle Datenbanken entfernen ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM dbadmin; ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM dbuser; ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM dbadmin; ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM dbuser; ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM dbadmin; ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM dbuser; DROP USER IF EXISTS ${DB_ADMIN}, ${DB_USER}; " | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT} #==============================================================================# echo "# ${DB_ROOT}" echo " -- Benutzer anlegen CREATE ROLE ${DB_ADMIN} LOGIN CREATEDB PASSWORD '${DB_A_PW}'; -- Rechte für eine Datenbank GRANT USAGE, CREATE ON SCHEMA public TO ${DB_ADMIN}; -- Rechte auf alle vorhandenen Tabellen IN "public" erteilen GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO ${DB_ADMIN}; -- Rechte auf alle neuen Tabellen IN "public" erteilen ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO ${DB_ADMIN}; -- (Optional) Rechte auf Sequences (IDs!) GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ${DB_ADMIN}; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO ${DB_ADMIN}; " | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT} #------------------------------------------------------------------------------# echo "# ${DB_ADMIN}" echo " -- Test-Datenbank anlegen CREATE DATABASE ${DATENBANK}; " | psql -h${HA_IP} -p${HA_PORT} -d ${DB_ROOT} -U ${DB_ADMIN} #------------------------------------------------------------------------------# echo "# ${DB_ROOT}" echo " -- Benutzer anlegen CREATE ROLE ${DB_USER} LOGIN PASSWORD '${DB_U_PW}'; -- Rechte für eine Datenbank GRANT USAGE, CREATE ON SCHEMA public TO ${DB_USER}; -- Rechte auf alle vorhandenen Tabellen IN "public" erteilen GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO ${DB_USER}; -- Rechte auf alle neuen Tabellen IN "public" erteilen ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO ${DB_USER}; " | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT} #------------------------------------------------------------------------------# echo "# ${DB_ADMIN}" echo " -- Permission auf public Schema und der neuen DB erteilen GRANT USAGE, CREATE ON SCHEMA public TO ${DB_ADMIN}; GRANT USAGE, CREATE ON SCHEMA public TO ${DB_USER}; " | psql -h${HA_IP} -p${HA_PORT} -d ${DATENBANK} -U ${DB_ADMIN} #------------------------------------------------------------------------------# echo "# ${DB_USER}" echo " CREATE TABLE ${TABELLE} (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code VARCHAR(10)); INSERT INTO ${TABELLE} (code) VALUES ('ABC'); SELECT * FROM ${TABELLE}; " | psql -h${HA_IP} -p${HA_PORT} -d ${DATENBANK} -U ${DB_USER} #==============================================================================# #echo "# ${DB_ROOT}" #echo " #-- Rechte auf eine bestimmte Datenbank erteilen #GRANT CONNECT ON DATABASE ${DATENBANK} TO ${DB_ADMIN}; #GRANT CONNECT ON DATABASE ${DATENBANK} TO ${DB_USER}; #" | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT} #------------------------------------------------------------------------------# #echo "# ${DB_ROOT}" #echo " #-- Rechte auf eine bestimmte Tabelle erteilen #GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE ${TABELLE} TO ${DB_ADMIN}; #GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE ${TABELLE} TO ${DB_USER}; #" | psql -h${HA_IP} -p${HA_PORT} -U ${DB_ROOT} #==============================================================================#
Wartung
Immer erst mit Dirk Hartmann absprechen!
- /root/bin/Status.sh
#!/bin/bash VERSION="v2026051100" # initiale Erstellung # KeepaliveD prüfen service keepalived status >/dev/null && echo "OK: KeepaliveD läuft" || echo "CRIT: KeepaliveD ist tot" # IP + VIP ip a | grep -F 'inet 10.145.43.3' echo "#------------------------------------------------------------------------------#" # PostgreSQL Commits sudo -u postgres psql -t -c "SELECT 'commits=' || SUM(xact_commit) || ', rollbacks=' || SUM(xact_rollback) FROM pg_stat_database;" # Active Connections sudo -u postgres psql -t -c "SELECT COUNT(*) || ' active connections' FROM pg_stat_activity WHERE state='active';" # HAProxy abfragen echo "#------------------------------------------------------------------------------#" #links -ssl.certificates 0 -dump http://admin:admin@localhost:8404/stats curl -s "http://admin:admin@localhost:8404/stats;csv" | grep -E 'pg_leader|pg_replica' | awk -F',' '{print $2, $18, $56}' # Patroni abfragen patronictl -c /etc/patroni/config.yml list # EtcD abfragen etcdctl --endpoints="http://pg-knoten-01:2379,http://pg-knoten-02:2379,http://pg-knoten-03:2379" endpoint health # Patroni abfragen #echo "#------------------------------------------------------------------------------#" #curl -i http://localhost:8008/leader ; echo # for HIP in 10.145.43.31 10.145.43.32 10.145.43.33 do echo "#------------------------------------------------------------------------------#" #curl -i http://${HIP}:8008/leader curl -s http://${HIP}:8008/leader | jq . | grep -E 'state|postmaster_start_time|role|scope|name' done
- Netzwerkzugriff per HAProxy prüfen
> echo "\l" | psql -h10.145.43.30 -p5433 -U postgres Password for user postgres: List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges -----------+----------+----------+-----------------+-------------+-------------+------------+-----------+----------------------- postgres | postgres | UTF8 | libc | de_DE.UTF-8 | de_DE.UTF-8 | | | template0 | postgres | UTF8 | libc | de_DE.UTF-8 | de_DE.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | libc | de_DE.UTF-8 | de_DE.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres (3 rows) > echo "\du" | psql -h10.145.43.30 -p5433 -U postgres Password for user postgres: List of roles Role name | Attributes -----------+------------------------------------------------------------ postgres | Superuser, Create role, Create DB, Replication, Bypass RLS repl | Replication
Wie das beste Vorgehen bei Wartungsarbeiten ist, muß erst noch ermittelt ausgelotet werden.
> service keepalived stop > service patroni stop > /root/bin/upgrade.sh ; sync ; /root/bin/upgrade.sh ; sync ; /root/bin/upgrade.sh ; sync > reboot && exit
Warum wird hier der HAProxy benötigt?
Wenn ich einen HA-Cluster mit
EtcD
HAProxy
KeepaliveD
PostgreSQL
Patroni
auf nur drei Knoten aufbaue, sodass alle Programme auf allen Knoten laufen, dann belegt PostgreSQL den Port 5432 und HAProxy den Port 5433 und HAProxy prüft Patroni, um zu entscheiden, wer Master ist.
Und KeepaliveD prüft HAProxy, um zu entscheiden, wo die VIP hochgefahren werden soll.
Aber wozu brauche ich HAProxy? KeepaliveD kann doch direkt Patroni prüfen und dann dort, wo der Leader ist, die VIP hochfahren.
# patronictl -c /etc/patroni/config.yml list
+ Cluster: pgcluster (7637598528541987072) -------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+--------------+--------------+---------+---------+----+-----------+
| pg-knoten-01 | pg-knoten-01 | Leader | running | 2 | |
| pg-knoten-02 | pg-knoten-02 | Replica | stopped | | unknown |
| pg-knoten-03 | pg-knoten-03 | Replica | stopped | | unknown |
+--------------+--------------+---------+---------+----+-----------+
HAProxy hat zusätzliche Funktionen, die KeepaliveD nicht bieten kann:
Verwaltet TCP‑Sessions
Erkennt:
PostgreSQL‑Health
Patroni‑State
Kann:
Verbindungen sauber abbrechen oder weiterleiten
Failover kontrollierter durchführen
Write‑Traffic NUR zum Leader schicken
Read‑Traffic auf Replicas verteilen
/home/http/wiki/data/attic/datenbank/ha-postgresql-cluster.1778608425.txt · Zuletzt geändert: von manfred