PostgreSQL Cheatsheet
DevOpspostgresqlsqldatabasepostgresdevops
Back openDesk Edu for a sovereign, open-source education — every vote counts.
Vote nowSave products you love by clicking the heart icon.
Produktions-Deployment-Guide für PostgreSQL mit optimierter Konfiguration und Redis mit RDB+AOF-Persistenz, inklusive automatisierter Backups und Point-in-Time-Recovery.
PostgreSQL ist ein leistungsstarkes Open-Source-Objekt-relationales Datenbanksystem. Dieser Leitfaden bietet eine tiefgehende technische Referenz für die Administration, fortgeschrittenes SQL und Performance-Tuning.
# Install PostgreSQL (Debian/Ubuntu)
sudo apt update
sudo apt install postgresql postgresql-contrib
# Switch to postgres user
sudo -i -u postgres
psql
# Access a specific database with a user
psql -d my_database -U my_user -h localhost
# List all databases
\l
# List all tables in current database
\dt
# List all users/roles
\du
# Show table structure (describe)
\d table_name
# Show table of columns
\d+ table_name
-- Create a table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert data
INSERT INTO users (username, email)
VALUES ('johndoe', 'john@example.com');
-- Select data
SELECT * FROM users WHERE username = 'johndoe';
SELECT id, username FROM users LIMIT 10;
-- Update data
UPDATE users SET email = 'john_new@example.com' WHERE id = 1;
-- Delete data
DELETE FROM users WHERE id = 1;
-- Inner Join
SELECT orders.id, users.username
FROM orders
JOIN users ON orders.user_id = users.id;
-- Left Join (all users, even without orders)
SELECT users.username, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- Self Join
SELECT a.name, b.name
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;
-- Create a table with JSONB
CREATE TABLE products (
id SERIAL PRIMARY KEY,
metadata JSONB
);
-- Insert JSONB data
INSERT INTO products (metadata) VALUES ('{"brand": "Apple", "specs": {"cpu": "M2", "ram": "16GB"}}');
-- Query JSONB with containment operator (@>)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
-- Access nested values with ->>
SELECT metadata->'specs'->>'cpu' AS cpu_type FROM products;
-- Use GIN index for JSONB performance
CREATE INDEX idx_metadata_gin ON products USING GIN (metadata);
-- JSONB path expressions
SELECT * FROM products
WHERE metadata @@ '$.specs.cpu == "M2"';
-- Simple CTE
WITH regional_sales AS (
SELECT region, SUM(amount) as total_sales
FROM sales
GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 10000;
-- Rekursives CTE (Hierarchische Daten) WITH RECURSIVE sub_tree AS ( SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c JOIN sub_tree st ON c.parent_id = st.id ) SELECT * FROM sub_tree;
#### Window Functions
```sql
-- Zeilennummerierung
SELECT name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;
-- Gleitende Durchschnitte & Lags
SELECT date, revenue,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg,
LAG(revenue) OVER (ORDER BY date) as prev_day_revenue,
LEAD(revenue) OVER (ORDER BY date) as next_day_revenue
FROM daily_sales;
-- Benutzer mit Passwort erstellen
CREATE USER web_user WITH PASSWORD 'strong_password';
-- Berechtigungen für eine Tabelle gewähren
GRANT SELECT, INSERT, UPDATE ON users TO web_user;
-- Alle Berechtigungen für eine Datenbank gewähren
GRANT ALL PRIVILEGES ON DATABASE my_db TO web_user;
-- Rollenhierarchie
CREATE ROLE readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
-- Passwort eines Benutzers ändern
ALTER USER web_user WITH PASSWORD 'new_password';
-- Vacuuming & Analyzing
VACUUM; -- Standard-Vacuum
VACUUM FULL; -- Komprimiert die Datenbank (erfordert exklusiven Lock)
ANALYZE; -- Aktualisiert Statistiken
VACUUM ANALYZE users; -- Sowohl Vacuum als auch Analyze für eine spezifische Tabelle
-- Reindex
REINDEX TABLE users; -- Erstellt Indizes für die Tabelle neu
REINDEX DATABASE my_db; -- Erstellt alle Indizes in der Datenbank neu
-- Wartungsstatistiken
SELECT relname, reltuples, relsize FROM pg_class WHERE relname = 'users';
# Einzelne Datenbank in eine Datei dumpen
pg_dump -U username -d db_name > db_backup.sql
# Wiederherstellung aus einer SQL-Datei
psql -U username -d db_name < db_backup.sql
# Vollständiges Cluster-Backup
pg_dumpall -U username > full_cluster_backup.sql
# Vollständiges Cluster wiederherstellen
psql -U username -f full_cluster_backup.sql
-- B-Tree (Standard)
CREATE INDEX idx_users_email ON users(email);
-- GIN (Generalized Inverted Index) - Bestens geeignet für JSONB/Arrays
CREATE INDEX idx_metadata_gin ON products USING GIN (metadata);
-- GiST (Generalized Search Tree) - Bestens geeignet für Geometrie/Näherung
CREATE INDEX idx_location ON points USING GiST (geom);
-- BRIN (Block Range Index) - Für massive, natürlich geordnete Datensätze
CREATE INDEX idx_timestamp ON logs USING BRIN (created_at);
-- Expressions-Index (Funktional)
CREATE INDEX idx_lower_email ON users (LOWER(email));
-- Range Partitioning (Nach Datum)
CREATE TABLE measurement (
city_id int,
log_date date NOT NULL,
resp_time float
) PARTITION BY RANGE (log_date);
-- Erstellen einer Partition für Januar 2023
CREATE TABLE measurement_y2023m01
PARTITION OF measurement
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
-- List Partitioning (Nach Region)
CREATE TABLE user_logs (...) PARTITION BY LIST (region);
CREATE TABLE logs_us PART_OF user_logs FOR VALUES IN ('US', 'CA');
-- Hash Partitioning (Nach ID)
CREATE TABLE users_hashed (...) PARTITION BY HASH (id);
CREATE TABLE users_part_1 PARTITION OF users_hashed FOR VALUES $= 0$;
-- Einfaches Explain
EXPLAIN SELECT * FROM users WHERE id = 1;
-- Detailliertes Explain mit tatsächlichen Ausführungszeiten
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Explain mit Buffer-Cache-Statistiken
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
-- Anzeige der kostspieligsten Knoten in einem Plan
EXPLAIN (FORMAT JSON, ANALYZE) SELECT * FROM orders;
-- Aktuelle Konfiguration prüfen
SHOW max_connections;
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;
-- Konfiguration auf Session-Ebene ändern
SET work_mem = '64MB';
SET client_encoding = 'UTF8';
SET lock_timeout = '1s';
-- Anpassung der Autovacuum-Einstellungen
ALTER TABLE users SET (autovacuum_enabled = true);
-- Explizites Row-Level Locking
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- Stärkster Lock
SELECT * FROM users WHERE id = 1 FOR SHARE; -- Shared Lock
SELECT * FROM users WHERE id = 1 FOR UPDATE OF name;
-- Transaktions-Isolationsstufen
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- (Hohe Sicherheit, hohe Contention)
BEGIN ISOLATION LEVEL READ COMMITTED;
-- (Standard: liest nur committed Daten)
-- Advisory Locks (Anwendungsebene)
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);
-- Aktive Verbindungen und lang laufende Queries anzeigen
SELECT pid, usename, query, state, wait_event
FROM pg_stat_activity
WHERE state != 'idle';
-- Tabellengröße und Bloat prüfen
SELECT pg_size_pretty(pg_relation_size('users'));
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- Aktuelle Locks prüfen
SELECT l.pid, a.datname, l.mode, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid;
# Verbindungen via psql prüfen
\conninfo
# Alle aktiven Verbindungen anzeigen
SELECT count(*) FROM pg_stat_activity;
# Eine Verbindung per PID beenden
SELECT pg_terminate_backend(pid);
-- Basis-Volltextsuche
SELECT title, body
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'PostgreSQL & performance');
-- Erstellen einer tsvector-Spalte mit GIN-Index
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
-- Befüllen des Suchvektors (Trigger-basiert wird bevorzugt)
UPDATE articles SET search_vector =
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B');
-- Gewichtete Suche: Treffer im Titel werden höher gewichtet als im Body
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database') query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- Phrasensuche
SELECT title FROM articles
WHERE to_tsvector('english', body) @@ phraseto_tsquery('english', 'performance tuning');
-- Headline-Funktion (generiert Auszüge mit hervorgehobenen Treffern)
SELECT ts_headline('english', body, to_tsquery('english', 'PostgreSQL'))
FROM articles WHERE id = 1;
-- Trigger erstellen, um den Search-Vector automatisch zu aktualisieren
CREATE FUNCTION articles_search_vector_update() RETURNS trigger AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER articles_search_vector_trigger
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_vector_update();
-- PostGIS — räumliche Daten
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
geom GEOMETRY(Point, 4326) -- WGS84 (GPS-Koordinaten)
);
INSERT INTO locations (name, geom)
VALUES ('Berlin', ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326));
-- Orte innerhalb von 10 km eines Punktes finden
SELECT name, ST_Distance(geom, ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326)) AS distance
FROM locations
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326), 10000)
ORDER BY distance;
-- pg_stat_statements — Abfragestatistiken
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Die 10 langsamsten Abfragen
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- pgcrypto — kryptografische Funktionen
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- UUID v4 generieren
SELECT gen_random_uuid();
-- Passwörter hashen
SELECT crypt('my_password', gen_salt('bf'));
-- Passwort verifizieren
SELECT (crypt('my_password', stored_hash) = stored_hash) AS password_valid;
-- uuid-ossp — UUID-Generierung
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4(); -- zufällige UUID
SELECT uuid_generate_v1(); -- MAC-Adresse + Zeitstempel
-- hstore — Key-Value-Store
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
attrs hstore -- flexible Key-Value-Attribute
);
INSERT INTO products (name, attrs) VALUES ('Widget', '"color"=>"red", "weight"=>"1.5"');
SELECT * FROM products WHERE attrs ? 'color';
SELECT attrs -> 'weight' FROM products WHERE name = 'Widget';
-- citext — Case-Insensitive Text
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE accounts (
username CITEXT PRIMARY KEY,
email CITEXT UNIQUE
);
INSERT INTO accounts VALUES ('JohnDoe', 'John@Example.Com');
SELECT * FROM accounts WHERE username = 'johndoe'; -- Treffer!
# --- Konfiguration des Primary-Servers ---
# postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
hot_standby = on
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
# pg_hba.conf — Replikationsverbindungen zulassen
host replication replicator 10.0.0.0/24 scram-sha-256
# Replikationsbenutzer erstellen
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'strong_replication_password';
# --- Standby-Server Setup ---
# PostgreSQL auf dem Standby stoppen
sudo systemctl stop postgresql
# pg_basebackup verwenden, um den Primary zu klonen
sudo -u postgres pg_basebackup \
-h primary_host -D /var/lib/postgresql/15/main \
-U replicator -P -v -R -X stream \
-C -S standby_slot
# -R erstellt standby.signal und setzt primary_conninfo in postgresql.auto.conf
# -C -S erstellt einen Replication Slot auf dem Primary
# Start des Standby
sudo systemctl start postgresql
-- Replikationsstatus auf dem PRIMARY überprüfen
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;
-- Health-Check des Replication Slots
SELECT slot_name, active, restart_lsn,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;
-- Failover: Standby zum Primary befördern
SELECT pg_promote();
-- Oder über die Kommandozeile:
# pg_ctl promote -D /var/lib/postgresql/15/main
-- Nach dem Failover: alten Primary als neuen Standby neu aufbauen
-- Publisher: Eine Publication erstellen
CREATE PUBLICATION my_publication FOR TABLE users, orders, products;
CREATE PUBLICATION all_tables FOR ALL TABLES;
-- Subscriber: Eine Subscription erstellen
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=primary_host dbname=mydb user=replicator password=secret'
PUBLICATION my_publication
WITH (copy_data = true, create_slot = true);
-- Subscriptions verwalten
ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription ENABLE;
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;
-- Status der Subscription anzeigen
SELECT subname, subrelid, substate FROM pg_subscription;
-- Eine Tabelle zu einer bestehenden Publication hinzufügen
ALTER PUBLICATION my_publication ADD TABLE new_table;
-- Anwendungsfall: Partielle Replikation (nur bestimmte Tabellen an ein Data Warehouse)
CREATE PUBLICATION analytics_pub FOR TABLE orders, order_items, products;
-- Reguläre View (virtuelle Tabelle — Abfrage wird jedes Mal ausgeführt)
CREATE VIEW active_users AS
SELECT id, username, email, last_login
FROM users
WHERE last_login > CURRENT_TIMESTAMP - INTERVAL '30 days';
-- Materialized View (Abfrageergebnis wird auf der Festplatte gespeichert)
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
WITH DATA;
-- Index auf der Materialized View für schnelle Abfragen erstellen
CREATE INDEX idx_monthly_sales_month ON monthly_sales (month);
-- Materialized View aktualisieren (sperrt die Tabelle während des Refreshs)
REFRESH MATERIALIZED VIEW monthly_sales;
-- Concurrent Refresh (keine Sperre — bleibt für Lesezugriffe verfügbar)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
-- Materialized View löschen
DROP MATERIALIZED VIEW IF EXISTS monthly_sales;
-- Anwendungsfall: Vorberechnete Dashboard-Daten
CREATE MATERIALIZED VIEW dashboard_metrics AS
SELECT
(SELECT COUNT(*) FROM users WHERE created_at > NOW() - INTERVAL '7 days') AS new_users,
(SELECT COUNT(*) FROM orders WHERE status = 'pending') AS pending_orders,
(SELECT SUM(amount) FROM payments WHERE created_at > NOW() - INTERVAL '1 day') AS daily_revenue;
-- Einfache Funktion, die einen einzelnen Wert zurückgibt
CREATE OR REPLACE FUNCTION get_order_total(p_order_id INT)
RETURNS NUMERIC AS $$
DECLARE
v_total NUMERIC;
BEGIN
SELECT SUM(quantity * unit_price)
INTO v_total
FROM order_items
WHERE order_id = p_order_id;
RETURN COALESCE(v_total, 0);
END;
$$ LANGUAGE plpgsql;
-- Funktion, die eine Tabelle zurückgibt (set-returning function)
CREATE OR REPLACE FUNCTION user_order_summary(p_user_id INT)
RETURNS TABLE (order_id INT, total NUMERIC, item_count INT) AS $$
BEGIN
RETURN QUERY
SELECT o.id, SUM(oi.quantity * oi.unit_price), COUNT(oi.id)
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = p_user_id
GROUP BY o.id
ORDER BY o.id DESC;
END;
$$ LANGUAGE plpgsql;
-- Funktion mit OUT-Parametern
CREATE OR REPLACE FUNCTION calculate_discount(
p_amount NUMERIC,
p_tier TEXT,
OUT discounted_amount NUMERIC,
OUT discount_pct NUMERIC
) AS $$
BEGIN
discount_pct := CASE p_tier
WHEN 'gold' THEN 0.15
WHEN 'silver' THEN 0.10
WHEN 'bronze' THEN 0.05
ELSE 0
END;
discounted_amount := p_amount * (1 - discount_pct);
END;
$$ LANGUAGE plpgsql;
-- SECURITY DEFINER: wird mit den Privilegien des Funktionsbesitzers ausgeführt
CREATE OR REPLACE FUNCTION admin_reset_password(p_user_id INT, p_new_hash TEXT)
RETURNS VOID
SECURITY DEFINER
SET search_path = public
LANGUAGE plpgsql AS $$
BEGIN
UPDATE users SET password_hash = p_new_hash WHERE id = p_user_id;
END;
$$;
-- Verwendung
SELECT get_order_total(42);
SELECT * FROM user_order_summary(7);
SELECT * FROM calculate_discount(100.00, 'gold');
-- Trigger-Funktion: Audit-Log für alle Änderungen an einer Tabelle
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
operation TEXT NOT NULL,
old_data JSONB,
new_data JSONB,
changed_by TEXT DEFAULT current_user,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, operation, old_data)
VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, new_data)
VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
-- Trigger an eine Tabelle binden
CREATE TRIGGER users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
-- BEFORE-Trigger: Geschäftsregeln erzwingen
CREATE OR REPLACE FUNCTION prevent_negative_balance()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.balance < 0 THEN
RAISE EXCEPTION 'Account balance cannot be negative (attempted: %)', NEW.balance;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_balance
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION prevent_negative_balance();
-- Trigger-Timing-Optionen: BEFORE, AFTER, INSTEAD OF
-- Zeilenebene: FOR EACH ROW (wird pro Zeile ausgeführt)
-- Statement-Ebene: FOR EACH STATEMENT (wird einmal pro Statement ausgeführt)
-- ARRAY
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title TEXT,
tags TEXT[]
);
INSERT INTO posts (title, tags) VALUES ('Intro to SQL', ARRAY['database', 'sql', 'beginner']);
SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT unnest(tags) FROM posts WHERE id = 1;
-- UUID
CREATE TABLE sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id INT NOT NULL,
expires_at TIMESTAMP NOT NULL
);
INSERT INTO sessions (user_id, expires_at) VALUES (42, NOW() + INTERVAL '24 hours');
-- MACADDR und INET/CIDR
CREATE TABLE network_devices (
id SERIAL PRIMARY KEY,
hostname TEXT,
ip_address INET,
mac_address MACADDR
);
INSERT INTO network_devices (hostname, ip_address, mac_address)
VALUES ('web-01', '192.168.1.10', '00:1a:2b:3c:4d:5e');
SELECT * FROM network_devices WHERE ip_address << '192.168.1.0/24'; -- innerhalb des Subnetzes
-- ENUM
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
ALTER TABLE orders ADD COLUMN status order_status DEFAULT 'pending';
SELECT * FROM orders WHERE status = 'shipped';
-- DOMAIN (benutzerdefinierter Typ mit Constraints)
CREATE DOMAIN email_address TEXT
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email email_address NOT NULL
);
-- Composite type (Zusammengesetzter Typ)
CREATE TYPE address AS (
street TEXT,
city TEXT,
zip_code TEXT,
country TEXT DEFAULT 'US'
);
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT,
billing_addr address
);
INSERT INTO customers (name, billing_addr)
VALUES ('Acme Corp', ROW('123 Main St', 'Springfield', '62701', 'US'));
SELECT (billing_addr).city FROM customers WHERE name = 'Acme Corp';
-- CHECK constraint
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(10,2) CHECK (price >= 0),
discount_pct INT CHECK (discount_pct BETWEEN 0 AND 100),
CHECK (price > discount_pct * price / 100) -- Constraint auf Tabellenebene
);
-- UNIQUE constraint (einfach und zusammengesetzt)
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
CREATE TABLE enrollments (
student_id INT,
course_id INT,
UNIQUE (student_id, course_id)
);
-- EXCLUDE constraint (verhindert überlappende Bereiche)
CREATE TABLE room_bookings (
room_id INT NOT NULL,
during TSTZRANGE NOT NULL,
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);
-- DEFERRABLE constraints (Prüfung am Ende der Transaktion)
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
balance NUMERIC CHECK (balance >= 0) DEFERRABLE INITIALLY DEFERRED
);
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- Constraints werden hier geprüft, nicht bei jedem UPDATE
-- Foreign key actions (Fremdschlüssel-Aktionen)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id)
ON DELETE CASCADE -- Bestellungen löschen, wenn Benutzer gelöscht wird
ON UPDATE CASCADE,
status TEXT DEFAULT 'pending'
);
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id)
ON DELETE SET NULL -- Logs behalten, Benutzerreferenz leeren
ON UPDATE SET NULL
);
# Export nach CSV
psql -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER" > users.csv
# Import aus CSV
psql -c "COPY users FROM STDIN WITH CSV HEADER" < users.csv
# Export mit benutzerdefiniertem Trennzeichen und NULL-Handling
psql -c "COPY products TO STDOUT WITH (FORMAT csv, DELIMITER '|', NULL 'NULL', HEADER true)" > products.csv
# Import spezifischer Spalten
psql -c "COPY users(username, email, created_at) FROM STDIN WITH CSV" < new_users.csv
# Binärformat (schneller bei großen Datensätzen)
psql -c "COPY large_table TO STDOUT WITH BINARY" > large_table.bin
psql -c "COPY large_table FROM STDIN WITH BINARY" < large_table.bin
-- SQL COPY (serverseitig, erfordert Superuser-Rechte)
COPY users TO '/tmp/users.csv' WITH CSV HEADER;
COPY users FROM '/tmp/users.csv' WITH CSV HEADER;
-- \copy (clientseitig, keine Superuser-Rechte erforderlich)
\copy users TO 'users.csv' CSV HEADER
\copy users FROM 'users.csv' CSV HEADER
# PgBouncer installieren
sudo apt install pgbouncer
# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 127.0.0.1
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
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
# Benutzer zur userlist.txt hinzufügen
echo '"mydb_user" "password_hash"' | sudo tee -a /etc/pgbouncer/userlist.txt
# Passwort-Hash aus PostgreSQL abrufen
psql -c "SELECT username || ':' || passwd FROM pg_shadow WHERE username = 'mydb_user'"
# Vergleich der Pool-Modi
# session — Verbindung wird erst nach Client-Disconnect wiederverwendet (sicherste Variante, benötigt die meisten Verbindungen)
# transaction — Verbindung wird nach Ende der Transaktion zurückgegeben (empfohlen für die meisten Anwendungen)
# statement — Verbindung wird nach jedem Statement zurückgegeben (nicht kompatibel mit Prepared Statements)
# Konfiguration neu laden
sudo systemctl reload pgbouncer
sudo systemctl restart pgbouncer
# Verbindung über PgBouncer (gleiches psql, anderer Port)
psql -h 127.0.0.1 -p 6432 -U mydb_user -d mydb
# Monitoring
psql -h 127.0.0.1 -p 6432 -d pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -d pgbouncer -c "SHOW STATS;"
# /etc/postgresql/15/main/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# Lokale Verbindungen
local all all peer
# IPv4 lokale Verbindungen (Passwort-Authentifizierung)
host all all 127.0.0.1/32 scram-sha-256
# IPv6 lokale Verbindungen
host all all ::1/128 scram-sha-256
# Applikationsserver (Passwort-Authentifizierung)
host mydb app_user 10.0.1.0/24 scram-sha-256
# Replikations-Verbindungen
host replication replicator 10.0.0.0/24 scram-sha-256
# Lokale Entwicklung vertrauen (unsicher!)
# host all all 192.168.1.0/24 trust
# Zertifikatsbasierte Authentifizierung
# hostssl all all 0.0.0.0/0 cert
# pg_hba.conf ohne Neustart neu laden
SELECT pg_reload_conf();
# postgresql.conf — WAL-Archivierung aktivieren
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300 # Erzwingt Archivierung alle 5 Minuten, auch wenn WAL nicht voll ist
# WAL-Komprimierung aktivieren (PostgreSQL 14+)
wal_compression = lz4
# --- Recovery (PITR) ---
# 1. PostgreSQL stoppen
sudo systemctl stop postgresql
# 2. Datenverzeichnis leeren
rm -rf /var/lib/postgresql/15/main/*
# 3. Aus Base-Backup wiederherstellen
sudo -u postgres pg_restore -D /var/lib/postgresql/15/main /backups/base_backup
# 4. Recovery-Signaldatei erstellen
sudo -u postgres touch /var/lib/postgresql/15/main/recovery.signal
# 5. Recovery-Target in postgresql.auto.conf konfigurieren
# restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
# recovery_target_time = '2026-04-20 14:30:00'
# recovery_target_action = 'promote'
# 6. PostgreSQL starten (Wiederherstellung beginnt automatisch)
sudo systemctl start postgresql
# pg_restore Optionen
pg_restore -d mydb -U username backup.dump # Wiederherstellung aus Custom-Format
pg_restore -d mydb -U username -c backup.dump # Bestehende Objekte zuerst löschen (clean)
pg_restore -d mydb -U username -j 4 backup.dump # Parallele Wiederherstellung mit 4 Jobs
pg_restore -l backup.dump > toc.txt # Inhalt auflisten
pg_restore -d mydb -L toc.txt backup.dump # Wiederherstellung mittels Inhaltsverzeichnis (selektiv)
-- Tablespace in einem spezifischen Verzeichnis erstellen
CREATE TABLESPACE fast_storage LOCATION '/mnt/nvme/postgresql';
-- Tablespace für Indizes erstellen
CREATE TABLESPACE index_storage LOCATION '/mnt/ssd/postgresql_indexes';
-- Tabelle in einem spezifischen Tablespace erstellen
CREATE TABLE large_analytics (
id BIGSERIAL,
event_data JSONB,
created_at TIMESTAMP DEFAULT NOW()
) TABLESPACE fast_storage;
-- Bestehende Tabelle in einen anderen Tablespace verschieben
ALTER TABLE large_analytics SET TABLESPACE fast_storage;
-- Alle Indizes einer Tabelle in einen anderen Tablespace verschieben
ALTER INDEX idx_analytics_created SET TABLESPACE index_storage;
-- Standard-Tablespace für neue Objekte festlegen
SET default_tablespace = fast_storage;
-- Tablespaces anzeigen
SELECT spcname, pg_tablespace_location(oid) AS location
FROM pg_tablespace;
-- Standard-Temp-Tablespace festlegen
SET temp_tablespaces = 'fast_storage';
-- Deep Dive in pg_stat_activity
SELECT pid, usename, datname, state, wait_event_type, wait_event,
query_start, state_change, EXTRACT(EPOCH FROM (NOW() - query_start)) AS duration_s,
LEFT(query, 80) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration_s DESC;
-- Top-Queries nach Gesamtausführungszeit (erfordert pg_stat_statements)
SELECT query, calls, total_exec_time AS total_ms,
mean_exec_time AS avg_ms, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;
-- Tabellen- und Indexgrößen
SELECT relname AS table_name,
pg_size_pretty(pg_table_size(relid)) AS table_size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_dead_tup AS dead_tuples,
n_live_tup AS live_tuples,
CASE WHEN n_dead_tup > 0.1 * n_live_tup THEN 'NEEDS VACUUM' ELSE 'OK' END AS vacuum_status
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
-- Index-Nutzung (unbenutzte Indizes finden)
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
idx_scan AS index_scans, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Analyse von Lock-Wartezeiten
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
EXTRACT(EPOCH FROM (NOW() - blocked.query_start)) AS blocked_duration_s
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
AND kl.database IS NOT DISTINCT FROM bl.database
AND kl.relation IS NOT DISTINCT FROM bl.relation
AND kl.page IS NOT DISTINCT FROM bl.page
AND kl.tuple IS NOT DISTINCT FROM bl.tuple
AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;
-- Bloat-Erkennung (Tabellen mit signifikantem Platzverlust)
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_stat_get_dead_tuples(c.oid) AS dead_tuples
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
ORDER BY pg_stat_get_dead_tuples(c.oid) DESC;
-- Datenbankweite Zusammenfassung
SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size,
pg_stat_activity.numbackends AS active_connections
FROM pg_database
LEFT JOIN pg_stat_activity ON pg_database.datname = pg_stat_activity.datname
GROUP BY pg_database.datname
ORDER BY pg_database_size(pg_database.datname) DESC;
### Best Practices
1. **Verwenden Sie immer `EXPLAIN ANALYZE`** für kritische Abfragen, um die Performance zu verifizieren.
2. **Bevorzugen Sie `JSONB` gegenüber `JSON`** für eine bessere Performance und Index-Unterstützung.
3. **Gehen Sie vorsichtig mit Transaktionen um**, um Deadlocks in Umgebungen mit hoher Nebenläufigkeit (High-Concurrency) zu vermeiden.
4. **Implementieren Sie Partitionierung** für Tabellen, die hunderte Millionen von Zeilen überschreiten.
5. **Nutzen Sie `VACUUM ANALYZE`** nach Bulk-Datenladungen, um die Statistiken zu aktualisieren.
6. **Implementieren Sie Connection Pooling** (z. B. PgBouncer), um eine Erschöpfung der Verbindungen zu verhindern.
7. **Verwenden Sie `LOWER()` und funktionale Indizes**, um case-insensitive Suchen zu optimieren.
8. **Nutzen Sie `CTE`s** für lesbare und komplexe hierarchische Abfragen.
9. **Design für MVCC**: Bedenken Sie, dass Updates/Deletes „tote“ Versionen von Zeilen erzeugen.
10. **Verwenden Sie immer explizite Transaktionsgrenzen** für mehrstufige Operationen.
11. **Nutzen Sie Materialized Views** für rechenintensive Abfragen, die keine Echtzeitdaten benötigen.
12. **Aktivieren Sie `pg_stat_statements`** in der Produktion, um die Performance von Abfragen über die Zeit zu verfolgen.
13. **Verwenden Sie `scram-sha-256`-Authentifizierung** anstelle von `md5` für alle Verbindungen.
14. **Archivieren Sie das WAL** und testen Sie regelmäßig die PITR-Wiederherstellung – Backups sind nur so gut wie Ihr Restore.
15. **Überwachen Sie den Replication Lag** und richten Sie Alerts ein, bevor dies die Read-after-Write-Konsistenz beeinträchtigt.
---
### Nächste Schritte
- [Bash CLI Tools Cheat Sheet](/cheatsheets/bash-tools-cheatsheet/) — Unix-Kommandozeilen-Utilities
- [Docker CLI Cheat Sheet](/cheatsheets/docker-cheatsheet/) — Containerisierung von PostgreSQL-Deployments
- [Systemd Cheat Sheet](/cheatsheets/systemd-cheatsheet/) — Service-Management für PostgreSQL
- [Redis Cheat Sheet](/cheatsheets/redis-cheatsheet/) — In-Memory-Datenspeicher
- [Git CLI Cheat Sheet](/cheatsheets/git-cheatsheet/) — Versionskontroll-Befehle
- [Nginx Cheat Sheet](/cheatsheets/nginx-cheatsheet/) — Reverse Proxy für PostgreSQL
- [Terraform Cheat Sheet](/cheatsheets/terraform-cheatsheet/) — Infrastructure as Code
---
## Ressourcen & Verwandte Plattformen
- **[tobias-weiss.org](https://tobias-weiss.org/content/cheatsheets)** - Weitere Cheat Sheets, Artikel und Shop-Produkte
- **[graphwiz.ai](https://graphwiz.ai/)** - KI, Knowledge Graphs und agentische Entwicklung
- **[courses.graphwiz.ai](https://courses.graphwiz.ai/)** - Interaktive Übungsprüfungen und Lernleitfäden
- **[ki-kompetenz-training.org](https://ki-kompetenz-training.org/)** - EU AI Act konforme KI-Kompetenztrainings
### Verwandte Cheat Sheets
- [Redis Cheat Sheet](https://tobias-weiss.org/content/cheatsheets/storage/redis-cheatsheet/) - Caching-Layer
- [MinIO S3 backup stack](https://tobias-weiss.org/store/minio-s3-backup-stack) - Production Object Storage