Database Troubleshooting¶

Solving PostgreSQL connection, migration, and performance problems.

Time to read: ~5 min Prerequisites: None

Missing DATABASE_URL¶

Symptoms¶

Error: DATABASE_URL environment variable is required
Backend fails to start
Validation error on startup

Solutions¶

1. Create or update .env file:

# In project root
cat >> .env << 'EOF'
DATABASE_URL=postgresql+asyncpg://security:your_password@localhost:5432/security
EOF

2. For Docker, use container hostname:

DATABASE_URL=postgresql+asyncpg://security:your_password@postgres:5432/security

3. Verify format:

postgresql+asyncpg://user:password@host:port/database

Components:

postgresql+asyncpg:// - Required prefix (async driver)
user:password - Database credentials
host:port - Database server address
database - Database name

Connection Refused¶

Symptoms¶

Error: connection refused
Error: could not connect to server
Health check shows database unhealthy

Diagnosis¶

# Check if PostgreSQL is running
docker compose -f docker-compose.prod.yml ps postgres

# Test connection directly
psql -h localhost -U security -d security

# Check PostgreSQL logs
docker compose -f docker-compose.prod.yml logs postgres

Solutions¶

1. Start PostgreSQL:

docker compose -f docker-compose.prod.yml up -d postgres

2. Check port:

# Default PostgreSQL port
ss -tlnp | grep 5432

3. Check firewall (if remote):

# Allow PostgreSQL port
sudo ufw allow 5432/tcp

4. Check pg_hba.conf allows connections:

For Docker, this is usually handled by the image.

Missing Migrations¶

Symptoms¶

Error: relation "cameras" does not exist
Error: relation "events" does not exist
Tables missing from database

Diagnosis¶

# List existing tables
psql -h localhost -U security -d security -c "\dt"

# Check alembic version
psql -h localhost -U security -d security -c "SELECT * FROM alembic_version;"

Solutions¶

1. Run migrations:

# Using alembic directly
cd backend
alembic upgrade head

# Or in container
docker compose -f docker-compose.prod.yml exec backend alembic upgrade head

2. Create database if needed:

# Connect to PostgreSQL
psql -h localhost -U postgres

# Create database and user
CREATE USER security WITH PASSWORD 'your_password';
CREATE DATABASE security OWNER security;
GRANT ALL PRIVILEGES ON DATABASE security TO security;
\q

Connection Pool Exhausted¶

Symptoms¶

Error: too many connections
Error: connection pool exhausted
Requests hang then timeout

Diagnosis¶

# Check active connections
psql -h localhost -U security -d security -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'security';"

# Check max connections
psql -h localhost -U security -d security -c "SHOW max_connections;"

Solutions¶

1. Increase PostgreSQL connections:

# In postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET max_connections = 200;
# Restart PostgreSQL

2. Check for connection leaks:

Look for:

Unclosed database sessions in code
Long-running transactions
Tests not cleaning up connections

3. Adjust connection pool:

The backend uses SQLAlchemy async pool. Default settings are usually sufficient.

Slow Queries¶

Symptoms¶

API responses slow
Database CPU high
Timeout errors on complex queries

Diagnosis¶

# Enable query logging (temporary)
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_duration_statement = 1000;  # Log queries >1s
SELECT pg_reload_conf();

# Check slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Solutions¶

1. Check indexes exist:

# List indexes
psql -h localhost -U security -d security -c "\di"

Required indexes should include:

events.started_at
events.camera_id
detections.camera_id
detections.detected_at

2. Analyze tables:

psql -h localhost -U security -d security -c "ANALYZE;"

3. Vacuum old data:

psql -h localhost -U security -d security -c "VACUUM ANALYZE;"

4. Run cleanup:

Old data slows queries. Trigger cleanup:

curl -X POST http://localhost:8000/api/system/cleanup

Disk Space¶

Symptoms¶

Error: No space left on device
Database operations fail
Log: could not extend file

Diagnosis¶

# Check disk usage
df -h

# Check database size
psql -h localhost -U security -d security -c "SELECT pg_size_pretty(pg_database_size('security'));"

# Check table sizes
psql -h localhost -U security -d security -c "
SELECT
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC;
"

Solutions¶

1. Run cleanup:

# Preview what would be deleted
curl -X POST "http://localhost:8000/api/system/cleanup?dry_run=true"

# Actually clean up
curl -X POST http://localhost:8000/api/system/cleanup

2. Vacuum to reclaim space:

psql -h localhost -U security -d security -c "VACUUM FULL;"

Warning: VACUUM FULL locks tables. Run during low activity.

3. Reduce retention:

# Default is 30 days
RETENTION_DAYS=14
LOG_RETENTION_DAYS=3

4. Clean PostgreSQL logs:

# Find PostgreSQL data directory
docker compose -f docker-compose.prod.yml exec postgres ls -la /var/lib/postgresql/data/log/

Authentication Errors¶

Symptoms¶

Error: password authentication failed
Error: FATAL: role "security" does not exist
Connection rejected

Solutions¶

1. Verify credentials:

# Test with psql
PGPASSWORD=your_password psql -h localhost -U security -d security

2. Reset password:

psql -h localhost -U postgres -c "ALTER USER security WITH PASSWORD 'new_password';"

3. Create missing role:

psql -h localhost -U postgres -c "CREATE USER security WITH PASSWORD 'your_password';"
psql -h localhost -U postgres -c "CREATE DATABASE security OWNER security;"

Backup and Recovery¶

Create Backup¶

# Full backup
pg_dump -h localhost -U security security > backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump -h localhost -U security security | gzip > backup_$(date +%Y%m%d).sql.gz

Restore Backup¶

# From SQL file
psql -h localhost -U security security < backup_20250115.sql

# From compressed
gunzip -c backup_20250115.sql.gz | psql -h localhost -U security security

Next Steps¶

Connection Issues - Network problems
Troubleshooting Index - Back to symptom index

Database Troubleshooting¶

Missing DATABASE_URL¶

Symptoms¶

Solutions¶

Connection Refused¶

Symptoms¶

Diagnosis¶

Solutions¶

Missing Migrations¶

Symptoms¶

Diagnosis¶

Solutions¶

Connection Pool Exhausted¶

Symptoms¶

Diagnosis¶

Solutions¶

Slow Queries¶

Symptoms¶

Diagnosis¶

Solutions¶

Disk Space¶

Symptoms¶

Diagnosis¶

Solutions¶

Authentication Errors¶

Symptoms¶

Solutions¶

Backup and Recovery¶

Create Backup¶

Restore Backup¶

Next Steps¶

See Also¶