Eve library

PostgreSQL Operations & Configuration Runbook

Runbook

Revision 1

On this page

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

PostgreSQL Architecture Overview#

Development Environment#

text
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#

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