Nicolay Braetter
c31d596b78
- Proxmox Host pve-braetter (Hardware, Storage, Bridges) - 14x LXC Container (CT 100-303) vollständig dokumentiert - 8x VMs (103-201) vollständig dokumentiert - Netzwerk-Übersicht mit komplettem IP-Belegungsplan - Dienste-Übersicht aller Web-Interfaces, DBs, Monitoring - PostgreSQL 16 HA Cluster (Patroni/etcd) detailliert
151 lines
3.8 KiB
Markdown
151 lines
3.8 KiB
Markdown
# CT 300–302 — PostgreSQL 16 HA Cluster + CT 303 pgAdmin
|
||
|
||
## Cluster-Übersicht
|
||
|
||
| VMID | Hostname | IP | Rolle | Status |
|
||
|------|----------|----|-------|--------|
|
||
| 300 | sql1 | 192.168.0.220 | Leader (Primary) | running |
|
||
| 301 | sql2 | 192.168.0.221 | Replica (Streaming) | running |
|
||
| 302 | sql3 | 192.168.0.222 | Replica (Streaming) | running |
|
||
| 303 | pgadmin | 192.168.0.223 | pgAdmin4 Web | running |
|
||
|
||
---
|
||
|
||
## CT 300 — sql1 (Leader)
|
||
|
||
| Eigenschaft | Wert |
|
||
|-------------|------|
|
||
| VMID | 300 |
|
||
| IP | 192.168.0.220/24 |
|
||
| OS | Ubuntu 24.04 LTS |
|
||
| CPU | 2 vCores |
|
||
| RAM | 2048 MB |
|
||
| Disk | osdisk:300/vm-300-disk-0.raw, 20 GB |
|
||
| Autostart | nein |
|
||
|
||
## CT 301 — sql2 (Replica)
|
||
|
||
| Eigenschaft | Wert |
|
||
|-------------|------|
|
||
| VMID | 301 |
|
||
| IP | 192.168.0.221/24 |
|
||
| OS | Ubuntu 24.04 LTS |
|
||
| CPU | 2 vCores |
|
||
| RAM | 2048 MB |
|
||
| Disk | osdisk:301/vm-301-disk-0.raw, 20 GB |
|
||
| Autostart | nein |
|
||
|
||
## CT 302 — sql3 (Replica)
|
||
|
||
| Eigenschaft | Wert |
|
||
|-------------|------|
|
||
| VMID | 302 |
|
||
| IP | 192.168.0.222/24 |
|
||
| OS | Ubuntu 24.04 LTS |
|
||
| CPU | 2 vCores |
|
||
| RAM | 2048 MB |
|
||
| Disk | osdisk:302/vm-302-disk-0.raw, 20 GB |
|
||
| Autostart | nein |
|
||
|
||
---
|
||
|
||
## Dienste (alle DB-Nodes)
|
||
|
||
| Dienst | Port | Beschreibung |
|
||
|--------|------|-------------|
|
||
| postgresql 16 | 5432 | Datenbank |
|
||
| patroni | 8008 | HA-Manager REST API |
|
||
| etcd | 2379 (client), 2380 (peer) | Distributed Key-Value Store (DCS) |
|
||
| apache2 | 80 | Nur sql1: pgAdmin4 Webserver |
|
||
|
||
## Patroni Konfiguration
|
||
|
||
- **Scope:** pg-cluster
|
||
- **Namespace:** /db/
|
||
- **Datenverzeichnis:** `/var/lib/postgresql/16/patroni`
|
||
- **Config:** `/etc/patroni/patroni.yml`
|
||
- **Systemd:** `patroni.service`
|
||
|
||
## etcd Konfiguration
|
||
|
||
- **Cluster-Token:** pg-etcd-cluster
|
||
- **Config:** `/etc/default/etcd`
|
||
- **Datenverzeichnis:** `/var/lib/etcd/default`
|
||
|
||
## Datenbanken & Benutzer
|
||
|
||
| Datenbank | Eigentümer |
|
||
|-----------|-----------|
|
||
| nicolay | nicolay |
|
||
| testdb | nicolay |
|
||
| postgres | postgres |
|
||
|
||
| Benutzer | Passwort | Rechte |
|
||
|----------|----------|--------|
|
||
| nicolay | N17b011975 | Login |
|
||
| postgres | postgres_pass | Superuser |
|
||
| replicator | replicator_pass | Replikation |
|
||
| admin | admin | createdb, createrole |
|
||
|
||
## Zugriff
|
||
|
||
```bash
|
||
# PostgreSQL (immer Leader)
|
||
psql -h 192.168.0.220 -U nicolay -d nicolay
|
||
|
||
# Cluster-Status
|
||
pct exec 300 -- /usr/local/bin/patronictl -c /etc/patroni/patroni.yml list
|
||
|
||
# Patroni REST API
|
||
curl http://192.168.0.220:8008/leader
|
||
curl http://192.168.0.221:8008/replica
|
||
|
||
# etcd
|
||
etcdctl --endpoints=http://192.168.0.220:2379 endpoint health
|
||
```
|
||
|
||
## Manueller Failover
|
||
|
||
```bash
|
||
pct exec 300 -- /usr/local/bin/patronictl -c /etc/patroni/patroni.yml failover pg-cluster
|
||
```
|
||
|
||
## Verhalten bei Ausfall
|
||
|
||
| Szenario | Verhalten |
|
||
|----------|-----------|
|
||
| sql1 fällt aus | Patroni wählt sql2 oder sql3 als neuen Leader (30–60s) |
|
||
| sql2 oder sql3 fällt aus | Kein Ausfall, Leader läuft weiter |
|
||
| 2 Nodes fallen aus | Kein Quorum → Cluster read-only |
|
||
|
||
---
|
||
|
||
## CT 303 — pgadmin
|
||
|
||
| Eigenschaft | Wert |
|
||
|-------------|------|
|
||
| VMID | 303 |
|
||
| Hostname | pgadmin |
|
||
| IP | 192.168.0.223/24 |
|
||
| OS | Ubuntu 24.04 LTS |
|
||
| CPU | 2 vCores |
|
||
| RAM | 1024 MB |
|
||
| Disk | osdisk:303/vm-303-disk-0.raw, 10 GB |
|
||
| Autostart | nein |
|
||
|
||
### pgAdmin4 Zugriff
|
||
|
||
- **URL:** http://192.168.0.223/pgadmin4/
|
||
- **Login:** nicolay.braetter@googlemail.com / N17b011975
|
||
- **Vorkonfigurierte Server:** sql1, sql2, sql3 (alle als "pg-cluster" Gruppe)
|
||
|
||
### Hinweis Redundanz
|
||
|
||
pgAdmin läuft auf einem **separaten** LXC, unabhängig von den Datenbank-Nodes. Bei Ausfall von sql1 bleibt pgAdmin erreichbar. Die Server-Verbindungen in pgAdmin zeigen direkt auf die jeweiligen Node-IPs.
|
||
|
||
## Install-Script
|
||
|
||
Vollständiges Installations-Script für den kompletten Cluster:
|
||
`/home/claude/postgres-ha-install.sh` auf pve-braetter
|
||
Gitea: (kein separates Repo — im aqualog-Repo enthalten)
|