Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » datenbank » ha-postgresql-cluster
Zuletzt angesehen: • die_spirituellen_geheimnisse • integrierter_schaltkreis_ic • windows • interessantes • wsl • gesundheit • survival • systemueberpruefung • ha-postgresql-cluster • gesellschaft
datenbank:ha-postgresql-cluster

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
datenbank:ha-postgresql-cluster [2026-05-13 21:22:40] – manfreddatenbank:ha-postgresql-cluster [2026-05-29 10:09:43] (aktuell) – [Tests] manfred
Zeile 1: Zeile 1:
 ====== HA-PostgreSQL-Cluster ====== ====== HA-PostgreSQL-Cluster ======
 +
 +siehe auch: **[[::Datenbank:PostgreSQL]]**
  
  
Zeile 6: Zeile 8:
 //Es soll ein hochverfügbarer PostgreSQL‑Cluster mit Patroni, EtcE, HAProxy und KeepaliveD auf drei Systemen installiert werden.// //Es soll ein hochverfügbarer PostgreSQL‑Cluster mit Patroni, EtcE, HAProxy und KeepaliveD auf drei Systemen installiert werden.//
  
-Wichtige Unterschiede zwischen diesem HA-PostgreSQL-Cluster mit 3 Systemen, gegenüber einem HA-PostgreSQL-Cluster mit 5 Systemen sind, dass hier alle Pakete auf allen 3 Systemen installiert werden müssen. Und weil PostgreSQL und HAProxy auf dem selben Host laufen, muß der Datenbank-Zugriff über den Cluster-Zugang (HAProxy) auf einen anderen Port laufen. In diesem Fall laufen die PostgreSQL-Instanzen aus dem Standard-Port (''5432'') und der HAProxy jeweils auf einen neuen Port (''5433''). +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. 
-Weiterhin gibt es in diesem Aufbau noch einen schreibgeschützten Port (''5443''), über den man auf den man nicht in die DB schreiben kann und von den Replicas liest, um den Leader zu entlasten.+In 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. 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.
Zeile 23: Zeile 25:
  
 ==== PG-DataDir mit ZFS ==== ==== PG-DataDir mit ZFS ====
- 
-siehe auch: [[::Dokumentationen:ZFS:ZFS#PostgreSQL]] 
  
 <code bash DataDir im RAID-1> <code bash DataDir im RAID-1>
Zeile 75: Zeile 75:
 ==== Csync2 ==== ==== Csync2 ====
  
-[[::dokumentationen:Csync2]]+[[::Csync2]]
  
 <file bash /etc/csync2.cfg> <file bash /etc/csync2.cfg>
Zeile 194: Zeile 194:
  
 <code text Cluster testen> <code text Cluster testen>
-> etcdctl --endpoints="http://fra2a-medos04:2379,http://fra2b-medos04:2379,http://fra2c-medos04:2379" endpoint health +> etcdctl --endpoints="http://pg-knoten-01:2379,http://pg-knoten-02:2379,http://pg-knoten-03:2379" endpoint health 
-http://fra2a-medos04:2379 is healthy: successfully committed proposal: took = 3.710884ms +http://pg-knoten-01:2379 is healthy: successfully committed proposal: took = 3.710884ms 
-http://fra2b-medos04:2379 is healthy: successfully committed proposal: took = 5.53501ms +http://pg-knoten-02:2379 is healthy: successfully committed proposal: took = 5.53501ms 
-http://fra2c-medos04:2379 is healthy: successfully committed proposal: took = 7.426682ms+http://pg-knoten-03:2379 is healthy: successfully committed proposal: took = 7.426682ms
 </code> </code>
  
Zeile 203: Zeile 203:
 === Hinweis === === Hinweis ===
  
-**//Solange der Cluster unhealthy ist, sind Verwaltungsoperationen wie etcdctl member add absichtlich verboten.//**+**//Solange der Cluster unhealthy ist, sind Verwaltungsoperationen wie ''etcdctl member add'' absichtlich verboten.//**
  
 <code bash> <code bash>
Zeile 300: Zeile 300:
  http-check expect status 200  http-check expect status 200
  default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions  default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
- server pg1 fra2a-medos04:5432 check port 8008 maxconn 100 + server pg1 pg-knoten-01:5432 check port 8008 maxconn 100 
- server pg2 fra2b-medos04:5432 check port 8008 maxconn 100 + server pg2 pg-knoten-02:5432 check port 8008 maxconn 100 
- server pg3 fra2c-medos04:5432 check port 8008 maxconn 100+ server pg3 pg-knoten-03:5432 check port 8008 maxconn 100
  
 # ============================ # ============================
Zeile 333: Zeile 333:
  http-check expect status 200  http-check expect status 200
  default-server inter 3s fall 3 rise 2  default-server inter 3s fall 3 rise 2
- server pg1 fra2a-medos04:5432 check port 8008 maxconn 100 + server pg1 pg-knoten-01:5432 check port 8008 maxconn 100 
- server pg2 fra2b-medos04:5432 check port 8008 maxconn 100 + server pg2 pg-knoten-02:5432 check port 8008 maxconn 100 
- server pg3 fra2c-medos04:5432 check port 8008 maxconn 100+ server pg3 pg-knoten-03:5432 check port 8008 maxconn 100
 </code> </code>
  
Zeile 371: Zeile 371:
 Content-Type: application/json 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": "fra2a-medos04"}}+{"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"}}
  
  
Zeile 381: Zeile 381:
 Content-Type: application/json 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": "fra2a-medos04"}}+{"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"}}
  
  
Zeile 392: Zeile 392:
 # HELP patroni_version Patroni semver without periods. # HELP patroni_version Patroni semver without periods.
 # TYPE patroni_version gauge # TYPE patroni_version gauge
-patroni_version{scope="pgcluster",name="fra2a-medos04"} 030202+patroni_version{scope="pgcluster",name="pg-knoten-01"} 030202
 ... ...
  
Zeile 402: Zeile 402:
 Content-Type: application/json Content-Type: application/json
  
-{"members": [{"name": "fra2a-medos04", "role": "replica", "state": "running", "api_url": "http://fra2c-medos04:8008/patroni", "host": "fra2c-medos04", "port": 5432, "lag": "unknown"}], "scope": "pgcluster"}+{"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"}
  
  
Zeile 420: Zeile 420:
  
 ==== KeepaliveD ==== ==== KeepaliveD ====
- 
-//KeepaliveD hat Probleme mit den NIC ''vlan203@bond2'' klar zu kommen, deshalb wir hier Multicast verwendet.// 
  
 <file bash Knoten 1 – /etc/keepalived/keepalived.conf> <file bash Knoten 1 – /etc/keepalived/keepalived.conf>
Zeile 460: Zeile 458:
         }         }
  
-        mcast_src_ip 10.145.43.31+        #mcast_src_ip 10.145.43.31
  
-#        unicast_src_ip 10.145.43.31 +        unicast_src_ip 10.145.43.31 
-#        unicast_peer { +        unicast_peer { 
-#                #10.145.43.31 +                #10.145.43.31 
-#                10.145.43.32 +                10.145.43.32 
-#                10.145.43.33 +                10.145.43.33 
-#        }+        }
 } }
 </file> </file>
Zeile 508: Zeile 506:
         }         }
  
-        mcast_src_ip 10.145.43.32+        #mcast_src_ip 10.145.43.32
  
-#        unicast_src_ip 10.145.43.32 +        unicast_src_ip 10.145.43.32 
-#        unicast_peer { +        unicast_peer { 
-#                10.145.43.31 +                10.145.43.31 
-#                #10.145.43.32 +                #10.145.43.32 
-#                10.145.43.33 +                10.145.43.33 
-#        }+        }
 } }
 </file> </file>
Zeile 556: Zeile 554:
         }         }
  
-        mcast_src_ip 10.145.43.33+        #mcast_src_ip 10.145.43.33
  
-#        unicast_src_ip 10.145.43.33 +        unicast_src_ip 10.145.43.33 
-#        unicast_peer { +        unicast_peer { 
-#                10.145.43.31 +                10.145.43.31 
-#                10.145.43.32 +                10.145.43.32 
-#                #10.145.43.33 +                #10.145.43.33 
-#        }+        }
 } }
 </file> </file>
Zeile 622: Zeile 620:
  
 <code bash Netzwerkzugriffe erlauben> <code bash Netzwerkzugriffe erlauben>
-> echo "listen_addresses = '*'" >> /etc/postgresql/16/main/postgresql.conf+> 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.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    replication     all             ::/0                    scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf
Zeile 727: Zeile 725:
 scope: pgcluster scope: pgcluster
 # name auf Host anpassen # name auf Host anpassen
-name: fra2a-medos04+name: pg-knoten-01
    
 restapi: restapi:
   listen: 0.0.0.0:8008   listen: 0.0.0.0:8008
   # connect_address auf Host anpassen   # connect_address auf Host anpassen
-  connect_address: fra2a-medos04:8008+  connect_address: pg-knoten-01:8008
    
 etcd3: etcd3:
   hosts:   hosts:
-    - fra2a-medos04:2379 +    - pg-knoten-01:2379 
-    - fra2b-medos04:2379 +    - pg-knoten-02:2379 
-    - fra2c-medos04:2379+    - pg-knoten-03:2379
    
 bootstrap: bootstrap:
Zeile 763: Zeile 761:
   listen: 0.0.0.0:5432   listen: 0.0.0.0:5432
   # connect_address auf Host anpassen   # connect_address auf Host anpassen
-  connect_address: fra2a-medos04:5432+  connect_address: pg-knoten-01:5432
   bin_dir:  /usr/lib/postgresql/16/bin   bin_dir:  /usr/lib/postgresql/16/bin
   data_dir: /var/lib/postgresql/16/main   data_dir: /var/lib/postgresql/16/main
Zeile 785: Zeile 783:
 scope: pgcluster scope: pgcluster
 # name auf Host anpassen # name auf Host anpassen
-name: fra2b-medos04+name: pg-knoten-02
    
 restapi: restapi:
   listen: 0.0.0.0:8008   listen: 0.0.0.0:8008
   # connect_address auf Host anpassen   # connect_address auf Host anpassen
-  connect_address: fra2b-medos04:8008+  connect_address: pg-knoten-02:8008
    
 etcd3: etcd3:
   hosts:   hosts:
-    - fra2a-medos04:2379 +    - pg-knoten-01:2379 
-    - fra2b-medos04:2379 +    - pg-knoten-02:2379 
-    - fra2c-medos04:2379+    - pg-knoten-03:2379
    
 bootstrap: bootstrap:
Zeile 821: Zeile 819:
   listen: 0.0.0.0:5432   listen: 0.0.0.0:5432
   # connect_address auf Host anpassen   # connect_address auf Host anpassen
-  connect_address: fra2b-medos04:5432+  connect_address: pg-knoten-02:5432
   bin_dir:  /usr/lib/postgresql/16/bin   bin_dir:  /usr/lib/postgresql/16/bin
   data_dir: /var/lib/postgresql/16/main   data_dir: /var/lib/postgresql/16/main
Zeile 843: Zeile 841:
 scope: pgcluster scope: pgcluster
 # name auf Host anpassen # name auf Host anpassen
-name: fra2c-medos04+name: pg-knoten-03
    
 restapi: restapi:
   listen: 0.0.0.0:8008   listen: 0.0.0.0:8008
   # connect_address auf Host anpassen   # connect_address auf Host anpassen
-  connect_address: fra2c-medos04:8008+  connect_address: pg-knoten-03:8008
    
 etcd3: etcd3:
   hosts:   hosts:
-    - fra2a-medos04:2379 +    - pg-knoten-01:2379 
-    - fra2b-medos04:2379 +    - pg-knoten-02:2379 
-    - fra2c-medos04:2379+    - pg-knoten-03:2379
    
 bootstrap: bootstrap:
Zeile 879: Zeile 877:
   listen: 0.0.0.0:5432   listen: 0.0.0.0:5432
   # connect_address auf Host anpassen   # connect_address auf Host anpassen
-  connect_address: fra2c-medos04:5432+  connect_address: pg-knoten-03:5432
   bin_dir:  /usr/lib/postgresql/16/bin   bin_dir:  /usr/lib/postgresql/16/bin
   data_dir: /var/lib/postgresql/16/main   data_dir: /var/lib/postgresql/16/main
Zeile 917: Zeile 915:
  
 <code text Clusterstatus prüfen> <code text Clusterstatus prüfen>
-> curl http://fra2a-medos04:8008 +> curl http://pg-knoten-01:8008 
-> curl http://fra2b-medos04:8008 +> curl http://pg-knoten-02:8008 
-> curl http://fra2c-medos04:8008+> curl http://pg-knoten-03:8008
 </code> </code>
  
Zeile 1013: Zeile 1011:
     "version": "3.2.2"                                      "version": "3.2.2"                                 
     "scope": "pgcluster"                                    "scope": "pgcluster"                               
-    "name": "fra2a-medos04"                            +    "name": "pg-knoten-01"                            
   }                                                       }                                                    
 } }
  
 > patronictl -c /etc/patroni/config.yml list > patronictl -c /etc/patroni/config.yml list
-+ Cluster: pgcluster (7637152973093863500) ---------+----+-----------+ ++ Cluster: pgcluster (7637152973093863500) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2c-medos04 | Replica | running |    |   unknown | +| pg-knoten-01 | pg-knoten-03 | Replica | running |    |   unknown | 
-+---------------+---------------+---------+---------+----+-----------+++--------------+--------------+---------+---------+----+-----------+
 </code> </code>
  
Zeile 1028: Zeile 1026:
 > patronictl -c /etc/patroni/config.yml failover > patronictl -c /etc/patroni/config.yml failover
 Current cluster topology Current cluster topology
-+ Cluster: pgcluster (7637152973093863500) ---------+----+-----------+ ++ Cluster: pgcluster (7637152973093863500) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2c-medos04 | Replica | running |    |   unknown | +| pg-knoten-01 | pg-knoten-03 | Replica | running |    |   unknown | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-Candidate ['fra2a-medos04'] []: fra2a-medos04+Candidate ['pg-knoten-01'] []: pg-knoten-01
 Are you sure you want to failover cluster pgcluster? [y/N]: y Are you sure you want to failover cluster pgcluster? [y/N]: y
 </code> </code>
Zeile 1049: Zeile 1047:
  
 > patronictl -c /etc/patroni/config.yml list > patronictl -c /etc/patroni/config.yml list
-+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running |  3 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running |  3 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown | 
-| fra2c-medos04 | fra2c-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown | 
-+---------------+---------------+---------+---------+----+-----------+++--------------+--------------+---------+---------+----+-----------+
  
  
 Wenn das so aussieht, dann kann von den Replicas nicht gelesen werden. Wenn das so aussieht, dann kann von den Replicas nicht gelesen werden.
-Um Problem das zu beheben, müssen die Replicas (beide) reinitialisiert werden.+Um das Problem zu beheben, müssen die Replicas (beide) reinitialisiert werden.
  
  
-> patronictl -c /etc/patroni/config.yml reinit pgcluster fra2b-medos04 +> patronictl -c /etc/patroni/config.yml reinit pgcluster pg-knoten-02 
-+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running |  3 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running |  3 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown | 
-| fra2c-medos04 | fra2c-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-Are you sure you want to reinitialize members fra2b-medos04? [y/N]: y +Are you sure you want to reinitialize members pg-knoten-02? [y/N]: y 
-Failed: reinitialize for member fra2b-medos04, status code=503, (bootstrap from leader 'fra2a-medos04' already in progress)+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 Do you want to cancel it and reinitialize anyway? [y/N]: y
-Success: reinitialize for member fra2b-medos04+Success: reinitialize for member pg-knoten-02
  
-> patronictl -c /etc/patroni/config.yml reinit pgcluster fra2c-medos04 +> patronictl -c /etc/patroni/config.yml reinit pgcluster pg-knoten-03 
-+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running |  3 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running |  3 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown | 
-| fra2c-medos04 | fra2c-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-Are you sure you want to reinitialize members fra2c-medos04? [y/N]: y +Are you sure you want to reinitialize members pg-knoten-03? [y/N]: y 
-Failed: reinitialize for member fra2c-medos04, status code=503, (bootstrap from leader 'fra2a-medos04' already in progress)+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]:   Do you want to cancel it and reinitialize anyway? [y/N]:  
  
Zeile 1092: Zeile 1090:
  
 > patronictl -c /etc/patroni/config.yml list > patronictl -c /etc/patroni/config.yml list
-+ Cluster: pgcluster (7637598528541987072) -----------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ 
-| Member        | Host          | Role    | State     | TL | Lag in MB | +| Member       | Host         | Role    | State     | TL | Lag in MB | 
-+---------------+---------------+---------+-----------+----+-----------+ ++--------------+--------------+---------+-----------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running   |  6 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running   |  6 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | streaming |  6 |         0 | +| pg-knoten-02 | pg-knoten-02 | Replica | streaming |  6 |         0 | 
-| fra2c-medos04 | fra2c-medos04 | 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}' > curl -s "http://admin:admin@localhost:8404/stats;csv" | grep -E 'pg_leader|pg_replica' | awk -F',' '{print $2, $18, $56}'
Zeile 1116: Zeile 1114:
 <code bash IST-Zustand> <code bash IST-Zustand>
 > patronictl -c /etc/patroni/config.yml list > patronictl -c /etc/patroni/config.yml list
-+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Replica | running | 13 |       217 | +| pg-knoten-01 | pg-knoten-01 | Replica | running | 13 |       217 | 
-| fra2b-medos04 | fra2b-medos04 | Replica | running | 13 |       217 | +| pg-knoten-02 | pg-knoten-02 | Replica | running | 13 |       217 | 
-| fra2c-medos04 | fra2c-medos04 | Replica | running | 13 |       217 | +| pg-knoten-03 | pg-knoten-03 | Replica | running | 13 |       217 | 
-+---------------+---------------+---------+---------+----+-----------+++--------------+--------------+---------+---------+----+-----------+
 </code> </code>
  
-<code bash reparieren>+<code bash einen zum Leader bestimmen>
 > patronictl -c /etc/patroni/config.yml failover pgcluster > patronictl -c /etc/patroni/config.yml failover pgcluster
-+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Replica | running | 13 |       217 | +| pg-knoten-01 | pg-knoten-01 | Replica | running | 13 |       217 | 
-| fra2b-medos04 | fra2b-medos04 | Replica | running | 13 |       217 | +| pg-knoten-02 | pg-knoten-02 | Replica | running | 13 |       217 | 
-| fra2c-medos04 | fra2c-medos04 | Replica | running | 13 |       217 | +| pg-knoten-03 | pg-knoten-03 | Replica | running | 13 |       217 | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-Candidate ['fra2a-medos04', 'fra2b-medos04', 'fra2c-medos04'] []: fra2a-medos04+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 Are you sure you want to failover cluster pgcluster? [y/N]: y
-2026-05-13 13:48:00.52027 Successfully failed over to "fra2a-medos04" +2026-05-13 13:48:00.52027 Successfully failed over to "pg-knoten-01" 
-+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running | 13 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running | 13 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | running | 13 |       217 | +| pg-knoten-02 | pg-knoten-02 | Replica | running | 13 |       217 | 
-| fra2c-medos04 | fra2c-medos04 | Replica | running | 13 |       217 | +| pg-knoten-03 | pg-knoten-03 | Replica | running | 13 |       217 | 
-+---------------+---------------+---------+---------+----+-----------+++--------------+--------------+---------+---------+----+-----------+
  
 > patronictl -c /etc/patroni/config.yml list > patronictl -c /etc/patroni/config.yml list
-+ Cluster: pgcluster (7637598528541987072) -----------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ 
-| Member        | Host          | Role    | State     | TL | Lag in MB | +| Member       | Host         | Role    | State     | TL | Lag in MB | 
-+---------------+---------------+---------+-----------+----+-----------+ ++--------------+--------------+---------+-----------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running   | 14 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running   | 14 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | streaming | 14 |         0 | +| pg-knoten-02 | pg-knoten-02 | Replica | streaming | 14 |         0 | 
-| fra2c-medos04 | fra2c-medos04 | Replica | streaming | 14 |         0 | +| pg-knoten-03 | pg-knoten-03 | Replica | streaming | 14 |         0 | 
-+---------------+---------------+---------+-----------+----+-----------+++--------------+--------------+---------+-----------+----+-----------+
 </code> </code>
  
Zeile 1343: Zeile 1341:
  repl      | Replication  repl      | Replication
 </code> </code>
- 
-//Wie das beste Vorgehen bei Wartungsarbeiten ist, muß erst noch ermittelt ausgelotet werden.// 
  
 <code bash> <code bash>
Zeile 1387: Zeile 1383:
  
 # EtcD abfragen # EtcD abfragen
-etcdctl --endpoints="http://fra2a-medos04:2379,http://fra2b-medos04:2379,http://fra2c-medos04:2379" endpoint health+etcdctl --endpoints="http://pg-knoten-01:2379,http://pg-knoten-02:2379,http://pg-knoten-03:2379" endpoint health
  
 # Patroni abfragen # Patroni abfragen
Zeile 1589: Zeile 1585:
 ==== Replica reinitialisieren ==== ==== Replica reinitialisieren ====
  
-<code bash fra2a-medos04 reparieren - neu syncen> +<code bash pg-knoten-01 reparieren - neu syncen> 
-> patronictl -c /etc/patroni/config.yml reinit pgcluster fra2b-medos04 +> patronictl -c /etc/patroni/config.yml reinit pgcluster pg-knoten-02 
-+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running |  3 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running |  3 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown | 
-| fra2c-medos04 | fra2c-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-Are you sure you want to reinitialize members fra2b-medos04? [y/N]: y +Are you sure you want to reinitialize members pg-knoten-02? [y/N]: y 
-Failed: reinitialize for member fra2b-medos04, status code=503, (bootstrap from leader 'fra2a-medos04' already in progress)+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 Do you want to cancel it and reinitialize anyway? [y/N]: y
-Success: reinitialize for member fra2b-medos04+Success: reinitialize for member pg-knoten-02
 </code> </code>
  
Zeile 1611: Zeile 1607:
 [~]# patronictl -c /etc/patroni/config.yml failover [~]# patronictl -c /etc/patroni/config.yml failover
 Current cluster topology Current cluster topology
-+ Cluster: pgcluster (7637598528541987072) -----------+----+-----------+ 
-| Member        | Host          | Role    | State     | TL | Lag in MB | 
-+---------------+---------------+---------+-----------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Replica | streaming |  8 |         0 | 
-| fra2b-medos04 | fra2b-medos04 | Leader  | running   |  8 |           | 
-| fra2c-medos04 | fra2c-medos04 | Replica | streaming |  8 |         0 | 
-+---------------+---------------+---------+-----------+----+-----------+ 
-Candidate ['fra2a-medos04', 'fra2c-medos04'] []: fra2a-medos04 
-Are you sure you want to failover cluster pgcluster, demoting current leader fra2b-medos04? [y/N]: y 
-2026-05-12 14:26:55.23394 Successfully failed over to "fra2a-medos04" 
 + Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ + Cluster: pgcluster (7637598528541987072) ---------+----+-----------+
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State     | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+-----------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running |  8 |           | +| pg-knoten-01 | pg-knoten-01 | Replica | streaming |  8 |         0 | 
-| fra2b-medos04 | fra2b-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-02 | pg-knoten-02 | Leader  | running   |  8 |           | 
-| fra2c-medos04 | fra2c-medos04 | Replica | running |  8 |         0 | +| 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] [root@FRA2C-MEDOS04]---------------------------------------------------------------------[0]-[0]-[2026-05-12 14:26:55]
 [~]# patronictl -c /etc/patroni/config.yml list [~]# patronictl -c /etc/patroni/config.yml list
-+ Cluster: pgcluster (7637598528541987072) -----------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ 
-| Member        | Host          | Role    | State     | TL | Lag in MB | +| Member       | Host         | Role    | State     | TL | Lag in MB | 
-+---------------+---------------+---------+-----------+----+-----------+ ++--------------+--------------+---------+-----------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running   |  9 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running   |  9 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | streaming |  9 |         0 | +| pg-knoten-02 | pg-knoten-02 | Replica | streaming |  9 |         0 | 
-| fra2c-medos04 | fra2c-medos04 | Replica | streaming |  9 |         0 | +| pg-knoten-03 | pg-knoten-03 | Replica | streaming |  9 |         0 | 
-+---------------+---------------+---------+-----------+----+-----------+++--------------+--------------+---------+-----------+----+-----------+
 </code> </code>
  
Zeile 1654: Zeile 1650:
 Aber wozu brauche ich HAProxy? KeepaliveD kann doch direkt Patroni prüfen und dann dort, wo der Leader ist, die VIP hochfahren. 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 # patronictl -c /etc/patroni/config.yml list
-+ Cluster: pgcluster (7637598528541987072) ---------+----+-----------+ ++ Cluster: pgcluster (7637598528541987072) -------+----+-----------+ 
-| Member        | Host          | Role    | State   | TL | Lag in MB | +| Member       | Host         | Role    | State   | TL | Lag in MB | 
-+---------------+---------------+---------+---------+----+-----------+ ++--------------+--------------+---------+---------+----+-----------+ 
-| fra2a-medos04 | fra2a-medos04 | Leader  | running |  2 |           | +| pg-knoten-01 | pg-knoten-01 | Leader  | running |  2 |           | 
-| fra2b-medos04 | fra2b-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-02 | pg-knoten-02 | Replica | stopped |    |   unknown | 
-| fra2c-medos04 | fra2c-medos04 | Replica | stopped |    |   unknown | +| pg-knoten-03 | pg-knoten-03 | Replica | stopped |    |   unknown | 
-+---------------+---------------+---------+---------+----+-----------+++--------------+--------------+---------+---------+----+-----------+
  
 HAProxy hat zusätzliche Funktionen, die KeepaliveD nicht bieten kann: HAProxy hat zusätzliche Funktionen, die KeepaliveD nicht bieten kann:
Zeile 1948: Zeile 1944:
     nvme3n1      -      -      0    676    138  14.6M     nvme3n1      -      -      0    676    138  14.6M
 -----------  -----  -----  -----  -----  -----  ----- -----------  -----  -----  -----  -----  -----  -----
 +</code>
 +
 +
 +==== Testdaten einspielen ====
 +
 +<code bash>
 +> 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 '');"
 +</code>
 +
 +<code bash Datensätze zählen>
 +> echo "SELECT COUNT(*) AS Tabellenname FROM tabelle;" | psql -d testdb -U dbuser
 +</code>
 +
 +
 +===== Verbindungen SSL/TLS-Verschlüsselt =====
 +
 +
 +==== Verschlüsselung konfigurieren ====
 +
 +<code bash 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
 +</code>
 +
 +
 +==== Verschlüsselung erzwingen ====
 +
 +<code bash 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
 +</code>
 +
 +
 +==== Verschlüsselung aktivieren ====
 +
 +<code bash kompletten Cluster restarten>
 +> patronictl -c /etc/patroni/config.yml restart pgcluster
 +</code>
 +
 +
 +===== temp_tablespaces =====
 +
 +<code bash 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
 +</code>
 +
 +<code bash 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
 +    ...
 </code> </code>
  
  
/home/http/wiki/data/attic/datenbank/ha-postgresql-cluster.1778707360.txt · Zuletzt geändert: von manfred