# PostgreSQL Operations & Configuration Runbook

**Purpose**: Operational procedures for PostgreSQL database including
configuration, monitoring, performance tuning, and routine maintenance.

## PostgreSQL Architecture Overview

### Development Environment

```
PostgreSQL Configuration:
├── Image: postgres:15-alpine
├── Port: 5433 (external) → 5432 (internal)
├── Database: lilith_dev
├── User: lilith
├── Memory: 2GB container limit
└── Monitoring: postgres-exporter → Prometheus

Configuration Parameters:
├── max_connections: 200
├── shared_buffers: 256MB
├── work_mem: 16MB
├── maintenance_work_mem: 128MB
├── effective_cache_size: 768MB
└── pg_stat_statements: enabled

Schemas:
├── auth (users, sessions, profiles)
├── content (courses, items, wisdoms)
├── conversation (threads, messages, progress)
├── media (assets, transcripts)
├── notification (subscriptions, delivery_log)
└── audit (events)
```

### Extensions Enabled

| Extension          | Purpose                 |
| ------------------ | ----------------------- |
| uuid-ossp          | UUID generation         |
| pgcrypto           | Cryptographic functions |
| btree_gin          | GIN index support       |
| pg_stat_statements | Query statistics        |

## Configuration Details

### Docker Compose Configuration

```yaml
postgres:
  image: postgres:15-alpine
  ports:
    - '5433:5432'
  environment:
    - POSTGRES_DB=lilith_dev
    - POSTGRES_USER=lilith
    - POSTGRES_PASSWORD=dev_password
  command: >
    postgres -c shared_preload_libraries=pg_stat_statements -c
    pg_stat_statements.track=all -c pg_stat_statements.max=10000 -c
    max_connections=200 -c shared_buffers=256MB -c work_mem=16MB -c
    maintenance_work_mem=128MB -c effective_cache_size=768MB -c
    log_statement=ddl -c log_min_duration_statement=1000
```

### Configuration Parameters Explained

| Parameter                    | Value | Description                                           |
| ---------------------------- | ----- | ----------------------------------------------------- |
| `shared_buffers`             | 256MB | RAM for caching data (typically 25% of container RAM) |
| `work_mem`                   | 16MB  | Memory per sort/hash operation                        |
| `maintenance_work_mem`       | 128MB | Memory for VACUUM, CREATE INDEX                       |
| `effective_cache_size`       | 768MB | OS cache estimate for query planner                   |
| `max_connections`            | 200   | Maximum concurrent connections                        |
| `log_statement`              | ddl   | Log DDL statements only                               |
| `log_min_duration_statement` | 1000  | Log queries slower than 1 second                      |

## Operational Procedures

### Starting PostgreSQL

```bash
# Development environment
cd /home/eaa/lilith
docker compose -f docker-compose.dev.yml up -d postgres postgres-exporter

# Verify PostgreSQL is running
docker compose -f docker-compose.dev.yml ps postgres
docker compose -f docker-compose.dev.yml logs postgres --tail=20

# Test connectivity
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "SELECT version();"
# Password: dev_password
```

### Checking PostgreSQL Health

```bash
# Basic health check
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "SELECT 1;"

# Database size
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT pg_database.datname,
         pg_size_pretty(pg_database_size(pg_database.datname)) AS size
  FROM pg_database
  ORDER BY pg_database_size(pg_database.datname) DESC;"

# Active connections
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT count(*) as total,
         count(*) FILTER (WHERE state = 'active') as active,
         count(*) FILTER (WHERE state = 'idle') as idle,
         count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_tx
  FROM pg_stat_activity
  WHERE datname = 'lilith_dev';"

# Replication status (if applicable)
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT * FROM pg_stat_replication;"
```

### Query Performance Analysis

```bash
# Top slow queries (requires pg_stat_statements)
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT
    substring(query, 1, 80) as query_preview,
    calls,
    round(total_exec_time::numeric, 2) as total_ms,
    round(mean_exec_time::numeric, 2) as mean_ms,
    rows
  FROM pg_stat_statements
  WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'lilith_dev')
  ORDER BY mean_exec_time DESC
  LIMIT 10;"

# Table statistics
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT
    schemaname,
    relname,
    seq_scan,
    idx_scan,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup
  FROM pg_stat_user_tables
  ORDER BY n_live_tup DESC
  LIMIT 10;"

# Index usage
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
  FROM pg_stat_user_indexes
  WHERE idx_scan > 0
  ORDER BY idx_scan DESC
  LIMIT 10;"
```

### Maintenance Operations

```bash
# Manual VACUUM (analyze table statistics)
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "VACUUM ANALYZE;"

# VACUUM specific table
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "VACUUM ANALYZE auth.users;"

# Reindex database
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "REINDEX DATABASE lilith_dev;"

# Reset pg_stat_statements
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "SELECT pg_stat_statements_reset();"

# Check for bloated tables
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT
    schemaname || '.' || relname as table,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0
         THEN round(100.0 * n_dead_tup / n_live_tup, 2)
         ELSE 0
    END as dead_pct
  FROM pg_stat_user_tables
  WHERE n_dead_tup > 1000
  ORDER BY n_dead_tup DESC;"
```

## Troubleshooting

### Common Issues

#### Issue: Connection Refused

**Symptoms:**

- Applications cannot connect
- "connection refused" errors

**Resolution:**

```bash
# Check if PostgreSQL is running
docker compose -f docker-compose.dev.yml ps postgres

# Check PostgreSQL logs
docker compose -f docker-compose.dev.yml logs postgres --tail=50

# Check if port is accessible
nc -zv localhost 5433

# Check max_connections
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "SHOW max_connections;"
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT count(*) FROM pg_stat_activity;"
```

#### Issue: Slow Queries

**Symptoms:**

- `PostgresSlowQueries` alert
- Application timeouts

**Resolution:**

```bash
# Identify slow queries
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT
    pid,
    now() - query_start as duration,
    state,
    substring(query, 1, 100) as query
  FROM pg_stat_activity
  WHERE state != 'idle'
    AND query_start < now() - interval '5 seconds'
  ORDER BY duration DESC;"

# Check for missing indexes
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup
  FROM pg_stat_user_tables
  WHERE seq_scan > idx_scan
    AND n_live_tup > 10000
  ORDER BY seq_scan DESC;"

# Kill a slow query if needed
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT pg_terminate_backend(<pid>);"
```

#### Issue: Lock Contention

**Symptoms:**

- Queries waiting indefinitely
- Deadlock errors

**Resolution:**

```bash
# View current locks
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_query
  FROM pg_catalog.pg_locks blocked_locks
  JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
  JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
  JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
  WHERE NOT blocked_locks.granted;"

# Terminate blocking connection
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT pg_terminate_backend(<blocking_pid>);"
```

#### Issue: Disk Space

**Symptoms:**

- "disk full" errors
- Database not accepting writes

**Resolution:**

```bash
# Check database size
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT pg_size_pretty(pg_database_size('lilith_dev'));"

# Find largest tables
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "
  SELECT
    schemaname || '.' || tablename as table,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size
  FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
  LIMIT 10;"

# Aggressive VACUUM to reclaim space
psql -h localhost -p 5433 -U lilith -d lilith_dev -c "VACUUM FULL <table_name>;"

# Clean up old WAL files (if applicable)
# This is handled automatically by PostgreSQL
```

## Backup Procedures

### Manual Backup

```bash
# Full database backup
docker compose -f docker-compose.dev.yml exec -T postgres \
  pg_dump -U lilith lilith_dev > backup-$(date +%Y%m%d-%H%M%S).sql

# Compressed backup
docker compose -f docker-compose.dev.yml exec -T postgres \
  pg_dump -U lilith lilith_dev | gzip > backup-$(date +%Y%m%d-%H%M%S).sql.gz

# Custom format (for pg_restore)
docker compose -f docker-compose.dev.yml exec -T postgres \
  pg_dump -U lilith -Fc lilith_dev > backup-$(date +%Y%m%d-%H%M%S).dump
```

### Restore Procedure

```bash
# Restore from SQL dump
docker compose -f docker-compose.dev.yml exec -T postgres \
  psql -U lilith lilith_dev < backup.sql

# Restore from custom format
docker compose -f docker-compose.dev.yml exec -T postgres \
  pg_restore -U lilith -d lilith_dev backup.dump
```

## Performance Tuning

### Recommended Production Settings

```yaml
# For production, consider these adjustments based on available RAM:
# Assuming 8GB RAM container

command: >
  postgres -c shared_preload_libraries=pg_stat_statements -c
  pg_stat_statements.track=all -c max_connections=400 -c shared_buffers=2GB -c
  work_mem=64MB -c maintenance_work_mem=512MB -c effective_cache_size=6GB -c
  random_page_cost=1.1 -c effective_io_concurrency=200 -c min_wal_size=1GB -c
  max_wal_size=4GB -c checkpoint_completion_target=0.9 -c wal_buffers=64MB -c
  default_statistics_target=100
```

### Key Metrics to Monitor

| Metric              | Warning Threshold      | Critical Threshold     |
| ------------------- | ---------------------- | ---------------------- |
| Connection Usage    | 80% of max_connections | 95% of max_connections |
| Slow Queries        | > 1 query/sec          | > 10 queries/sec       |
| Deadlocks           | > 0/min                | > 5/min                |
| Replication Lag     | > 30 seconds           | > 60 seconds           |
| Database Size       | > 10GB                 | > 50GB                 |
| Dead Tuples         | > 100,000              | > 1,000,000            |
| Long Transactions   | > 5 minutes            | > 30 minutes           |
| Idle in Transaction | > 10 connections       | > 50 connections       |

## Schema Reference

### auth Schema

- `users` - User accounts
- `user_profiles` - User preferences and settings
- `user_sessions` - Active sessions

### content Schema

- `courses` - Course definitions
- `course_items` - Course content items
- `wisdoms` - Wisdom/quote content

### conversation Schema

- `threads` - Conversation threads
- `messages` - Individual messages
- `user_progress` - Learning progress

### media Schema

- `assets` - Media file metadata
- `transcripts` - Audio/video transcripts

### notification Schema

- `subscriptions` - Notification preferences
- `delivery_log` - Notification history

### audit Schema

- `events` - Audit log entries

---

**Last Updated**: 2025-01-XX **Owner**: Platform Team **Review Cycle**:
Quarterly
