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:
PostgresSlowQueriesalert- 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 accountsuser_profiles- User preferences and settingsuser_sessions- Active sessions
content Schema#
courses- Course definitionscourse_items- Course content itemswisdoms- Wisdom/quote content
conversation Schema#
threads- Conversation threadsmessages- Individual messagesuser_progress- Learning progress
media Schema#
assets- Media file metadatatranscripts- Audio/video transcripts
notification Schema#
subscriptions- Notification preferencesdelivery_log- Notification history
audit Schema#
events- Audit log entries
Last Updated: 2025-01-XX Owner: Platform Team Review Cycle: Quarterly