PostgreSQL installieren und einrichten unter Linux

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

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

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.