PostgreSQL ist ein leistungsstarkes Open-Source-Datenbanksystem. Es bietet umfangreiche Funktionen für Enterprise-Anwendungen.

Installation

Debian/Ubuntu

# PostgreSQL installieren
apt update
apt install postgresql postgresql-contrib

# Version prüfen
psql --version

# Status
systemctl status postgresql

CentOS/RHEL

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

# Installieren
dnf install postgresql16-server postgresql16

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

# Starten
systemctl enable --now postgresql-16

Erste Schritte

Als postgres-User verbinden

# Zu postgres wechseln
sudo -i -u postgres

# psql starten
psql

# Oder direkt
sudo -u postgres psql

Grundbefehle

-- Datenbanken auflisten
\l

-- Mit Datenbank verbinden
\c dbname

-- Tabellen anzeigen
\dt

-- Tabelle beschreiben
\d tablename

-- Benutzer anzeigen
\du

-- Hilfe
\?

-- Beenden
\q

Benutzer und Rollen

Benutzer erstellen

-- Mit Passwort
CREATE USER appuser WITH PASSWORD 'password';

-- Mit Optionen
CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'adminpass';

-- Rolle ohne Login
CREATE ROLE readonly;

Berechtigungen

-- Datenbank-Rechte
GRANT ALL PRIVILEGES ON DATABASE mydb TO appuser;
GRANT CONNECT ON DATABASE mydb TO appuser;

-- Schema-Rechte
GRANT USAGE ON SCHEMA public TO appuser;
GRANT ALL ON SCHEMA public TO appuser;

-- Tabellen-Rechte
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appuser;

-- Für zukünftige Tabellen
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

-- Readonly-Rolle
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Passwort ändern

ALTER USER appuser WITH PASSWORD 'newpassword';

-- Passwort läuft ab
ALTER USER appuser VALID UNTIL '2025-12-31';

Datenbanken verwalten

Datenbank erstellen

-- Einfach
CREATE DATABASE mydb;

-- Mit Optionen
CREATE DATABASE mydb
  OWNER appuser
  ENCODING 'UTF8'
  LC_COLLATE 'de_DE.UTF-8'
  LC_CTYPE 'de_DE.UTF-8'
  TEMPLATE template0;

-- Von Template
CREATE DATABASE newdb TEMPLATE existingdb;

Datenbank löschen

-- Verbindungen trennen
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb';

-- Löschen
DROP DATABASE mydb;

Datenbank-Info

-- Größe
SELECT pg_size_pretty(pg_database_size('mydb'));

-- Alle Datenbanken mit Größe
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Konfiguration

postgresql.conf

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

# Verbindungen
listen_addresses = 'localhost'          # oder '*'
port = 5432
max_connections = 100

# Speicher
shared_buffers = 256MB                  # 25% des RAM
effective_cache_size = 768MB            # 75% des RAM
work_mem = 4MB
maintenance_work_mem = 64MB

# WAL
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000       # Log queries > 1s

pg_hba.conf (Authentifizierung)

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

# TYPE  DATABASE    USER        ADDRESS         METHOD
local   all         postgres                    peer
local   all         all                         peer
host    all         all         127.0.0.1/32    scram-sha-256
host    all         all         ::1/128         scram-sha-256
host    all         all         192.168.1.0/24  scram-sha-256
host    replication replica     192.168.1.0/24  scram-sha-256

Konfiguration neu laden

SELECT pg_reload_conf();
# Oder
systemctl reload postgresql

Tabellen und Schemas

Schema erstellen

CREATE SCHEMA app;
CREATE SCHEMA app AUTHORIZATION appuser;

-- Standardschema setzen
SET search_path TO app, public;

-- Permanent für Benutzer
ALTER USER appuser SET search_path TO app, public;

Tabelle erstellen

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

-- Mit Constraints
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indizes

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

-- Unique Index
CREATE UNIQUE INDEX idx_users_username ON users(username);

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

-- Multi-Column Index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- GIN Index (für Arrays, JSON)
CREATE INDEX idx_tags ON articles USING GIN(tags);

-- Indizes anzeigen
\di

Backup und Restore

pg_dump (Einzelne Datenbank)

# Backup
pg_dump mydb > mydb.sql
pg_dump -Fc mydb > mydb.dump     # Custom Format (komprimiert)
pg_dump -Ft mydb > mydb.tar      # Tar Format

# Mit Authentifizierung
pg_dump -U postgres -h localhost mydb > mydb.sql

# Nur Schema
pg_dump -s mydb > schema.sql

# Nur Daten
pg_dump -a mydb > data.sql

# Bestimmte Tabellen
pg_dump -t users -t orders mydb > tables.sql

pg_restore

# Aus Custom Format
pg_restore -d mydb mydb.dump

# Neu erstellen
pg_restore -C -d postgres mydb.dump

# Parallel (schneller)
pg_restore -j 4 -d mydb mydb.dump

pg_dumpall (Alle Datenbanken)

# Komplettes Backup
pg_dumpall > all_databases.sql

# Nur Globale Objekte (User, Rollen)
pg_dumpall --globals-only > globals.sql

# Restore
psql -f all_databases.sql postgres

Automatisiertes 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
for db in $(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
    pg_dump -U postgres -Fc $db > "$BACKUP_DIR/${db}_${DATE}.dump"
done

# Globals
pg_dumpall -U postgres --globals-only > "$BACKUP_DIR/globals_${DATE}.sql"

# Alte Backups löschen
find $BACKUP_DIR -type f -mtime +$KEEP_DAYS -delete

Replikation

Streaming Replication

# Primary: postgresql.conf
wal_level = replica
max_wal_senders = 3
wal_keep_size = 128MB

# Primary: pg_hba.conf
host replication replica 192.168.1.0/24 scram-sha-256

# Primary: Replikations-User
CREATE USER replica WITH REPLICATION PASSWORD 'replicapass';

Standby einrichten

# Auf Standby
systemctl stop postgresql

# Basis-Backup vom Primary
pg_basebackup -h primary -D /var/lib/postgresql/16/main -U replica -P -R

# Standby starten
systemctl start postgresql

Replikation prüfen

-- Auf Primary
SELECT * FROM pg_stat_replication;

-- Auf Standby
SELECT * FROM pg_stat_wal_receiver;

Performance

EXPLAIN ANALYZE

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

-- Buffers anzeigen
EXPLAIN (ANALYZE, BUFFERS) SELECT ...

Statistiken aktualisieren

-- Tabellen-Statistiken
ANALYZE users;
ANALYZE;  -- Alle Tabellen

-- Automatisches Vacuum
-- In postgresql.conf aktiviert

VACUUM

-- Speicher freigeben
VACUUM;
VACUUM users;

-- Mit Statistiken
VACUUM ANALYZE;

-- Vollständig (exklusiver Lock)
VACUUM FULL users;

Langsame Queries finden

-- Aktuell laufende Queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- pg_stat_statements Extension
CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Connection Pooling (PgBouncer)

# /etc/pgbouncer/pgbouncer.ini

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Monitoring

Wichtige Views

-- Verbindungen
SELECT * FROM pg_stat_activity;

-- Datenbank-Statistiken
SELECT * FROM pg_stat_database;

-- Tabellen-Statistiken
SELECT * FROM pg_stat_user_tables;

-- Index-Nutzung
SELECT * FROM pg_stat_user_indexes;

-- Locks
SELECT * FROM pg_locks;

Verbindungen

-- Aktive Verbindungen
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Verbindungen pro Datenbank
SELECT datname, count(*)
FROM pg_stat_activity
GROUP BY datname;

-- Verbindung beenden
SELECT pg_terminate_backend(pid);

Troubleshooting

Logs

# Log-Verzeichnis
/var/log/postgresql/
/var/lib/postgresql/16/main/pg_log/

# Live Logs
tail -f /var/log/postgresql/postgresql-16-main.log

Sperrungen (Locks)

-- Blockierende Queries
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Tabellenbloat

-- Bloat-Analyse
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Zusammenfassung

| Befehl | Funktion | |--------|----------| | \l | Datenbanken anzeigen | | \c dbname | Verbinden | | \dt | Tabellen anzeigen | | \du | Benutzer anzeigen | | \d table | Tabelle beschreiben |

| Tool | Verwendung | |------|------------| | psql | Interaktiver Client | | pg_dump | Backup | | pg_restore | Restore | | pg_basebackup | Physisches Backup | | vacuumdb | Vacuum |

| View | Information | |------|-------------| | pg_stat_activity | Verbindungen | | pg_stat_database | DB-Statistiken | | pg_stat_user_tables | Tabellen-Stats | | pg_locks | Sperren |

Fazit

PostgreSQL ist ein mächtiges RDBMS mit umfangreichen Enterprise-Features. Die Kombination aus Stabilität, Performance und Erweiterbarkeit macht es zur ersten Wahl für kritische Anwendungen. Regelmäßige Backups, Monitoring und VACUUM sind essentiell für den stabilen Betrieb. Mit Streaming Replication und PgBouncer lässt sich PostgreSQL auch für hochverfügbare und skalierbare Architekturen einsetzen.