PostgreSQL ist eine leistungsstarke Open-Source-Datenbank mit erweiterten Funktionen wie JSON-Support, Volltextsuche und räumlichen Daten. Dieser Artikel zeigt die Grundlagen für Einsteiger.

Warum PostgreSQL?

Vorteile

- ACID-konform (Transaktionssicher)
- Erweiterte Datentypen (JSON, Arrays, UUID)
- Volltextsuche eingebaut
- Räumliche Daten (PostGIS)
- Erweiterbar (Extensions)
- Aktive Community
- Kostenlos und Open Source

PostgreSQL vs. MySQL

| Feature | PostgreSQL | MySQL | |---------|------------|-------| | SQL-Konformität | Sehr hoch | Mittel | | JSON-Support | Nativ (JSONB) | JSON-Typ | | Volltextsuche | Eingebaut | Eingebaut | | Replikation | Streaming | Master-Slave | | Lizenz | PostgreSQL (permissiv) | GPL | | Performance | Komplex-Queries | Einfache Queries |

Installation

Debian/Ubuntu

# PostgreSQL installieren
apt update
apt install postgresql postgresql-contrib

# Status prüfen
systemctl status postgresql

# Version
psql --version

CentOS/RHEL

# Repository hinzufügen
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# PostgreSQL 16 installieren
dnf -qy module disable postgresql
dnf install -y postgresql16-server

# Initialisieren
/usr/pgsql-16/bin/postgresql-16-setup initdb

# Starten
systemctl enable --now postgresql-16

Erste Schritte

Als postgres-User verbinden

# Zum postgres-User wechseln
sudo -i -u postgres

# psql starten
psql

Grundlegende Befehle

-- Verbundene Datenbank
\conninfo

-- Datenbanken auflisten
\l

-- Tabellen auflisten
\dt

-- Benutzer auflisten
\du

-- Hilfe
\?

-- SQL-Hilfe
\h CREATE TABLE

-- Beenden
\q

Datenbanken verwalten

Datenbank erstellen

-- Als SQL
CREATE DATABASE meine_db;

-- Mit Optionen
CREATE DATABASE meine_db
    OWNER = mein_user
    ENCODING = 'UTF8'
    LC_COLLATE = 'de_DE.UTF-8'
    LC_CTYPE = 'de_DE.UTF-8'
    TEMPLATE = template0;
# Von der Shell
createdb meine_db

Datenbank löschen

DROP DATABASE meine_db;
dropdb meine_db

Zu Datenbank verbinden

\c meine_db

Benutzer verwalten

Benutzer erstellen

-- Einfacher Benutzer
CREATE USER mein_user WITH PASSWORD 'sicheres_passwort';

-- Mit Optionen
CREATE USER mein_user WITH
    PASSWORD 'sicheres_passwort'
    CREATEDB
    LOGIN;

-- Superuser (vorsichtig!)
CREATE USER admin_user WITH
    PASSWORD 'passwort'
    SUPERUSER;

Berechtigungen

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

-- Nur Leserechte
GRANT CONNECT ON DATABASE meine_db TO leser;
GRANT USAGE ON SCHEMA public TO leser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO leser;

-- Rechte entziehen
REVOKE ALL PRIVILEGES ON DATABASE meine_db FROM mein_user;

Passwort ändern

ALTER USER mein_user WITH PASSWORD 'neues_passwort';

Benutzer löschen

DROP USER mein_user;

Tabellen und Daten

Tabelle erstellen

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT true
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    tags TEXT[],
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Daten einfügen

INSERT INTO users (username, email, password_hash)
VALUES ('max', 'max@example.com', 'hash123');

INSERT INTO posts (user_id, title, content, tags, metadata)
VALUES (
    1,
    'Mein erster Post',
    'Inhalt des Posts',
    ARRAY['postgresql', 'tutorial'],
    '{"views": 0, "likes": 0}'::jsonb
);

Daten abfragen

-- Alle Daten
SELECT * FROM users;

-- Mit Bedingung
SELECT username, email FROM users WHERE is_active = true;

-- Join
SELECT p.title, u.username
FROM posts p
JOIN users u ON p.user_id = u.id;

-- JSON-Feld abfragen
SELECT title, metadata->>'views' as views
FROM posts
WHERE (metadata->>'likes')::int > 10;

-- Array-Suche
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);

Daten aktualisieren

UPDATE users SET is_active = false WHERE id = 1;

-- JSON aktualisieren
UPDATE posts
SET metadata = jsonb_set(metadata, '{views}', '100')
WHERE id = 1;

Daten löschen

DELETE FROM users WHERE id = 1;

Authentifizierung konfigurieren

pg_hba.conf

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Lokale Verbindungen
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 Verbindungen
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.0/24          md5

# IPv6 Verbindungen
host    all             all             ::1/128                 md5

Authentifizierungsmethoden

| Methode | Beschreibung | |---------|--------------| | peer | Unix-User = DB-User | | md5 | Passwort (MD5-Hash) | | scram-sha-256 | Sicheres Passwort | | trust | Ohne Passwort (unsicher!) | | reject | Verbindung ablehnen |

Änderungen anwenden

systemctl reload postgresql

Remote-Zugriff

postgresql.conf

# /etc/postgresql/16/main/postgresql.conf

# Auf allen Interfaces lauschen
listen_addresses = '*'

# Oder bestimmte IP
listen_addresses = '192.168.1.10'

Firewall

# UFW
ufw allow 5432/tcp

# iptables
iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

Verbindung testen

psql -h server-ip -U mein_user -d meine_db

Backup und Restore

pg_dump (einzelne Datenbank)

# Backup erstellen
pg_dump meine_db > backup.sql

# Mit Kompression
pg_dump meine_db | gzip > backup.sql.gz

# Custom Format (für pg_restore)
pg_dump -Fc meine_db > backup.dump

# Nur Schema
pg_dump --schema-only meine_db > schema.sql

# Nur Daten
pg_dump --data-only meine_db > data.sql

pg_dumpall (alle Datenbanken)

pg_dumpall > all_databases.sql

Restore

# SQL-Dump
psql meine_db < backup.sql

# Komprimiert
gunzip -c backup.sql.gz | psql meine_db

# Custom Format
pg_restore -d meine_db backup.dump

# In neue Datenbank
createdb neue_db
pg_restore -d neue_db backup.dump

Automatisches Backup

#!/bin/bash
# /usr/local/bin/pg-backup.sh

BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7

mkdir -p "$BACKUP_DIR"

# Alle Datenbanken sichern
for db in $(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"); do
    pg_dump -U postgres -Fc "$db" > "$BACKUP_DIR/${db}_${DATE}.dump"
done

# Alte Backups löschen
find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete
# Crontab
0 2 * * * /usr/local/bin/pg-backup.sh

Performance-Tuning

postgresql.conf Grundeinstellungen

# /etc/postgresql/16/main/postgresql.conf

# Speicher
shared_buffers = 256MB          # 25% des RAM
effective_cache_size = 768MB    # 75% des RAM
work_mem = 16MB                 # Pro Operation
maintenance_work_mem = 128MB    # Für VACUUM, etc.

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Planer
random_page_cost = 1.1          # Für SSD
effective_io_concurrency = 200  # Für SSD

# Logging
log_min_duration_statement = 1000  # Queries > 1s loggen

Konfiguration prüfen

SHOW shared_buffers;
SHOW work_mem;

-- Alle Einstellungen
SELECT name, setting, unit FROM pg_settings;

Konfiguration neu laden

systemctl reload postgresql

VACUUM und ANALYZE

-- Tabelle aufräumen
VACUUM users;

-- Mit Statistik-Update
VACUUM ANALYZE users;

-- Vollständig (sperrt Tabelle)
VACUUM FULL users;

-- Automatisch (in postgresql.conf)
autovacuum = on

Index erstellen

-- B-Tree Index (Standard)
CREATE INDEX idx_users_email ON users(email);

-- Für LIKE-Suchen
CREATE INDEX idx_users_username_pattern ON users(username varchar_pattern_ops);

-- Für JSON
CREATE INDEX idx_posts_metadata ON posts USING GIN(metadata);

-- Partial Index
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Query analysieren

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Mit Ausführung
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Nützliche Extensions

Extensions installieren

-- Verfügbare Extensions
SELECT * FROM pg_available_extensions;

-- Extension aktivieren
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- UUID generieren
SELECT uuid_generate_v4();

Beliebte Extensions

| Extension | Funktion | |-----------|----------| | uuid-ossp | UUID-Generierung | | pg_trgm | Trigram-Suche (Ähnlichkeit) | | hstore | Key-Value-Paare | | postgis | Räumliche Daten | | pg_stat_statements | Query-Statistiken |

Monitoring

Aktive Verbindungen

SELECT * FROM pg_stat_activity;

-- Aktive Queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active';

Datenbankgröße

SELECT pg_size_pretty(pg_database_size('meine_db'));

-- Alle Datenbanken
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database ORDER BY pg_database_size(datname) DESC;

Tabellengröße

SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Langsame Queries

-- pg_stat_statements aktivieren
CREATE EXTENSION pg_stat_statements;

-- Langsame Queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Zusammenfassung

| Aufgabe | Befehl | |---------|--------| | Verbinden | psql -U user -d database | | DB erstellen | CREATE DATABASE name; | | User erstellen | CREATE USER name WITH PASSWORD 'pw'; | | Rechte geben | GRANT ALL ON DATABASE db TO user; | | Backup | pg_dump db > backup.sql | | Restore | psql db < backup.sql | | Reload | systemctl reload postgresql |

Fazit

PostgreSQL ist eine ausgereifte Datenbank mit vielen Features. Die Grundkonfiguration ist einfach, und für die meisten Anwendungen reichen die Standard-Einstellungen. Achten Sie auf regelmäßige Backups, konfigurieren Sie die Authentifizierung sicher und nutzen Sie VACUUM ANALYZE für optimale Performance.