PostgreSQL Referenz für myapp_db

Verbindung

psql -h localhost -p 5432 -U app_user -d myapp_db

Connection String

postgresql://app_user:PASSWORD@localhost:5432/myapp_db

Häufige Befehle

psql Konsole

| Befehl | Beschreibung | |--------|--------------| | \l | Datenbanken auflisten | | \dt | Tabellen anzeigen | | \d table_name | Tabellenstruktur | | \q | Beenden |

Datenbank-Operationen

-- Datenbank erstellen
CREATE DATABASE myapp_db;

-- Benutzer erstellen
CREATE USER app_user WITH PASSWORD 'sicheres_passwort';

-- Rechte vergeben
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO app_user;

Backup & Restore

# Backup erstellen
pg_dump -h localhost -U app_user myapp_db > backup.sql

# Backup wiederherstellen
psql -h localhost -U app_user myapp_db < backup.sql

Performance

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

-- Index erstellen
CREATE INDEX idx_name ON table_name(column_name);

-- Query Plan analysieren
EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

Nützliche Extensions

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";  -- UUIDs
CREATE EXTENSION IF NOT EXISTS "pg_trgm";    -- Fuzzy Search