====== HA-PostgreSQL-Cluster ======
siehe auch: **[[::Datenbank: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 ====
> 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 ====
[[::Csync2]]
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 ====
## 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"
## 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"
## 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"
> systemctl restart etcd
> systemctl enable etcd
> 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
> 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
> 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
> 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 ====
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))
### 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
[]
> links -ssl.certificates 0 -dump http://admin:admin@localhost:8404/stats
==== KeepaliveD ====
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
}
}
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
}
}
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
}
}
#!/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/null 2>&1
if [ $? -eq 0 ]; then
exit 0 # OK → VIP behalten
else
exit 1 # Fehler → VIP abgeben
fi
systemctl enable keepalived
systemctl restart keepalived
> 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 ====
> apt install postgresql postgresql-contrib
> service postgresql stop
> 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
> service postgresql start
> 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))
> echo '\du' | sudo -u postgres psql
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
> 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 ====
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
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
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
> 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 /etc/patroni/config.yml &
> curl http://pg-knoten-01:8008
> curl http://pg-knoten-02:8008
> curl http://pg-knoten-03:8008
> systemctl restart patroni
> 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)
> 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)
# echo "SELECT pg_reload_conf();" | psql -U postgres
pg_reload_conf
----------------
t
(1 row)
> 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)
> 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 |
+--------------+--------------+---------+---------+----+-----------+
> 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 ===
> 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 |
+--------------+--------------+---------+---------+----+-----------+
> 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 ====
#!/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!//
> 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 ====
#!/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
#!/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
#!/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
#!/bin/bash
echo "# Bonus: Alias wie SHOW PROCESSLIST"
echo "
SELECT * FROM show_processlist;
" | sudo -u postgres psql
#!/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
#!/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 ====
> 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 =====
> 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!
#!/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
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)
> /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
#!/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:
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:
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)
> 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)
> 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 '');"
> echo "SELECT COUNT(*) AS Tabellenname FROM tabelle;" | psql -d testdb -U dbuser
===== Verbindungen SSL/TLS-Verschlüsselt =====
==== Verschlüsselung 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
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 ====
> 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 ====
> patronictl -c /etc/patroni/config.yml restart pgcluster
===== temp_tablespaces =====
> mkdir -p /var/lib/postgresql/temp_tablespaces
> chown postgres:postgres /var/lib/postgresql/temp_tablespaces
> chmod 0700 /var/lib/postgresql/temp_tablespaces
> 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
...