Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » datenbank » ha-postgresql-cluster
Zuletzt angesehen: • zusammenstellung_verschiedener_ansaetze_zur_gewinnung_von_freier_energie • die_spirituellen_geheimnisse • integrierter_schaltkreis_ic • windows • interessantes • wsl • gesundheit • survival • systemueberpruefung • ha-postgresql-cluster
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). Weiterhin gibt es in diesem Aufbau noch einen schreibgeschützten Port (5443), über den man auf den man nicht in die DB schreiben kann und von den Replicas liest, um den Leader zu entlasten.

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

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				# Port zum schreiben
	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_replicas' | 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 *:5443				# Port zum lesen
	#default_backend pg_replicas		# bei Ausfall von 2 Knoten, kein lesen mehr möglich
	#
	# Wenn mindestens eine Replica gesund ist → Replicas
	use_backend pg_replicas if { nbsrv(pg_replicas) gt 0 }
	#
	# Andernfalls automatisch zum Leader
	default_backend pg_leader
 
backend pg_replicas
	mode tcp
	option httpchk
	balance leastconn			# Verteilt nach geringster Last
	http-check send meth GET uri /replica ver HTTP/1.1 hdr Host localhost
	http-check expect status 200
	default-server inter 3s fall 3 rise 2
	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
> 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:8404                0.0.0.0:*     users:(("haproxy",pid=2513697,fd=9))
LISTEN    0      4096                 0.0.0.0:5443                0.0.0.0:*     users:(("haproxy",pid=2513697,fd=11))
LISTEN    0      4096                 0.0.0.0:5433                0.0.0.0:*     users:(("haproxy",pid=2513697,fd=10))
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: geheim01
    replication:
      username: repl
      password: geheim02
 
pg_hba:
    - host replication all 0.0.0.0/0 scram-sha-256
    - 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: geheim01
    replication:
      username: repl
      password: geheim02
 
pg_hba:
    - host replication all 0.0.0.0/0 scram-sha-256
    - 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: geheim01
    replication:
      username: repl
      password: geheim02
 
pg_hba:
    - host replication all 0.0.0.0/0 scram-sha-256
    - 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
> curl -s "http://admin:admin@localhost:8404/stats;csv" | grep -E 'pg_leader|pg_replica' | awk -F',' '{print $2, $18, $56}'
pg1 UP 1752
pg2 DOWN -1
pg3 DOWN -1
BACKEND UP 1752
pg1 DOWN -1
pg2 DOWN -1
pg3 DOWN -1
BACKEND DOWN -1
 
> 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 |  3 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown |
| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown |
+--------------+--------------+---------+---------+----+-----------+
 
 
Wenn das so aussieht, dann kann von den Replicas nicht gelesen werden.
Um Problem das zu beheben, müssen die Replicas (beide) reinitialisiert werden.
 
 
> patronictl -c /etc/patroni/config.yml reinit pgcluster pg-knoten-02
+ Cluster: pgcluster (7637598528541987072) -------+----+-----------+
| Member       | Host         | Role    | State   | TL | Lag in MB |
+--------------+--------------+---------+---------+----+-----------+
| pg-knoten-01 | pg-knoten-01 | Leader  | running |  3 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown |
| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown |
+--------------+--------------+---------+---------+----+-----------+
Are you sure you want to reinitialize members pg-knoten-02? [y/N]: y
Failed: reinitialize for member pg-knoten-02, status code=503, (bootstrap from leader 'pg-knoten-01' already in progress)
Do you want to cancel it and reinitialize anyway? [y/N]: y
Success: reinitialize for member pg-knoten-02
 
> patronictl -c /etc/patroni/config.yml reinit pgcluster pg-knoten-03
+ Cluster: pgcluster (7637598528541987072) -------+----+-----------+
| Member       | Host         | Role    | State   | TL | Lag in MB |
+--------------+--------------+---------+---------+----+-----------+
| pg-knoten-01 | pg-knoten-01 | Leader  | running |  3 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown |
| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown |
+--------------+--------------+---------+---------+----+-----------+
Are you sure you want to reinitialize members pg-knoten-03? [y/N]: y
Failed: reinitialize for member pg-knoten-03, status code=503, (bootstrap from leader 'pg-knoten-01' already in progress)
Do you want to cancel it and reinitialize anyway? [y/N]:  
 
 
Jetzt arbeiten die Replicas ordentliche und man kann von ihnen auch ordentlich lesen:
 
 
> 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   |  6 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | streaming |  6 |         0 |
| pg-knoten-03 | pg-knoten-03 | Replica | streaming |  6 |         0 |
+--------------+--------------+---------+-----------+----+-----------+
 
> curl -s "http://admin:admin@localhost:8404/stats;csv" | grep -E 'pg_leader|pg_replica' | awk -F',' '{print $2, $18, $56}'
pg1 UP 6000
pg2 DOWN -1
pg3 DOWN -1
BACKEND UP 6000
pg1 DOWN -1
pg2 UP -1
pg3 UP -1
BACKEND UP -1

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

Status / Monitoring

/root/bin/Status.sh
#!/bin/bash
 
#VERSION="v2026051100"		# initiale Erstellung
VERSION="v2026051200"		# erweitert
 
uptime
 
# 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
pg_isready -q && sudo -u postgres psql -t -c "SELECT 'commits=' || SUM(xact_commit) || ', rollbacks=' || SUM(xact_rollback) FROM pg_stat_database;" || echo Replica
 
# Active Connections
pg_isready -q && 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
 
echo "#------------------------------------------------------------------------------#"
pg_isready -q && echo "SELECT name, setting, unit, SOURCE, sourcefile, sourceline
FROM pg_settings
WHERE name IN (
  'autovacuum_work_mem',
  'effective_cache_size',
  'maintenance_work_mem',
  'max_connections',
  'shared_buffers',
  'work_mem'
  );" | sudo -u postgres psql
/root/bin/CPU-Nutzung.sh
#!/bin/bash
 
# Laufende Parallel‑Worker sehen
 
echo "
SELECT pid, backend_type, state
FROM pg_stat_activity
WHERE backend_type LIKE '%parallel%';
 
SELECT backend_type, count(*)
FROM pg_stat_activity
GROUP BY backend_type;
" | sudo -u postgres psql
/root/bin/CREATE_VIEW_SHOW_PROCESSLIST.sh
#!/bin/bash
 
echo "# Bonus: Alias wie SHOW PROCESSLIST"
echo "
CREATE VIEW show_processlist AS
SELECT
    pid AS id,
    usename AS "User",
    client_addr AS host,
    datname AS db,
    state,
    now() - query_start AS time,
    query AS info
FROM pg_stat_activity;
" | sudo -u postgres psql
/root/bin/SHOW_PROCESSLIST.sh
#!/bin/bash
 
 
echo "# Bonus: Alias wie SHOW PROCESSLIST"
echo "
SELECT * FROM show_processlist;
" | sudo -u postgres psql
/root/bin/DB-Status.sh
#!/bin/bash
 
 
#echo "# pg_stat_activity"
#echo "
#SELECT * FROM pg_stat_activity;
#" | sudo -u postgres psql
 
#echo "### Praxisnahe Varianten (das, was man wirklich braucht)"
#echo "# Übersicht wie in MySQL"
#echo "
#SELECT
#    pid,
#    usename,
#    datname,
#    state,
#    client_addr,
#    application_name,
#    backend_start,
#    query_start,
#    query
#FROM pg_stat_activity
#ORDER BY query_start DESC;
#" | sudo -u postgres psql
 
echo "# Nur aktive Queries (was läuft gerade)"
echo "
SELECT
    pid,
    usename,
    now() - query_start AS runtime,
    state,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY runtime DESC;
" | sudo -u postgres psql
 
echo "# Laufzeitkiller: die schlimmsten Queries"
echo "
SELECT
    pid,
    usename,
    now() - query_start AS runtime,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes'
ORDER BY runtime DESC;
" | sudo -u postgres psql
 
echo "# Wer blockiert wen?"
echo "
SELECT
    a.pid AS blocked_pid,
    a.query AS blocked_query,
    b.pid AS blocking_pid,
    b.query AS blocking_query
FROM pg_locks bl
JOIN pg_stat_activity a ON a.pid = bl.pid
JOIN pg_locks kl
  ON kl.locktype = bl.locktype
 AND kl.database IS NOT DISTINCT FROM bl.database
 AND kl.relation IS NOT DISTINCT FROM bl.relation
 AND kl.page IS NOT DISTINCT FROM bl.page
 AND kl.tuple IS NOT DISTINCT FROM bl.tuple
 AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid
 AND kl.classid IS NOT DISTINCT FROM bl.classid
 AND kl.objid IS NOT DISTINCT FROM bl.objid
 AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid
 AND kl.pid != bl.pid
JOIN pg_stat_activity b ON b.pid = kl.pid
WHERE NOT bl.granted;
" | sudo -u postgres psql
 
#echo "### Sessions beenden (KILL QUERY / KILL CONNECTION)"
#echo "# Query abbrechen (sanft)"
#echo "
#SELECT pg_cancel_backend(pid)
#FROM pg_stat_activity
#WHERE pid = 12345;
#" | sudo -u postgres psql
 
#echo "# Session hart beenden"
#echo "
#SELECT pg_terminate_backend(pid)
#FROM pg_stat_activity
#WHERE pid = 12345;
#" | sudo -u postgres psql
 
echo "# Idle in transaction (sehr wichtig!)"
echo "
SELECT
    pid,
    usename,
    now() - query_start AS idle_time,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_time DESC;
" | sudo -u postgres psql
/root/bin/DB-Status_-_nur_eigenes.sh
#!/bin/bash
 
 
if [ x = "x${1}" ] ; then
	DBUSER="postgres"
else
	DBUSER="${1}"
fi
 
echo "# Nur eigene DB / eigener User"
#echo "
#SELECT *
#FROM pg_stat_activity
#WHERE datname = current_database();
#" | sudo -u postgres psql
 
#echo "# Oder"
echo "
SELECT *
FROM pg_stat_activity
WHERE usename = '${DBUSER}';
" | sudo -u postgres psql

Replica reinitialisieren

pg-knoten-01 reparieren - neu syncen
> patronictl -c /etc/patroni/config.yml reinit pgcluster pg-knoten-02
+ Cluster: pgcluster (7637598528541987072) -------+----+-----------+
| Member       | Host         | Role    | State   | TL | Lag in MB |
+--------------+--------------+---------+---------+----+-----------+
| pg-knoten-01 | pg-knoten-01 | Leader  | running |  3 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown |
| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown |
+--------------+--------------+---------+---------+----+-----------+
Are you sure you want to reinitialize members pg-knoten-02? [y/N]: y
Failed: reinitialize for member pg-knoten-02, status code=503, (bootstrap from leader 'pg-knoten-01' already in progress)
Do you want to cancel it and reinitialize anyway? [y/N]: y
Success: reinitialize for member pg-knoten-02

failover

[root@pg-knoten-03]---------------------------------------------------------------------[0]-[0]-[2026-05-12 14:26:12]
[~]# patronictl -c /etc/patroni/config.yml failover
Current cluster topology
+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+
| Member       | Host         | Role    | State     | TL | Lag in MB |
+--------------+--------------+---------+-----------+----+-----------+
| pg-knoten-01 | pg-knoten-01 | Replica | streaming |  8 |         0 |
| pg-knoten-02 | pg-knoten-02 | Leader  | running   |  8 |           |
| pg-knoten-03 | pg-knoten-03 | Replica | streaming |  8 |         0 |
+--------------+--------------+---------+-----------+----+-----------+
Candidate ['pg-knoten-01', 'pg-knoten-03'] []: pg-knoten-01
Are you sure you want to failover cluster pgcluster, demoting current leader pg-knoten-02? [y/N]: y
2026-05-12 14:26:55.23394 Successfully failed over to "pg-knoten-01"
+ Cluster: pgcluster (7637598528541987072) -------+----+-----------+
| Member       | Host         | Role    | State   | TL | Lag in MB |
+--------------+--------------+---------+---------+----+-----------+
| pg-knoten-01 | pg-knoten-01 | Leader  | running |  8 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown |
| pg-knoten-03 | pg-knoten-03 | Replica | running |  8 |         0 |
+--------------+--------------+---------+---------+----+-----------+
 
[root@pg-knoten-03]---------------------------------------------------------------------[0]-[0]-[2026-05-12 14:26:55]
[~]# 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   |  9 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | streaming |  9 |         0 |
| pg-knoten-03 | pg-knoten-03 | Replica | streaming |  9 |         0 |
+--------------+--------------+---------+-----------+----+-----------+

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

PostgreSQL-Tuning

Kurzfassung:
 
autovacuum_work_mem  => 3% bis 10% des RAM
effective_cache_size => 50% bis 75% der RAM-Größe
maintenance_work_mem => 3% bis 10% des RAM
max_connections      => nur soviel, wie man wirklich braucht
shared_buffers       => 10% bis 25% der RAM-Größe
 
                      frei verfügbarer RAM
work_mem ~ --------------------------------------------
           max_connections * Sort/Hash-Knoten pro Query
 
work_mem ~           => ca. 1% der RAM-Größe; Vorsicht, kann zu OOM-Kill führen!
/root/bin/PostgreSQL-Tuning.sh
#!/bin/bash
 
### RAM: 1547643 MB
# > echo "SELECT name, setting, unit, SOURCE, sourcefile, sourceline FROM pg_settings WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'max_connections', 'maintenance_work_mem', 'autovacuum_work_mem', 'shared_memory_size');" | sudo -u postgres psql
#          name         |  setting  | unit |       source       |                 sourcefile                  | sourceline 
# ----------------------+-----------+------+--------------------+---------------------------------------------+------------
#  autovacuum_work_mem  | -1        | kB   | default            |                                             |           
#  effective_cache_size | 148635648 | 8kB  | configuration file | /var/lib/postgresql/16/main/postgresql.conf |          6
#  maintenance_work_mem | 78643200  | kB   | configuration file | /var/lib/postgresql/16/main/postgresql.conf |          9
#  max_connections      | 100       |      | command line       |                                             |           
#  shared_buffers       | 49545216  | 8kB  | configuration file | /var/lib/postgresql/16/main/postgresql.conf |         17
#  shared_memory_size   | 394457    | MB   | default            |                                             |           
#  work_mem             | 8388608   | kB   | configuration file | /var/lib/postgresql/16/main/postgresql.conf |         22
# (7 rows)
 
RAM_IN_MB="$(free -m | tail -n+2 | head -n1 | awk '{printf "%.0f\n", $2}')"
echo "RAM: ${RAM_IN_MB} MB"
 
S_PG="$(echo "SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'autovacuum_work_mem',
  'effective_cache_size',
  'maintenance_work_mem',
  'max_connections',
  'shared_buffers',
  'work_mem'
);" | sudo -u postgres psql -t | head -n-1 | awk '{print $1,$3,$5}' | grep -Fv -- '-1' | awk '{a=$2; if ($3=="8kB") {a=$2*8}; b=a/1024" MB"; if (a>10485760) b=a/1024/1024" GB"; if ($1=="max_connections") b=$2; print $1"\t\t"b}')"
echo "
# IST-Werte (ggf. autovacuum_work_mem = maintenance_work_mem):
${S_PG}
"
 
AUTOVACUUM_WORK_MEM="$(echo "${RAM_IN_MB}"  | awk '{a=$1/10;  b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "\t\t%.0f %s\n", e,f}')"
EFFECTIVE_CACHE_SIZE="$(echo "${RAM_IN_MB}" | awk '{a=$1*3/4; b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "\t\t%.0f %s\n", e,f}')"
MAINTENANCE_WORK_MEM="$(echo "${RAM_IN_MB}" | awk '{a=$1/10;  b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "\t\t%.0f %s\n", e,f}')"
SHARED_BUFFERS="$(echo "${RAM_IN_MB}"       | awk '{a=$1/4;   b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "\t\t%.0f %s\n", e,f}')"
WORK_MEM="$(echo "${RAM_IN_MB}"             | awk '{a=$1/100; b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "\t\t%.0f %s\n", e,f}')"
 
echo "# empfohlene Werte, die meistens passen:
autovacuum_work_mem: ${AUTOVACUUM_WORK_MEM} (ca. 10% vom RAM)
effective_cache_size: ${EFFECTIVE_CACHE_SIZE} (ca. 50-75% vom RAM)
maintenance_work_mem: ${MAINTENANCE_WORK_MEM} (ca. 3-10% vom RAM)
shared_buffers: ${SHARED_BUFFERS} (ca. 10-25% vom RAM)
work_mem: ${WORK_MEM} (ca. 1% vom RAM; Vorsicht, kann zu OOM-Kill führen!)
"
Konfigurationsvorschlag vorher ansehen
> /root/bin/PostgreSQL-Tuning.sh
RAM: 1547643 MB
 
# IST-Werte (ggf. autovacuum_work_mem = maintenance_work_mem):
effective_cache_size		4096 MB
maintenance_work_mem		64 MB
max_connections		100
shared_buffers		128 MB
work_mem		4 MB
 
# empfohlene Werte, die meistens passen:
autovacuum_work_mem: 		151 GB (ca. 10% vom RAM)
effective_cache_size: 		1134 GB (ca. 50-75% vom RAM)
maintenance_work_mem: 		151 GB (ca. 3-10% vom RAM)
shared_buffers: 		378 GB (ca. 10-25% vom RAM)
work_mem: 		15 GB (ca. 1% vom RAM; Vorsicht, kann zu OOM-Kill führen!)
Patroni konfigurieren
> patronictl -c /etc/patroni/config.yml edit-config pgcluster
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    hot_standby: 'on'
    max_replication_slots: 10
    max_wal_senders: 10
    wal_level: replica
    effective_cache_size: 1134GB
    maintenance_work_mem: 75GB
    shared_buffers: 378GB
    work_mem: 8GB
  use_pg_rewind: true
retry_timeout: 10
ttl: 30
 
Apply these changes? [y/N]: y
Configuration changed
 
 
> service patroni restart
RAM: 1547643 MB
         name         |  setting  | unit |       source       |                 sourcefile                  | sourceline 
----------------------+-----------+------+--------------------+---------------------------------------------+------------
 autovacuum_work_mem  | -1        | kB   | default            |                                             |           
 effective_cache_size | 148635648 | 8kB  | configuration file | /var/lib/postgresql/16/main/postgresql.conf |          6
 maintenance_work_mem | 78643200  | kB   | configuration file | /var/lib/postgresql/16/main/postgresql.conf |          9
 max_connections      | 100       |      | command line       |                                             |           
 shared_buffers       | 49545216  | 8kB  | configuration file | /var/lib/postgresql/16/main/postgresql.conf |         17
 shared_memory_size   | 394457    | MB   | default            |                                             |           
 work_mem             | 8388608   | kB   | configuration file | /var/lib/postgresql/16/main/postgresql.conf |         22
(7 rows)
Konfigurationsvorschlag nachher ansehen
> /root/bin/PostgreSQL-Tuning.sh
RAM: 1547643 MB
 
# IST-Werte (ggf. autovacuum_work_mem = maintenance_work_mem):
effective_cache_size		1134 GB
maintenance_work_mem		75 GB
max_connections		100
shared_buffers		378 GB
work_mem		8192 MB
 
# empfohlene Werte, die meistens passen:
autovacuum_work_mem: 		151 GB (ca. 10% vom RAM)
effective_cache_size: 		1134 GB (ca. 50-75% vom RAM)
maintenance_work_mem: 		151 GB (ca. 3-10% vom RAM)
shared_buffers: 		378 GB (ca. 10-25% vom RAM)
work_mem: 		15 GB (ca. 1% vom RAM; Vorsicht, kann zu OOM-Kill führen!)
/root/bin/CPU-Nutzung.sh
#!/bin/bash
 
 
# Laufende Parallel‑Worker sehen
 
echo "
SELECT pid, backend_type, state
FROM pg_stat_activity
WHERE backend_type LIKE '%parallel%';
 
SELECT backend_type, count(*)
FROM pg_stat_activity
GROUP BY backend_type;
" | sudo -u postgres psql
/home/http/wiki/data/attic/datenbank/ha-postgresql-cluster.1778611742.txt · Zuletzt geändert: von manfred