PostgreSQL/PostGIS

PostgreSQL mit PostGIS-Erweiterung ist die zentrale Geodatenbank von p2d2.

Installation

PostgreSQL 15

# Auf Debian/Ubuntu
apt install postgresql-15 postgresql-client-15

# Service starten
systemctl enable postgresql
systemctl start postgresql

PostGIS 3.4

# PostGIS-Paket installieren
apt install postgresql-15-postgis-3

# In Datenbank aktivieren
sudo -u postgres psql -d p2d2 -c "CREATE EXTENSION postgis;"
sudo -u postgres psql -d p2d2 -c "CREATE EXTENSION postgis_topology;"

Datenbank einrichten

Datenbank und User erstellen

-- Als postgres-User
CREATE USER p2d2 WITH PASSWORD 'secure_password';
CREATE DATABASE p2d2 OWNER p2d2;

-- In p2d2-Datenbank
\c p2d2
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

Schema erstellen

-- Hauptschema für Features
CREATE SCHEMA features;
GRANT ALL ON SCHEMA features TO p2d2;

-- Schema für Metadaten
CREATE SCHEMA metadata;
GRANT ALL ON SCHEMA metadata TO p2d2;

-- Schema für Versionierung
CREATE SCHEMA history;
GRANT ALL ON SCHEMA history TO p2d2;

Tabellen erstellen

Feature-Tabelle

CREATE TABLE features.friedhoefe (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    kategorie VARCHAR(50),
    adresse TEXT,
    telefon VARCHAR(50),
    email VARCHAR(100),
    webseite VARCHAR(255),
    oeffnungszeiten TEXT,
    beschreibung TEXT,
    geom GEOMETRY(MultiPolygon, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100),
    updated_by VARCHAR(100),
    status VARCHAR(20) DEFAULT 'draft',
    qc_status VARCHAR(20),
    qc_by VARCHAR(100),
    qc_at TIMESTAMP
);

-- Räumlicher Index
CREATE INDEX idx_friedhoefe_geom ON features.friedhoefe USING GIST(geom);

-- Attribut-Indizes
CREATE INDEX idx_friedhoefe_name ON features.friedhoefe(name);
CREATE INDEX idx_friedhoefe_status ON features.friedhoefe(status);

Versionierungs-Tabelle

CREATE TABLE history.friedhoefe_history (
    history_id SERIAL PRIMARY KEY,
    feature_id INTEGER,
    operation VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Trigger für Versionierung

-- Trigger-Funktion
CREATE OR REPLACE FUNCTION history.log_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO history.friedhoefe_history (feature_id, operation, old_data, new_data, changed_by)
        VALUES (OLD.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), NEW.updated_by);
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO history.friedhoefe_history (feature_id, operation, old_data, changed_by)
        VALUES (OLD.id, 'DELETE', row_to_json(OLD), OLD.updated_by);
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO history.friedhoefe_history (feature_id, operation, new_data, changed_by)
        VALUES (NEW.id, 'INSERT', row_to_json(NEW), NEW.created_by);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Trigger erstellen
CREATE TRIGGER friedhoefe_audit
    AFTER INSERT OR UPDATE OR DELETE ON features.friedhoefe
    FOR EACH ROW EXECUTE FUNCTION history.log_changes();

Performance-Tuning

postgresql.conf

# Shared Buffers (25% of RAM)
shared_buffers = 8GB

# Effective Cache (50-75% of RAM)
effective_cache_size = 24GB

# Work Memory
work_mem = 256MB

# Maintenance Work Memory
maintenance_work_mem = 2GB

# Max Parallel Workers
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Random Page Cost (SSD)
random_page_cost = 1.1

# Logging
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Connection-Pooling mit PgBouncer

# PgBouncer installieren
apt install pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
p2d2 = host=localhost port=5432 dbname=p2d2

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 100

Backup-Strategie

Logical Backup (pg_dump)

# Vollständiger Dump
pg_dump -U p2d2 -F c -b -v -f p2d2_$(date +%Y%m%d).backup p2d2

# Nur Schema
pg_dump -U p2d2 -s -f p2d2_schema.sql p2d2

# Nur Daten einer Tabelle
pg_dump -U p2d2 -t features.friedhoefe -f friedhoefe_data.sql p2d2

Physical Backup (pg_basebackup)

# Base-Backup für PITR
pg_basebackup -U replication -D /backup/pg_base -Fp -Xs -P

# WAL-Archivierung aktivieren
# postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

Automatisches Backup

# /usr/local/bin/backup-p2d2.sh
#!/bin/bash
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)

pg_dump -U p2d2 -F c -f $BACKUP_DIR/p2d2_$DATE.backup p2d2

# Alte Backups löschen (älter als 30 Tage)
find $BACKUP_DIR -name "p2d2_*.backup" -mtime +30 -delete

# Cronjob: täglich um 2 Uhr
# 0 2 * * * /usr/local/bin/backup-p2d2.sh

Monitoring

pg_stat_statements

-- Extension aktivieren
CREATE EXTENSION pg_stat_statements;

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

Prometheus-Exporter

# postgres_exporter installieren
docker run -d \
  --name postgres-exporter \
  -p 9187:9187 \
  -e DATA_SOURCE_NAME="postgresql://p2d2:password@localhost:5432/p2d2?sslmode=disable" \
  prometheuscommunity/postgres-exporter

Wartung

VACUUM und ANALYZE

# Automatisches VACUUM
# postgresql.conf:
autovacuum = on
autovacuum_naptime = 1min

# Manuelles VACUUM
VACUUM ANALYZE features.friedhoefe;

# VACUUM FULL (sperrt Tabelle!)
VACUUM FULL features.friedhoefe;

Reindex

-- Index neubauen
REINDEX INDEX idx_friedhoefe_geom;

-- Alle Indizes einer Tabelle
REINDEX TABLE features.friedhoefe;

VACUUM FULL

VACUUM FULL sperrt die Tabelle exklusiv und kann lange dauern! Nur in Wartungsfenstern durchführen.