PostgreSQL auf dem VPS: Einrichtung, die fünf Einstellungen, Zugriff

PostgreSQL läuft nach apt install sofort und mit Voreinstellungen, die für eine 20 Jahre alte Beispielmaschine gedacht waren. Auf einem VPS lohnen sich genau fünf Änderungen. Alles andere in der Konfigurationsdatei kann bleiben, bis eine Messung etwas anderes sagt.

Installieren und absichern#

apt install postgresql
systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"

Erster Blick: Wohin lauscht der Dienst?

ss -tlnp | grep 5432
# Debian-Paket: 127.0.0.1:5432 — genau richtig.

Datenbank und Benutzer anlegen, mit einem Passwort, das nicht in der Shell-Historie landet:

sudo -u postgres psql

CREATE DATABASE meineapp;
CREATE USER app WITH PASSWORD :'pw';   -- Passwort per \set vorher setzen
GRANT ALL PRIVILEGES ON DATABASE meineapp TO app;
\c meineapp
GRANT ALL ON SCHEMA public TO app;     -- seit PG 15 nötig, sonst schreibt niemand

Zugriff regeln#

pg_hba.conf entscheidet, wer sich von wo womit anmelden darf, die Datei wird von oben nach unten gelesen, die erste passende Zeile gewinnt.

# /etc/postgresql/*/main/pg_hba.conf
local   all   postgres              peer
local   meineapp   app              scram-sha-256
host    meineapp   app  127.0.0.1/32  scram-sha-256
# Kein "host all all 0.0.0.0/0" — nie.

Muss die Datenbank von einem anderen Host erreichbar sein, gehört dazwischen ein Tunnel oder ein privates Netz, nicht der offene Port. Für einen zweiten Server im selben Overlay ist WireGuard der schlichteste Weg; der Aufbau steht in FritzBox über WireGuard an einen VPS und funktioniert zwischen zwei Servern genauso.

Die fünf Einstellungen#

Ab PostgreSQL 9.4 lassen sich Parameter direkt in der Sitzung setzen; sie landen in postgresql.auto.conf und überleben Updates der Hauptdatei.

-- Beispielwerte für eine Maschine mit 8 GB RAM, auf der auch die Anwendung läuft
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET effective_cache_size = '4GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

Was die fünf tun:

1. shared_buffers, der eigene Zwischenspeicher. Grobe Regel: ein Viertel des RAM, wenn die Maschine der Datenbank gehört; weniger, wenn Anwendung und Webserver daneben laufen.
2. effective_cache_size, kein Speicher, den PostgreSQL belegt, sondern eine Schätzung, wie viel das Betriebssystem zusätzlich puffert. Sie beeinflusst die Wahl des Abfrageplans. Zu niedrig gesetzt heißt: unnötige Tabellendurchläufe statt Index.
3. work_mem, Speicher pro Sortiervorgang, nicht pro Verbindung und schon gar nicht insgesamt. Eine Abfrage kann ihn mehrfach belegen. Hier liegt der klassische Fehler, siehe Kasten.
4. maintenance_work_mem, für VACUUM und Indexaufbau. Darf großzügig sein, weil selten mehrere gleichzeitig laufen.
5. random_page_cost, sagt dem Planer, wie teuer wahlfreier Zugriff ist. Der Standard stammt aus der Zeit rotierender Platten; auf NVMe ist ein Wert nahe 1 realistischer, und der Planer wählt dann eher einen Index.

Verbindungen: der Engpass, den niemand erwartet#

Jede Verbindung ist ein eigener Prozess. Hunderte davon kosten Speicher und Umschaltzeit, auch wenn sie nichts tun. Auf einem VPS ist die Antwort deshalb nicht „max_connections hochsetzen“, sondern ein Verbindungspool:

apt install pgbouncer
# Anwendung verbindet sich zu PgBouncer, PgBouncer hält wenige echte Verbindungen offen

Als Orientierung: Ein niedriger zweistelliger Wert für max_connections plus Pool trägt mehr Anwendungslast als ein dreistelliger Wert ohne. Und er scheitert vorhersehbar statt plötzlich.

Nachsehen, was langsam ist#

-- Erweiterung einmalig aktivieren (Neustart nötig, wenn shared_preload_libraries gesetzt wird)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Die teuersten Abfragen nach Gesamtzeit
SELECT round(total_exec_time::numeric,0) AS ms, calls, left(query, 90)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- Und für eine einzelne Abfrage: was macht der Planer wirklich?
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Steht dort Seq Scan auf einer großen Tabelle, fehlt in der Regel ein Index und das ist auf kleinen Servern der häufigste Unterschied zwischen „träge“ und „sofort“. Deutlich häufiger jedenfalls als alles, was man an Parametern drehen kann.

Wartung, die von allein läuft#

autovacuum ist standardmäßig an und soll es bleiben. Es räumt gelöschte Zeilenversionen ab; ohne das wächst die Datenbank und wird langsamer. Nachsehen, ob es hinterherkommt:

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Viele tote Zeilen bei alter oder fehlender letzter Ausführung heißen: Die Tabelle wird zu schnell verändert für die Standardeinstellung. Dann wird sie pro Tabelle nachgeschärft, nicht global.

Sicherungen gehören dazu, aber in einen eigenen Beitrag: Datenbank sichern und wirklich zurückspielen.

Häufige Fragen#

Im Container oder direkt auf dem System?#

Beides ist vertretbar. Im Container gilt: Das Datenverzeichnis muss ein benanntes Volume sein, sonst ist die Datenbank beim nächsten docker compose down -v weg. Die Grundregeln stehen in Docker Compose produktiv.

Wie viel RAM braucht PostgreSQL?#

Es läuft mit sehr wenig. Schnell wird es, wenn der häufig gelesene Teil der Daten in den Speicher passt, deshalb ist die Datenbankgröße die wichtigere Zahl als eine feste RAM-Angabe.

Muss ich VACUUM FULL laufen lassen?#

Im Normalbetrieb nein, und es sperrt die Tabelle vollständig. Nur nach dem Löschen sehr großer Datenmengen, und dann in einem Wartungsfenster.

Wie aktualisiere ich auf eine neue Hauptversion?#

Mit pg_upgradecluster (Debian/Ubuntu) und vorher einer geprüften Sicherung. Hauptversionen ändern das interne Format; ein einfaches apt upgrade reicht dafür nicht.

Reicht ein Passwort als Schutz?#

Wenn nur 127.0.0.1 lauscht, ja. Sobald der Port von außen erreichbar wäre, nein, dann braucht es TLS, Netzbeschränkung und einen Grund, warum das überhaupt so ist.

Kurz gesagt#

• Nach der Installation zuerst ss -tlnp | grep 5432, Bindung prüfen.
• Ab PG 15 zusätzlich GRANT ALL ON SCHEMA public, sonst schreibt die Anwendung nicht.
• Fünf Parameter genügen; work_mem ist pro Sortierung, nicht pro Server.
• Viele Verbindungen löst ein Pool, nicht ein höheres max_connections.
• Langsam liegt meist an fehlenden Indizes, pg_stat_statements zeigt, an welchen.