HA-PostgreSQL-Cluster

siehe auch: PostgreSQL

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 auf dem Standard-Port (5432) und der HAProxy jeweils auf neuen Ports (schreiben: 5433 / lesen: 5443).

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

PG-DataDir mit ZFS

DataDir im RAID-1

> apt install zfsutils-linux
 
> zpool create -m /var/lib/postgresql pg_datadir mirror /dev/nvme2n1 /dev/nvme3n1
> zpool list
NAME         SIZE  ALLOC   FREE  CKPOINT  EXPANDSZ   FRAG    CAP  DEDUP    HEALTH  ALTROOT
pg_datadir  2.91T   284K  2.91T        -         -     0%     0%  1.00x    ONLINE  -
 
> zpool status
  pool: pg_datadir
 state: ONLINE
config:
 
	NAME         STATE     READ WRITE CKSUM
	pg_datadir   ONLINE       0     0     0
	  mirror-0   ONLINE       0     0     0
	    nvme2n1  ONLINE       0     0     0
	    nvme3n1  ONLINE       0     0     0
 
errors: No known data errors
 
> zfs set compression=lz4    pg_datadir
> zfs set recordsize=16K     pg_datadir
> zfs set logbias=latency    pg_datadir
> zfs set atime=off          pg_datadir
 
> mkdir -p /var/lib/postgresql/16/main
> chown -R postgres:postgres /var/lib/postgresql/
> chmod 700 /var/lib/postgresql/16/main
 
> mount | grep postgresql
 
> zfs get mountpoint,compression,encryption,logbias,recordsize
NAME        PROPERTY     VALUE                SOURCE
pg_datadir  mountpoint   /var/lib/postgresql  local
pg_datadir  compression  lz4                  local
pg_datadir  encryption   off                  default
pg_datadir  logbias      latency              local
pg_datadir  recordsize   16K                  local
 
> df -h
Filesystem      Size  Used Avail Use% Mounted on
...
pg_datadir      2.9T   40M  2.9T   1% /var/lib/postgresql

Csync2

/etc/csync2.cfg

group Loadbalancer #group name, we can have multiple groups
{
    host FRA2A-MEDOS04; #master server
    host FRA2B-MEDOS04; #slave server
    host FRA2C-MEDOS04; #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 FRA2C-MEDOS04 systemd[1]: Starting haproxy.service - HAProxy Load Balancer...
May 08 17:47:16 FRA2C-MEDOS04 haproxy[1219172]: [NOTICE]   (1219172) : New worker (1219177) forked
May 08 17:47:16 FRA2C-MEDOS04 haproxy[1219172]: [NOTICE]   (1219172) : Loading success.
May 08 17:47:16 FRA2C-MEDOS04 systemd[1]: Started haproxy.service - HAProxy Load Balancer.
May 08 17:47:16 FRA2C-MEDOS04 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 FRA2C-MEDOS04 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 -50
        fall 2
}
 
vrrp_instance VIP {
        #interface bond2
        interface vlan203
        virtual_router_id 203
        state BACKUP
        priority 100
        #nopreempt
        advert_int 1
 
        track_script {
                chk_dienst_vip
        }
 
        authentication {
                auth_type PASS
                auth_pass RaBoo9as
        }
 
        virtual_ipaddress {
                10.145.43.30/24 dev vlan203
        }
 
        #mcast_src_ip 10.145.43.31
 
        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 -50
        fall 2
}
 
vrrp_instance VIP {
        #interface bond2
        interface vlan203
        virtual_router_id 203
        state BACKUP
        priority 100
        #nopreempt
        advert_int 1
 
        track_script {
                chk_dienst_vip
        }
 
        authentication {
                auth_type PASS
                auth_pass RaBoo9as
        }
 
        virtual_ipaddress {
                10.145.43.30/24 dev vlan203
        }
 
        #mcast_src_ip 10.145.43.32
 
        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 -50
        fall 2
}
 
vrrp_instance VIP {
        #interface bond2
        interface vlan203
        virtual_router_id 203
        state BACKUP
        priority 100
        #nopreempt
        advert_int 1
 
        track_script {
                chk_dienst_vip
        }
 
        authentication {
                auth_type PASS
                auth_pass RaBoo9as
        }
 
        virtual_ipaddress {
                10.145.43.30/24 dev vlan203
        }
 
        #mcast_src_ip 10.145.43.33
 
        unicast_src_ip 10.145.43.33
        unicast_peer {
                10.145.43.31
                10.145.43.32
                #10.145.43.33
        }
}

/root/bin/check_haproxy.sh

#!/bin/bash
 
#==============================================================================#
# Dieses Skript prüft, ob der HAProxy läuft und erreichbar ist.
#==============================================================================#
 
# Prüfe, ob HAProxy läuft und erreichbar ist
HOST="127.0.0.1"
PORT=5433
 
# timeout wichtig, damit Keepalived nicht hängt
timeout 1 bash -c "</dev/tcp/$HOST/$PORT" >/dev/null 2>&1
 
if [ $? -eq 0 ]; then
    exit 0   # OK → VIP behalten
else
    exit 1   # Fehler → VIP abgeben
fi

Starten

systemctl enable keepalived
systemctl restart keepalived

VIP testen

> ping 10.145.43.30

VRRP-Kommunikation

> # tcpdump -i vlan203 proto 112 -n | grep -F 'vrid 203'
tcpdump: verbose output suppressed, use -v[v]... for full protocol decode
listening on vlan203, link-type EN10MB (Ethernet), snapshot length 262144 bytes
12:51:40.263101 IP 10.145.43.31 > 10.145.43.33: VRRPv2, Advertisement, vrid 203, prio 102, authtype simple, intvl 1s, length 20
12:51:41.263202 IP 10.145.43.31 > 10.145.43.33: VRRPv2, Advertisement, vrid 203, prio 102, authtype simple, intvl 1s, length 20
12:51:42.263398 IP 10.145.43.31 > 10.145.43.33: VRRPv2, Advertisement, vrid 203, prio 102, authtype simple, intvl 1s, length 20
12:51:43.263547 IP 10.145.43.31 > 10.145.43.33: VRRPv2, Advertisement, vrid 203, prio 102, authtype simple, intvl 1s, length 20

PostgreSQL

PostgreSQL installieren

> apt install postgresql postgresql-contrib

STOP

> service postgresql stop

Netzwerkzugriffe erlauben

> echo "listen_addresses = '*'" >> /etc/postgresql/16/main/pg_hba.conf
> echo "host    replication     all             0.0.0.0/0               scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf
> echo "host    replication     all             ::/0                    scram-sha-256" >> /etc/postgresql/16/main/pg_hba.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 replication all 0.0.0.0/0 scram-sha-256
    - host replication all ::/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: my_pg_super_pass
    replication:
      username: repl
      password: AejohTe6
 
pg_hba:
    - host replication all 0.0.0.0/0 scram-sha-256
    - host replication all ::/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: my_pg_super_pass
    replication:
      username: repl
      password: AejohTe6
 
pg_hba:
    - host replication all 0.0.0.0/0 scram-sha-256
    - host replication all ::/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/
> cp /etc/postgresql/16/main/pg_hba.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-03 | 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-03 | 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 das Problem 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

Probleme nach kompletten Cluster-Neustart

IST-Zustand

> patronictl -c /etc/patroni/config.yml list
+ Cluster: pgcluster (7637598528541987072) -------+----+-----------+
| Member       | Host         | Role    | State   | TL | Lag in MB |
+--------------+--------------+---------+---------+----+-----------+
| pg-knoten-01 | pg-knoten-01 | Replica | running | 13 |       217 |
| pg-knoten-02 | pg-knoten-02 | Replica | running | 13 |       217 |
| pg-knoten-03 | pg-knoten-03 | Replica | running | 13 |       217 |
+--------------+--------------+---------+---------+----+-----------+

einen zum Leader bestimmen

> patronictl -c /etc/patroni/config.yml failover pgcluster
+ Cluster: pgcluster (7637598528541987072) -------+----+-----------+
| Member       | Host         | Role    | State   | TL | Lag in MB |
+--------------+--------------+---------+---------+----+-----------+
| pg-knoten-01 | pg-knoten-01 | Replica | running | 13 |       217 |
| pg-knoten-02 | pg-knoten-02 | Replica | running | 13 |       217 |
| pg-knoten-03 | pg-knoten-03 | Replica | running | 13 |       217 |
+--------------+--------------+---------+---------+----+-----------+
Candidate ['pg-knoten-01', 'pg-knoten-02', 'pg-knoten-03'] []: pg-knoten-01
Are you sure you want to failover cluster pgcluster? [y/N]: y
2026-05-13 13:48:00.52027 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 | 13 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | running | 13 |       217 |
| pg-knoten-03 | pg-knoten-03 | Replica | running | 13 |       217 |
+--------------+--------------+---------+---------+----+-----------+
 
> 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   | 14 |           |
| pg-knoten-02 | pg-knoten-02 | Replica | streaming | 14 |         0 |
| pg-knoten-03 | pg-knoten-03 | Replica | streaming | 14 |         0 |
+--------------+--------------+---------+-----------+----+-----------+

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!

Datenbanken und User zeigen

> 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

> 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@FRA2C-MEDOS04]---------------------------------------------------------------------[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@FRA2C-MEDOS04]---------------------------------------------------------------------[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

Daten asynchron einspielen, das geht etwas schneller

> psql -c "SET synchronous_commit=off;"

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
 
#VERSION="v2026051100"		# initiale Erstellung
VERSION="v2026051300"		# jetzt werden die neu berechneten Werte, automatisch in die DB geschrieben
 
#CLUSTER_NAME="pgcluster"
CLUSTER_NAME="$(awk '/^scope: /{print $NF}' /etc/patroni/config.yml)"
 
### 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 "%.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 "%.0f%s\n", e,f}')"
MAINTENANCE_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 "%.0f%s\n", e,f}')"
MAX_CONNECTIONS="$(awk '/^processor[[:space:]]+: /{printf "%.0f\n", $NF*11/10}' /proc/cpuinfo | sort -n | tail -n1)"
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 "%.0f%s\n", e,f}')"
WORK_MEM="$(echo "${RAM_IN_MB}"             | awk '{a=$1/10000; b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "%.0f%s\n", e,f}')"
MAX_WAL_SIZE="$(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 "%.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. 1-10% vom RAM)
max_connections:      ${MAX_CONNECTIONS} (ca. CPU-Kerne + 10%)
shared_buffers:       ${SHARED_BUFFERS} (ca. 10-25% vom RAM)
max_wal_size:         ${MAX_WAL_SIZE} (ca. 10% vom RAM / eigentlich kein Bezug zum RAM, 50-200GB)
work_mem:             ${WORK_MEM} (ca. 0,1-1% vom RAM; Vorsicht, kann zu OOM-Kill führen!)
"
 
### neu berechnete Werte, direkt setzen
### patronictl -c /etc/patroni/config.yml edit-config pgcluster
echo "#==============================================================================#"
echo
jq -n \
  --arg ecs "${EFFECTIVE_CACHE_SIZE}" \
  --arg mwm "${MAINTENANCE_WORK_MEM}" \
  --arg mc  "${MAX_CONNECTIONS}" \
  --arg sb  "${SHARED_BUFFERS}" \
  --arg mw  "${MAX_WAL_SIZE}" \
  --arg wm  "${WORK_MEM}" \
'{
  postgresql: {
    parameters: {
      effective_cache_size: $ecs,
      maintenance_work_mem: $mwm,
      max_connections: $mc,
      shared_buffers: $sb,
      max_wal_size: $mw,
      wal_buffers: "1GB",
      checkpoint_timeout: "30min",
      work_mem: $wm
    }
  }
}' | curl -s -X PATCH http://localhost:8008/config \
        -H "Content-Type: application/json" \
        --data-binary @-
echo
echo "#==============================================================================#"
 
### aktuelle Einstellungen anzeigen
patronictl -c /etc/patroni/config.yml show-config ${CLUSTER_NAME}
 
### neue Einstellungen aktivieren
### shared_buffers benötigt leider einen Restart
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		151 GB
max_connections		150
shared_buffers		378 GB
work_mem		15 GB
 
# empfohlene Werte, die meistens passen:
autovacuum_work_mem:  151GB (ca. 10% vom RAM)
effective_cache_size: 1134GB (ca. 50-75% vom RAM)
maintenance_work_mem: 151GB (ca. 3-10% vom RAM)
max_connections:      140 (ca. CPU-Kerne + 10%)
shared_buffers:       378GB (ca. 10-25% vom RAM)
work_mem:             15GB (ca. 1% vom RAM; Vorsicht, kann zu OOM-Kill führen!)
 
#==============================================================================#
 
{"loop_wait": 10, "maximum_lag_on_failover": 1048576, "postgresql": {"parameters": {"effective_cache_size": "1134GB", "hot_standby": "on", "maintenance_work_mem": "151GB", "max_replication_slots": 10, "max_wal_senders": 10, "shared_buffers": "378GB", "wal_level": "replica", "work_mem": "15GB", "max_connections": "140"}, "use_pg_rewind": true}, "retry_timeout": 10, "ttl": 30}
#==============================================================================#
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    effective_cache_size: 1134GB
    hot_standby: 'on'
    maintenance_work_mem: 151GB
    max_connections: '140'
    max_replication_slots: 10
    max_wal_senders: 10
    shared_buffers: 378GB
    wal_level: replica
    work_mem: 15GB
  use_pg_rewind: true
retry_timeout: 10
ttl: 30

/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/CPU-Nutzung.sh
 pid | backend_type | state 
-----+--------------+-------
(0 rows)
 
   backend_type    | count 
-------------------+-------
 client backend    |     3
 startup           |     1
 background writer |     1
 walreceiver       |     1
 checkpointer      |     1
(5 rows)

Tests

> time sudo -u postgres pgbench -i -s 1000 testdb
dropping old tables...
creating tables...
generating data (client-side)...
100000000 of 100000000 tuples (100%) done (elapsed 85.93 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 125.30 s (drop tables 4.04 s, create tables 0.01 s, client-side generate 86.85 s, vacuum 0.84 s, primary keys 33.56 s).
 
real	2m5.365s
user	0m0.038s
sys	0m0.077s
 
> sudo -u postgres pgbench -c 128 -j 128 -T 120 -N testdb
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: simple update>
scaling factor: 1000
query mode: simple
number of clients: 128
number of threads: 128
maximum number of tries: 1
duration: 120 s
number of transactions actually processed: 5956017
number of failed transactions: 0 (0.000%)
latency average = 2.578 ms
initial connection time = 66.949 ms
tps = 49656.884083 (without initial connection time)
 
> sudo -u postgres pgbench -c 250 -j 250 -T 120 -N testdb
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: simple update>
scaling factor: 1000
query mode: simple
number of clients: 250
number of threads: 250
maximum number of tries: 1
duration: 120 s
number of transactions actually processed: 6463558
number of failed transactions: 0 (0.000%)
latency average = 4.637 ms
initial connection time = 154.766 ms
tps = 53915.406631 (without initial connection time)

WAL

> echo "SELECT wal_write_time, wal_sync_time FROM pg_stat_wal;" | sudo -u postgres psql -d testdb
 wal_write_time | wal_sync_time 
----------------+---------------
              0 |             0
(1 row)

ZFS I/O

> zpool iostat -v
               capacity     operations     bandwidth 
pool         alloc   free   read  write   read  write
-----------  -----  -----  -----  -----  -----  -----
pg_datadir   9.12G  2.90T      0  1.32K    276  29.3M
  mirror-0   9.12G  2.90T      0  1.32K    276  29.3M
    nvme2n1      -      -      0    673    138  14.6M
    nvme3n1      -      -      0    676    138  14.6M
-----------  -----  -----  -----  -----  -----  -----

Testdaten einspielen

> echo "COPY tabelle FROM '/tmp/export_mit_header.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER MATCH, NULL '');" | psql -U dbuser -d testdb
 
oder
 
> cat /tmp/export_mit_header.tsv | psql -d testdb -U dbuser -c "COPY tabelle FROM STDIN WITH (FORMAT csv, DELIMITER E'\t', HEADER MATCH, NULL '');"

Datensätze zählen

> echo "SELECT COUNT(*) AS Tabellenname FROM tabelle;" | psql -d testdb -U dbuser

Verbindungen SSL/TLS-Verschlüsselt

Verschlüsselung konfigurieren

postgresql.conf

> 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
    ssl: on
    ssl_ca_file: '/etc/ssl/server-ca.pem'
    ssl_cert_file: '/etc/ssl/server-cert.pem'
    ssl_key_file: '/etc/ssl/server-key.pem'
    ssl_min_protocol_version: 'TLSv1.2'
    ssl_ciphers: 'HIGH:!aNULL:!3DES:!MD5:!DES:!MEDIUM'
    ssl_prefer_server_ciphers: on
  use_pg_rewind: true
retry_timeout: 10
ttl: 30

Verschlüsselung erzwingen

pg_hba.conf

> vim /etc/patroni/config.yml
...
pg_hba:
    - hostnossl all all 0.0.0.0/0 reject
    - hostnossl all all ::/0 reject
    - hostssl replication all 0.0.0.0/0 scram-sha-256
    - hostssl replication all ::/0 scram-sha-256
    - hostssl all all 0.0.0.0/0 scram-sha-256
    - hostssl all all ::/0 scram-sha-256

Verschlüsselung aktivieren

kompletten Cluster restarten

> patronictl -c /etc/patroni/config.yml restart pgcluster

temp_tablespaces

temp_tablespaces-Verzeichnis anlegen

> mkdir -p /var/lib/postgresql/temp_tablespaces
> chown postgres:postgres /var/lib/postgresql/temp_tablespaces
> chmod 0700 /var/lib/postgresql/temp_tablespaces

temp_tablespaces konfigurieren

> echo "SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace WHERE spcname = 'fasttmp';" | sudo -u postgres psql
> echo "CREATE TABLESPACE fasttmp LOCATION '/var/lib/postgresql/temp_tablespaces';" | sudo -u postgres psql
> echo "SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace WHERE spcname = 'fasttmp';" | sudo -u postgres psql
 
> patronictl -c /etc/patroni/config.yml edit-config
...
postgresql:
  parameters:
    temp_file_limit: '-1'
    temp_tablespaces: fasttmp
    ...

Inhaltsverzeichnis