Database Setup & Management

Anleitung zur Einrichtung und Verwaltung der PostgreSQL-Datenbank für die Appiyon-Plattform.

Übersicht

Appiyon nutzt PostgreSQL 16 als primäre Datenbank mit Doctrine ORM für Object-Relational Mapping.

Initial Setup

1. PostgreSQL installieren

Ubuntu/Debian:

bash

sudo apt update
sudo apt install postgresql-16 postgresql-contrib

macOS (Homebrew):

bash

brew install postgresql@16
brew services start postgresql@16

Docker:

bash

docker run --name appiyon-postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=appiyon \
  -p 5432:5432 \
  -d postgres:16

2. User & Database erstellen

bash

# Als postgres User
sudo -u postgres psql

-- User erstellen
CREATE USER appiyonadmin WITH PASSWORD 'secure_password';

-- Database erstellen
CREATE DATABASE symfony OWNER appiyonadmin;

-- Privileges gewähren
GRANT ALL PRIVILEGES ON DATABASE symfony TO appiyonadmin;

-- Schema-Zugriff (PostgreSQL 15+)
\c symfony
GRANT ALL ON SCHEMA public TO appiyonadmin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appiyonadmin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO appiyonadmin;

3. DATABASE_URL konfigurieren

bash

# .env.local
DATABASE_URL="postgresql://appiyonadmin:secure_password@127.0.0.1:5432/symfony?serverVersion=16&charset=utf8"

4. Verbindung testen

bash

php bin/console doctrine:database:create
# Sollte Erfolg melden oder "database already exists"

Datenbank-Schema

Aktueller Stand (2025-10-24)

Tabellen (5):

admins - System-Administratoren
admin_sessions - Admin-Session-Tracking
admin_login_attempts - Login-Versuche & Rate-Limiting
admin_audit_logs - Audit-Trail für Admin-Aktionen
admin_password_reset_tokens - Password-Reset-Tokens

Migrations:

doctrine_migration_versions - Doctrine Migrations Tracking

Schema-Befehle

bash

# Schema-Status validieren
php bin/console doctrine:schema:validate

# Aktuelles Schema anzeigen
php bin/console doctrine:schema:update --dump-sql

# Schema direkt aktualisieren (NICHT für Production!)
php bin/console doctrine:schema:update --force

# Schema komplett neu erstellen
php bin/console doctrine:schema:drop --force --full-database
php bin/console doctrine:schema:create

Migrations

Migration erstellen

bash

# Automatisch via Diff
php bin/console doctrine:migrations:diff

# Manuelle Migration
php bin/console doctrine:migrations:generate

Migration ausführen

bash

# Status anzeigen
php bin/console doctrine:migrations:status

# Alle Migrations ausführen
php bin/console doctrine:migrations:migrate

# Ohne Confirmation
php bin/console doctrine:migrations:migrate --no-interaction

# Einzelne Migration
php bin/console doctrine:migrations:execute DoctrineMigrations\\Infrastructure\\Version0001 --up

Migration rollback

bash

# Letzte Migration rückgängig
php bin/console doctrine:migrations:migrate prev

# Zu bestimmter Version
php bin/console doctrine:migrations:migrate DoctrineMigrations\\Infrastructure\\Version0001

Siehe auch: Migrations Guide

Doctrine Commands

Database Management

bash

# Database erstellen
php bin/console doctrine:database:create

# Database löschen
php bin/console doctrine:database:drop --force

# Database leeren (alle Tabellen löschen)
php bin/console doctrine:schema:drop --force --full-database

Entity Management

bash

# Entity-Info anzeigen
php bin/console doctrine:mapping:info

# Alle Entities auflisten
php bin/console doctrine:mapping:describe Admin

Query Debugging

bash

# SQL-Logging aktivieren
# config/packages/dev/doctrine.yaml
doctrine:
    dbal:
        logging: true
        profiling: true

Queries erscheinen dann im Web Profiler Toolbar.

PostgreSQL-spezifische Features

SERIAL vs IDENTITY

sql

-- Alt (funktioniert, aber veraltet)
id SERIAL PRIMARY KEY

-- Neu (empfohlen)
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

JSON/JSONB

php

#[ORM\Column(type: 'json')]
private array $data;

#[ORM\Column(type: 'jsonb')]  // PostgreSQL-spezifisch, performanter
private array $metadata;

Arrays

php

#[ORM\Column(type: 'simple_array')]
private array $tags;  // Stored as comma-separated

#[ORM\Column(type: 'json')]
private array $tags;  // Stored as JSON (empfohlen)

ULID/UUID Support

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

php

use Symfony\Component\Uid\Ulid;
use Symfony\Component\Uid\Uuid;

#[ORM\Column(type: 'ulid')]
private Ulid $id;

#[ORM\Column(type: 'uuid')]
private Uuid $uuid;

Full-Text Search

sql

-- TSVector Spalte
ALTER TABLE apps ADD COLUMN search_vector tsvector;

-- Index
CREATE INDEX apps_search_idx ON apps USING GIN(search_vector);

-- Trigger für Auto-Update
CREATE TRIGGER apps_search_update
BEFORE INSERT OR UPDATE ON apps
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', name, description);

Performance Optimization

Indices

bash

# Fehlende Indices finden
php bin/console doctrine:migrations:diff

Regelmäßig Queries analysieren und Indices hinzufügen:

php

#[ORM\Index(name: 'idx_created_at', columns: ['created_at'])]
#[ORM\Index(name: 'idx_email', columns: ['email'])]

Query Optimization

N+1 Problem vermeiden:

php

// Schlecht: N+1 Queries
$admins = $repository->findAll();
foreach ($admins as $admin) {
    $admin->getSessions(); // Separate Query pro Admin!
}

// Gut: Mit Join
$admins = $repository->createQueryBuilder('a')
    ->leftJoin('a.sessions', 's')
    ->addSelect('s')
    ->getQuery()
    ->getResult();

Connection Pooling

yaml

# config/packages/doctrine.yaml
doctrine:
    dbal:
        options:
            connect_timeout: 5
            persistent: true

Batch Processing

php

$batchSize = 20;
$i = 0;

foreach ($data as $item) {
    $entity = new Entity($item);
    $em->persist($entity);

    if (($i % $batchSize) === 0) {
        $em->flush();
        $em->clear(); // Detach Entities, freier Speicher
    }
    $i++;
}

$em->flush();
$em->clear();

Backup & Restore

Backup erstellen

bash

# Komplett
pg_dump -U appiyonadmin -h localhost symfony > backup.sql

# Mit Kompression
pg_dump -U appiyonadmin -h localhost symfony | gzip > backup.sql.gz

# Nur Schema
pg_dump -U appiyonadmin -h localhost --schema-only symfony > schema.sql

# Nur Daten
pg_dump -U appiyonadmin -h localhost --data-only symfony > data.sql

# Custom Format (empfohlen für große DBs)
pg_dump -U appiyonadmin -h localhost -Fc symfony > backup.dump

Restore

bash

# SQL-Datei
psql -U appiyonadmin -h localhost symfony < backup.sql

# Compressed
gunzip -c backup.sql.gz | psql -U appiyonadmin -h localhost symfony

# Custom Format
pg_restore -U appiyonadmin -h localhost -d symfony backup.dump

# Mit Drop (löscht bestehende Daten)
pg_restore -U appiyonadmin -h localhost -d symfony --clean backup.dump

Automated Backups

bash

#!/bin/bash
# /usr/local/bin/appiyon-backup.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/var/backups/appiyon"
DB_NAME="symfony"
DB_USER="appiyonadmin"

mkdir -p $BACKUP_DIR

pg_dump -U $DB_USER -Fc $DB_NAME > $BACKUP_DIR/appiyon_$DATE.dump

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

Cron Job:

bash

# Täglich um 2 Uhr nachts
0 2 * * * /usr/local/bin/appiyon-backup.sh

Connection Configuration

doctrine.yaml

yaml

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

        # Connection Options
        driver: 'pdo_pgsql'
        server_version: '16'
        charset: utf8

        # Connection Pool
        options:
            connect_timeout: 5
            persistent: true

        # Logging (nur Dev)
        logging: '%kernel.debug%'
        profiling: '%kernel.debug%'

    orm:
        auto_generate_proxy_classes: true
        enable_lazy_ghost_objects: true
        report_fields_where_declared: true
        validate_xml_mapping: true
        naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        auto_mapping: true

        mappings:
            Infrastructure:
                type: attribute
                is_bundle: false
                dir: '%kernel.project_dir%/src/Appi/Infrastructure/*/Entity'
                prefix: 'App\Appi\Infrastructure'
                alias: Infrastructure
            # ... weitere Layer

Database Monitoring

Connection Status

bash

# Aktive Connections
psql -U postgres -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'symfony';"

# Connection Details
psql -U postgres -c "SELECT pid, usename, application_name, client_addr, state, query FROM pg_stat_activity WHERE datname = 'symfony';"

# Kill Connection
psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'symfony' AND pid <> pg_backend_pid();"

Table Sizes

sql

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Slow Queries

sql

-- pg_stat_statements Extension aktivieren
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Testing Database

Separate Test-Database

bash

# .env.test
DATABASE_URL="postgresql://appiyonadmin:password@127.0.0.1:5432/symfony_test?serverVersion=16"

Test-Setup

bash

# Test-DB erstellen
php bin/console doctrine:database:create --env=test

# Migrations ausführen
php bin/console doctrine:migrations:migrate --env=test --no-interaction

# Schema validieren
php bin/console doctrine:schema:validate --env=test

Test-Fixtures

php

// tests/Fixtures/AdminFixture.php
class AdminFixture
{
    public static function create(): Admin
    {
        return new Admin(
            'Test Admin',
            AdminEmail::fromString('test@example.com'),
            AdminPassword::fromPlainText('TestPass123!')
        );
    }
}

Troubleshooting

Connection Refused

bash

# PostgreSQL läuft nicht
sudo systemctl start postgresql

# Falsche Credentials
psql -U appiyonadmin -d symfony -h localhost
# Sollte ohne Fehler verbinden

# .env.local prüfen
cat .env.local | grep DATABASE_URL

Permission Denied

sql

-- Als postgres User
GRANT ALL ON SCHEMA public TO appiyonadmin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appiyonadmin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO appiyonadmin;

Schema Out of Sync

bash

# Validieren
php bin/console doctrine:schema:validate

# Diff anzeigen
php bin/console doctrine:schema:update --dump-sql

# Neue Migration erstellen
php bin/console doctrine:migrations:diff

Migration Failed

bash

# Status prüfen
php bin/console doctrine:migrations:status

# Migration-Versions-Tabelle prüfen
psql -U appiyonadmin -d symfony -c "SELECT * FROM doctrine_migration_versions;"

# Manuelle Korrektur (wenn nötig)
# Version als executed markieren
psql -U appiyonadmin -d symfony -c "INSERT INTO doctrine_migration_versions (version, executed_at, execution_time) VALUES ('DoctrineMigrations\\Infrastructure\\Version0001', NOW(), 1);"

Security

Strong Password

bash

# 32-Zeichen random password generieren
openssl rand -base64 32

Connection Limits

sql

-- Max Connections pro User
ALTER ROLE appiyonadmin CONNECTION LIMIT 50;

SSL Connection

bash

DATABASE_URL="postgresql://user:pass@host:5432/db?sslmode=require"

Checkliste

[ ] PostgreSQL 16 installiert
[ ] User appiyonadmin erstellt mit starkem Passwort
[ ] Database symfony erstellt
[ ] Privileges korrekt gesetzt
[ ] DATABASE_URL in .env.local konfiguriert
[ ] Verbindung getestet
[ ] Migrations ausgeführt
[ ] Schema validiert
[ ] Backup-Strategie eingerichtet
[ ] Test-Database konfiguriert

Database Setup & Management ​

Übersicht ​

Initial Setup ​

1. PostgreSQL installieren ​

2. User & Database erstellen ​

3. DATABASE_URL konfigurieren ​

4. Verbindung testen ​

Datenbank-Schema ​

Aktueller Stand (2025-10-24) ​

Schema-Befehle ​

Migrations ​

Migration erstellen ​

Migration ausführen ​

Migration rollback ​

Doctrine Commands ​

Database Management ​

Entity Management ​

Query Debugging ​

PostgreSQL-spezifische Features ​

SERIAL vs IDENTITY ​

JSON/JSONB ​

Arrays ​

ULID/UUID Support ​

Full-Text Search ​

Performance Optimization ​

Indices ​

Query Optimization ​

Connection Pooling ​

Batch Processing ​

Backup & Restore ​

Backup erstellen ​

Restore ​

Automated Backups ​

Connection Configuration ​

doctrine.yaml ​

Database Monitoring ​

Connection Status ​

Table Sizes ​

Slow Queries ​

Testing Database ​

Separate Test-Database ​

Test-Setup ​

Test-Fixtures ​

Troubleshooting ​

Connection Refused ​

Permission Denied ​

Schema Out of Sync ​

Migration Failed ​

Security ​

Strong Password ​

Connection Limits ​

SSL Connection ​

Checkliste ​

Weitere Ressourcen ​

Database Setup & Management

Übersicht

Initial Setup

1. PostgreSQL installieren

2. User & Database erstellen

3. DATABASE_URL konfigurieren

4. Verbindung testen

Datenbank-Schema

Aktueller Stand (2025-10-24)

Schema-Befehle

Migrations

Migration erstellen

Migration ausführen

Migration rollback

Doctrine Commands

Database Management

Entity Management

Query Debugging

PostgreSQL-spezifische Features

SERIAL vs IDENTITY

JSON/JSONB

Arrays

ULID/UUID Support

Full-Text Search

Performance Optimization

Indices

Query Optimization

Connection Pooling

Batch Processing

Backup & Restore

Backup erstellen

Restore

Automated Backups

Connection Configuration

doctrine.yaml

Database Monitoring

Connection Status

Table Sizes

Slow Queries

Testing Database

Separate Test-Database

Test-Setup

Test-Fixtures

Troubleshooting

Connection Refused

Permission Denied

Schema Out of Sync

Migration Failed

Security

Strong Password

Connection Limits

SSL Connection

Checkliste

Weitere Ressourcen