Differences

This shows you the differences between two versions of the page.

--- services:postgresql [2026/02/06 23:46] – external edit 127.0.0.1
+++ services:postgresql [2026/02/08 08:34] (current) – created - external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== PostgreSQL ======
-Shared database server for n8n and Vaultwarden.
+Shared Database für alle Services.
-===== Access =====
+===== Details =====
-^ Item ^ Value ^
+^ Key ^ Value ^
-| Host | 10.100.161.110 |
-| Port | 5432 |
 | Container | CT 110 |
+| IP | 10.100.161.110 |
+| Port | 5432 |
+| RAM | 1 GB |
+| Version | 15 |
-===== Databases =====
+===== Datenbanken =====
 ^ Database ^ User ^ Service ^
-| n8n | n8n | [[services:n8n|n8n]] |
+| n8n | n8n | n8n Workflows |
-| vaultwarden | vaultwarden | [[services:vaultwarden|Vaultwarden]] |
+| vaultwarden | vaultwarden | Vaultwarden |
+| forgejo | forgejo | Forgejo Git |
-Credentials in Vaultwarden (Network Automation collection).
+===== Zugriff =====
-===== Installation =====
 <code bash>
-# Container erstellen (Proxmox)
+# Auf dem Container
-pct create 110 local:vztmpl/debian-12-standard_12.7-1_amd64.tar.zst \
+su - postgres
-  --hostname postgres \
+psql
-  --memory 1024 --swap 512 \
-  --cores 2 \
-  --net0 name=eth0,bridge=vmbr0,tag=160,ip=10.100.161.110/23,gw=10.100.161.254 \
-  --features nesting=1 \
-  --unprivileged 1 \
-  --start 1
-# PostgreSQL installieren
+# Remote (von anderem CT)
-pct exec 110 -- bash -c '
+psql -h 10.100.161.110 -U <user> -d <database>
-apt update && apt install -y postgresql postgresql-contrib
+</code>
-# Auf alle IPs hören
+===== Befehle =====
-sed -i "s/#listen_addresses.*/listen_addresses = '\''*'\''/" /etc/postgresql/*/main/postgresql.conf
-# Remote-Zugriff erlauben
+<code sql>
-echo "host all all 10.100.160.0/23 scram-sha-256" >> /etc/postgresql/*/main/pg_hba.conf
+-- Datenbanken anzeigen
+\l
-systemctl restart postgresql
+-- User anzeigen
-'
+\du
-# Admin-User erstellen
+-- Zu DB wechseln
-pct exec 110 -- su - postgres -c "psql -c \"ALTER USER postgres PASSWORD 'SECURE_PASSWORD';\""
+\c <database>
-</code>
-==== Datenbank für Service anlegen ====
+-- Tabellen anzeigen
+\dt
-<code bash>
-pct exec 110 -- su - postgres -c "psql" << SQL
-CREATE USER myservice WITH PASSWORD 'SERVICE_PASSWORD';
-CREATE DATABASE myservice OWNER myservice;
-GRANT ALL PRIVILEGES ON DATABASE myservice TO myservice;
-SQL
 </code>
@@ Line 61: / Line 49: @@
 <code bash>
-# Alle Datenbanken sichern
+# Alle Datenbanken
-pct exec 110 -- su - postgres -c "pg_dumpall" > /backup/postgres_$(date +%Y%m%d).sql
+pg_dumpall -U postgres > backup.sql
 # Einzelne DB
-pct exec 110 -- su - postgres -c "pg_dump n8n" > /backup/n8n_$(date +%Y%m%d).sql
+pg_dump -U postgres <database> > database.sql
 </code>
-===== Management =====
+Backup-Location: /var/lib/pve/local/dump/
+===== Ansible =====
 <code bash>
-# Status
+ansible-playbook site.yml --limit postgres
-pct exec 110 -- systemctl status postgresql
-# Logs
-pct exec 110 -- journalctl -u postgresql -f
-# psql Shell
-pct exec 110 -- su - postgres -c "psql"
-# Datenbanken auflisten
-pct exec 110 -- su - postgres -c "psql -c '\l'"
 </code>
-===== Related =====
+===== Credentials =====
-  * [[services:n8n|n8n]] - Workflow automation
+Alle Passwörter in Vaultwarden:
-  * [[services:vaultwarden|Vaultwarden]] - Password manager
+  * **Organisation:** mxmlabs
-  * [[infrastructure:containers|Containers]] - CT 110 details
+  * **Collection:** Network Automation