PostgreSQL installieren und grundlegend einstellen

01Was ist PostgreSQL und warum wird es gewählt?

PostgreSQL ist ein quelloffenes, kostenloses relationales Datenbanksystem mit einer Geschichte von fast vierzig Jahren. Dank Transaktionssicherheit (ACID), leistungsfähigen Datentypen, JSONB-Unterstützung und fortgeschrittenen Indexoptionen wird es in einem breiten Spektrum eingesetzt, von kleinen Projekten bis zu Anwendungen mit hohem Traffic.

Bei Daten, bei denen Konsistenz entscheidend ist, etwa Reservierungen, Lagerbestände, Bestellungen oder Inserate, ist PostgreSQL eine solide Wahl. In Systemen wie einer Hotelsoftware oder einer E-Commerce-Infrastruktur, in denen mehrere Personen gleichzeitig auf denselben Datensatz zugreifen, ist die Transaktionssicherheit deshalb besonders wichtig. Wer lokal schnell PHP und eine Datenbank ausprobieren möchte, findet in der XAMPP-Installation einen praktischen Einstieg. Für ähnliche Aufgaben wird häufig auch MySQL eingesetzt; wenn Sie beide Systeme vergleichen möchten, lesen Sie die Anleitung zur MySQL-Installation.

In diesem Beitrag behandeln wir alles, was Sie am ersten Tag nach der Installation brauchen: Rollen und Datenbanken anlegen, Einstellungen für Remote-Verbindungen, Backups und die häufigsten Fehlermeldungen.

02Voraussetzungen vor der Installation

Wenn Sie vor der Installation ein paar Entscheidungen klären, vermeiden Sie spätere Probleme. Legen Sie vor allem für einen Produktivserver von Anfang an fest, welche Hauptversion Sie einsetzen; ein Wechsel zwischen Hauptversionen erfordert Sicherung und Wiederherstellung oder pg_upgrade.

Auch die Wahl der Methode ist wichtig. Auf dem Entwicklungsrechner ist Docker der einfachste Weg, um verschiedene Versionen parallel und ohne Konflikte zu betreiben. Auf einem Produktivserver bietet ein über den Paketmanager des Betriebssystems installierter Dienst die besser vorhersehbare Struktur, wenn es um Sicherheitsupdates, Log-Überwachung und den automatischen Start geht.

• Ein Benutzer mit Administratorrechten (Windows) oder sudo-Rechten (macOS, Linux)
• Der Standardport 5432 darf nicht von einer anderen Anwendung belegt sein
• Aktuelle Informationen zur unterstützten Hauptversion auf der offiziellen Website: postgresql.org
• Ein starkes Passwort für den Superuser, das Sie sicher abgelegt haben
• Eine funktionierende Docker-Installation, falls Sie mit Docker arbeiten

03Installation je nach Betriebssystem

Jede der folgenden Methoden installiert aus offiziellen oder offiziell empfohlenen Quellen. Ersetzen Sie in den Befehlen den Versions-Platzhalter durch die von Ihnen gewählte Hauptversion.

Windows

Laden Sie auf der Windows-Seite von postgresql.org das von EDB bereitgestellte Installationsprogramm herunter. Der Assistent installiert Server, pgAdmin, Kommandozeilenwerkzeuge und Stack Builder gemeinsam und fragt nach dem Passwort des Benutzers postgres, dem Port und der Spracheinstellung.

Damit Sie den Befehl psql nach der Installation in jedem Terminal nutzen können, fügen Sie den bin-Ordner zur PATH-Variablen hinzu. Die Verknüpfung SQL Shell (psql) im Startmenü erfüllt denselben Zweck.

# Der Ordnername hängt von der installierten Hauptversion ab
$env:Path += ";C:\Program Files\PostgreSQL\<version>\bin"
psql --version
psql -U postgres -h localhost

macOS (Homebrew)

Unter Homebrew wird PostgreSQL in versionierten Paketen angeboten. Listen Sie zuerst die vorhandenen Pakete auf, installieren Sie dann die gewünschte Hauptversion und starten Sie sie als Dienst. Wenn Sie eine grafische Oberfläche bevorzugen, ist Postgres.app eine verbreitete Alternative.

brew search postgresql
brew install postgresql@<version>
brew services start postgresql@<version>
# Versionierte Pakete werden eventuell nicht automatisch zum PATH hinzugefügt;
# führen Sie die Zeile echo 'export PATH=...' aus der Installationsausgabe aus
psql --version
psql postgres

Ubuntu und Debian

Das Paket aus dem Repository der Distribution genügt für die meisten Projekte. Falls Sie eine neuere Hauptversion benötigen, können Sie das auf postgresql.org beschriebene PGDG-Repository hinzufügen und von dort installieren.

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

Docker

Für die Entwicklungsumgebung ist das offizielle postgres-Image der sauberste Weg: Sie starten und stoppen die Datenbank, ohne einen dauerhaften Dienst auf Ihrem Rechner zu installieren. Damit keine Daten verloren gehen, binden Sie unbedingt ein benanntes Volume ein.

• Bei den offiziellen Images ab Version 18 wird das Volume unter /var/lib/postgresql eingebunden.
• Bei Images bis Version 17 binden Sie das Volume unter /var/lib/postgresql/data ein; prüfen Sie dazu den Hinweis auf der Docker-Hub-Seite des Images.

docker run -d --name pg \
  -e POSTGRES_PASSWORD=GucluBirParola \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  postgres:<version>

docker exec -it pg psql -U postgres

04Benutzer und Datenbank anlegen

Die mit der Installation gelieferte Rolle postgres ist ein Superuser; Ihre Anwendung mit dieser Rolle zu verbinden, ist aus Sicherheitsgründen falsch. Legen Sie für jedes Projekt eine eigene Rolle an, die nur auf der zugehörigen Datenbank berechtigt ist.

In PostgreSQL sind Benutzer und Rolle dasselbe Konzept: Eine Rolle mit LOGIN-Recht verhält sich wie ein Benutzer. Die Rolle, die Sie zum Besitzer der Datenbank machen, kann im public-Schema dieser Datenbank Tabellen erstellen.

CREATE ROLE uygulama WITH LOGIN PASSWORD 'BurayaGucluParola';
CREATE DATABASE magaza OWNER uygulama ENCODING 'UTF8';

-- Benutzer für schreibgeschützte Berichte
CREATE ROLE rapor WITH LOGIN PASSWORD 'BaskaGucluParola';
GRANT CONNECT ON DATABASE magaza TO rapor;
\c magaza
GRANT USAGE ON SCHEMA public TO rapor;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rapor;
ALTER DEFAULT PRIVILEGES FOR ROLE uygulama IN SCHEMA public
  GRANT SELECT ON TABLES TO rapor;

05Die wichtigsten psql-Befehle

psql ist der Kommandozeilen-Client von PostgreSQL. Meta-Befehle, die mit einem Backslash beginnen, senden kein SQL an den Server, sondern werden vom Client selbst interpretiert und beschleunigen die Navigation erheblich.

psql -h localhost -U uygulama -d magaza
# Per Verbindungs-String
psql "postgresql://uygulama@localhost:5432/magaza"

06Verwaltung mit pgAdmin über die grafische Oberfläche

pgAdmin ist das offizielle, kostenlose Verwaltungswerkzeug für PostgreSQL. Es wird mit dem Windows-Installationsprogramm mitgeliefert; unter macOS und Linux installieren Sie es separat über pgadmin.org oder betreiben es als Docker-Image.

Definieren Sie beim ersten Start über Register > Server einen neuen Server: Tragen Sie auf der Registerkarte Connection als Host localhost, Port 5432 sowie Benutzer und Passwort ein. Nach der Verbindung können Sie Tabellen durchsuchen, mit dem Query Tool Abfragen ausführen sowie Backup und Wiederherstellung über das Menü durchführen.

Wenn Sie sich mit einem entfernten Server verbinden, ist es sicherer, die Option SSH-Tunnel von pgAdmin zu nutzen, statt den Datenbankport ins Internet zu öffnen.

07Remote-Verbindung und Grundeinstellungen

PostgreSQL lauscht standardmäßig nur auf lokale Verbindungen. Da der Speicherort der Konfigurationsdateien je nach Betriebssystem und Version variiert, ermitteln Sie ihn zuerst in psql.

Der Wert listen_addresses in der Datei postgresql.conf legt die Adressen fest, auf denen gelauscht wird, pg_hba.conf bestimmt, welcher Client mit welcher Methode authentifiziert wird. Verwenden Sie bei passwortgeschützten Verbindungen die Methode scram-sha-256 und halten Sie den erlaubten IP-Bereich so eng wie möglich.

• shared_buffers: Die offizielle Dokumentation empfiehlt für den Anfang auf einem dedizierten Server etwa ein Viertel des Arbeitsspeichers.
• max_connections: Setzen Sie den Wert nicht hoch an, sondern nutzen Sie anwendungsseitig einen Connection-Pool.
• Um langsame Abfragen zu finden, aktivieren Sie die Einstellung log_min_duration_statement und analysieren Sie sie mit EXPLAIN ANALYZE.

SHOW config_file;
SHOW hba_file;
SHOW data_directory;

# TYPE  DATABASE  USER       ADDRESS          METHOD
host    magaza    uygulama   203.0.113.10/32  scram-sha-256

# Für Änderungen an pg_hba.conf genügt ein Reload
sudo systemctl reload postgresql
# Änderungen an listen_addresses erfordern einen Neustart
sudo systemctl restart postgresql

08Backup und häufige Fehler

Planen Sie direkt nach der Installation ein automatisches Backup. pg_dump erstellt eine konsistente Kopie einer einzelnen Datenbank; ein Backup im benutzerdefinierten Format lässt sich mit pg_restore selektiv wiederherstellen. Bewahren Sie das Backup nicht auf demselben Server, sondern an einem anderen Ort auf.

Die Datenbankperformance wirkt sich direkt auf die Gesamtgeschwindigkeit der Anwendung aus. Ergebnisse erzielen Sie, wenn Abfragezeiten, Indizes und Serverantwort gemeinsam betrachtet werden; diesen ganzheitlichen Ansatz setzen wir in unserer Arbeit zu Geschwindigkeits- und Performance-Management um.

pg_dump -h localhost -U uygulama -F c -f magaza.dump magaza
pg_restore -h localhost -U uygulama -d magaza --clean magaza.dump