PostgreSQL ist eine der leistungsstärksten Open-Source-Datenbanken. Sie eignet sich für komplexe Anwendungen und große Datenmengen.

Warum PostgreSQL?

  • ACID-konform: Zuverlässige Transaktionen
  • Erweiterbar: Eigene Datentypen, Funktionen, Operatoren
  • JSON-Unterstützung: Ideal für moderne Anwendungen
  • GIS-Fähig: Mit PostGIS für Geodaten
  • Kostenlos: Open Source ohne Einschränkungen

Installation

Ubuntu/Debian

apt update
apt install postgresql postgresql-contrib

CentOS/AlmaLinux

dnf install postgresql-server postgresql-contrib
postgresql-setup --initdb

Neuere PostgreSQL-Version

Für die neueste Version das offizielle Repository nutzen:

# Ubuntu/Debian
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt update
apt install postgresql-16

Dienst starten

systemctl start postgresql
systemctl enable postgresql
systemctl status postgresql

Erste Schritte

Als postgres-Benutzer arbeiten

PostgreSQL erstellt einen System-Benutzer postgres:

sudo -i -u postgres
psql

Oder direkt:

sudo -u postgres psql

PostgreSQL-Shell (psql)

-- Version anzeigen
SELECT version();

-- Datenbanken auflisten
\l

-- Benutzer auflisten
\du

-- Beenden
\q

Benutzer erstellen

In psql

-- Benutzer mit Passwort erstellen
CREATE USER meinuser WITH PASSWORD 'sicheres_passwort';

-- Mit Rechten
CREATE USER meinuser WITH PASSWORD 'sicheres_passwort' CREATEDB;

-- Superuser (Vorsicht!)
CREATE USER admin WITH PASSWORD 'passwort' SUPERUSER;

Per Kommandozeile

sudo -u postgres createuser --interactive meinuser

Datenbank erstellen

In psql

-- Einfache Datenbank
CREATE DATABASE meine_db;

-- Mit Besitzer
CREATE DATABASE meine_db OWNER meinuser;

-- Mit Encoding
CREATE DATABASE meine_db
    OWNER meinuser
    ENCODING 'UTF8'
    LC_COLLATE 'de_DE.UTF-8'
    LC_CTYPE 'de_DE.UTF-8';

Per Kommandozeile

sudo -u postgres createdb -O meinuser meine_db

Berechtigungen

-- Alle Rechte auf Datenbank
GRANT ALL PRIVILEGES ON DATABASE meine_db TO meinuser;

-- Alle Rechte auf Schema
GRANT ALL ON SCHEMA public TO meinuser;

-- Auf alle Tabellen
GRANT ALL ON ALL TABLES IN SCHEMA public TO meinuser;

-- Nur Lesen
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Authentifizierung konfigurieren

Die Datei pg_hba.conf steuert, wer sich wie verbinden darf:

# Pfad finden
sudo -u postgres psql -c "SHOW hba_file;"
# Meist: /etc/postgresql/16/main/pg_hba.conf

Beispielkonfiguration

# TYPE  DATABASE    USER        ADDRESS         METHOD
local   all         postgres                    peer
local   all         all                         md5
host    all         all         127.0.0.1/32    md5
host    all         all         ::1/128         md5
host    all         all         192.168.1.0/24  md5

Methoden:

  • peer: System-Benutzer = DB-Benutzer
  • md5: Passwort-Authentifizierung
  • trust: Ohne Passwort (nur für Tests!)

Nach Änderungen:

systemctl reload postgresql

Remote-Zugriff aktivieren

postgresql.conf anpassen

sudo nano /etc/postgresql/16/main/postgresql.conf
listen_addresses = '*'    # oder spezifische IPs
port = 5432

pg_hba.conf für Remote

# Remote-Zugriff von bestimmtem Netz
host    all    all    192.168.1.0/24    md5

# Einzelne IP
host    meine_db    meinuser    203.0.113.50/32    md5

Firewall

ufw allow 5432/tcp
# oder eingeschränkt
ufw allow from 192.168.1.0/24 to any port 5432

Neu starten

systemctl restart postgresql

Mit Datenbank arbeiten

Verbinden

# Lokal
psql -U meinuser -d meine_db

# Remote
psql -h server.example.com -U meinuser -d meine_db

Wichtige psql-Befehle

| Befehl | Beschreibung | |--------|--------------| | \l | Datenbanken auflisten | | \c dbname | Zu Datenbank wechseln | | \dt | Tabellen anzeigen | | \d tablename | Tabellenstruktur | | \du | Benutzer auflisten | | \dn | Schemas auflisten | | \i datei.sql | SQL-Datei ausführen | | \q | Beenden | | \? | Hilfe |

SQL-Beispiele

-- Tabelle erstellen
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Daten einfügen
INSERT INTO users (name, email) VALUES ('Max', 'max@example.com');

-- Abfragen
SELECT * FROM users;
SELECT name, email FROM users WHERE created_at > '2024-01-01';

-- Aktualisieren
UPDATE users SET name = 'Maximilian' WHERE id = 1;

-- Löschen
DELETE FROM users WHERE id = 1;

Backup und Restore

Einzelne Datenbank (pg_dump)

# Backup erstellen
pg_dump -U postgres meine_db > backup.sql

# Mit Komprimierung
pg_dump -U postgres meine_db | gzip > backup.sql.gz

# Custom-Format (empfohlen)
pg_dump -U postgres -Fc meine_db > backup.dump

Wiederherstellen

# SQL-Format
psql -U postgres meine_db < backup.sql

# Komprimiert
gunzip < backup.sql.gz | psql -U postgres meine_db

# Custom-Format
pg_restore -U postgres -d meine_db backup.dump

Alle Datenbanken (pg_dumpall)

# Backup
sudo -u postgres pg_dumpall > all_databases.sql

# Wiederherstellen
sudo -u postgres psql < all_databases.sql

Performance-Tuning

postgresql.conf optimieren

# Arbeitsspeicher (25% des RAMs)
shared_buffers = 2GB

# Für Abfragen (RAM / Verbindungen / 2)
work_mem = 64MB

# Für Wartung
maintenance_work_mem = 512MB

# Checkpoint-Konfiguration
checkpoint_completion_target = 0.9
wal_buffers = 64MB

# Für SSDs
random_page_cost = 1.1
effective_io_concurrency = 200

# Statistiken
effective_cache_size = 6GB  # 75% des RAMs

Nach Änderungen

systemctl restart postgresql

Monitoring

Aktive Verbindungen

SELECT * FROM pg_stat_activity;

Datenbankgröße

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database;

Tabellengröße

SELECT tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Langsame Queries loggen

In postgresql.conf:

log_min_duration_statement = 1000  # Queries über 1 Sekunde

Häufige Befehle

| Aktion | Befehl | |--------|--------| | Als postgres anmelden | sudo -u postgres psql | | Benutzer erstellen | CREATE USER name WITH PASSWORD 'pw'; | | Datenbank erstellen | CREATE DATABASE db OWNER user; | | Rechte vergeben | GRANT ALL ON DATABASE db TO user; | | Backup | pg_dump -U user db > backup.sql | | Restore | psql -U user db < backup.sql | | Reload Config | systemctl reload postgresql |

Fazit

PostgreSQL ist eine exzellente Wahl für anspruchsvolle Anwendungen. Die Installation ist einfach, die Konfiguration flexibel. Vergessen Sie nicht regelmäßige Backups und optimieren Sie die Konfiguration für Ihre Hardware.