Database Maintenance

Rack Gateway uses PostgreSQL for persistent storage. This guide covers database maintenance tasks including migrations, backups, and troubleshooting.

Database Commands

The rack-gateway-api binary includes maintenance subcommands for database operations.

Apply Migrations

Apply pending database migrations:

rack-gateway-api migrate

This command:

Connects using DATABASE_URL or PG* environment variables
Applies migrations in internal/gateway/db/migrations/
Uses schema_migrations table to track applied migrations
Is safe to run repeatedly (idempotent)

Reset Database

Reset the database to a clean state (development only):

# Required safety guards
export RESET_RACK_GATEWAY_DATABASE=DELETE_ALL_DATA
export DEV_MODE=true

rack-gateway-api reset-db

Safety guards:

RESET_RACK_GATEWAY_DATABASE=DELETE_ALL_DATA - Must be set exactly
Database must be marked as development, OR set DISABLE_DATABASE_ENVIRONMENT_CHECK=1
For fresh databases, DEV_MODE=true is required

Check Migration Status

Verify which migrations have been applied:

# Connect to database and check
psql $DATABASE_URL -c "SELECT * FROM schema_migrations ORDER BY version;"

Backup Procedures

Automated Backups (AWS RDS)

If using AWS RDS, configure automated backups:

# Terraform configuration
resource "aws_db_instance" "gateway" {
  # Automated backups
  backup_retention_period = 30
  backup_window           = "03:00-04:00"

  # Point-in-time recovery
  copy_tags_to_snapshot = true
}

Manual Backup

Create a manual backup using pg_dump:

# Full database backup
pg_dump $DATABASE_URL > rack_gateway_backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump $DATABASE_URL | gzip > rack_gateway_backup_$(date +%Y%m%d).sql.gz

Restore from Backup

Stop the gateway service
Terminal window
```
convox scale gateway --count 0
```

Restore the database

psql $DATABASE_URL < rack_gateway_backup_20240115.sql

Run any pending migrations
Terminal window
```
rack-gateway-api migrate
```
Restart the service
Terminal window
```
convox scale gateway --count 2
```

Schema Overview

The database contains these main tables:

Table	Purpose
`users`	User accounts from OAuth
`user_sessions`	Active user sessions
`api_tokens`	API tokens for CI/CD
`mfa_methods`	User MFA enrollments
`audit.audit_event`	Immutable audit log entries
`mfa_backup_codes`	MFA backup codes
`trusted_devices`	Trusted devices for step-up
`settings`	Gateway configuration
`deploy_approval_requests`	Pending deploy approvals

Key Relationships

Maintenance Tasks

Cleanup Old Sessions

Sessions expire automatically, but you can manually clean up:

-- Delete sessions older than 30 days
DELETE FROM user_sessions
WHERE created_at < NOW() - INTERVAL '30 days';

Cleanup Old Audit Logs

Audit log retention is policy-driven. WORM anchors use AUDIT_ANCHOR_RETENTION_DAYS.

-- Check audit log volume
SELECT COUNT(*), DATE(timestamp) as day
FROM audit.audit_event
GROUP BY DATE(timestamp)
ORDER BY day DESC
LIMIT 10;

-- Manual cleanup (if needed)
DELETE FROM audit.audit_event
WHERE timestamp < NOW() - INTERVAL '400 days';

Vacuum and Analyze

Regular maintenance improves performance:

-- Analyze query statistics
ANALYZE;

-- Reclaim space from deleted rows
VACUUM ANALYZE;

For RDS, this happens automatically. For self-managed PostgreSQL, schedule regular maintenance.

Connection Management

Connection Pool Settings

Configure connection pooling:

DB_MAX_OPEN_CONNS=25    # Maximum concurrent connections
DB_MAX_IDLE_CONNS=5     # Idle connections to keep warm
DB_CONN_MAX_LIFETIME=30m # Connection lifetime
DB_CONN_MAX_IDLE_TIME=10m # Idle timeout

Monitoring Connections

-- Current active connections
SELECT count(*) FROM pg_stat_activity
WHERE datname = 'rack_gateway';

-- Connection details
SELECT usename, application_name, client_addr, state
FROM pg_stat_activity
WHERE datname = 'rack_gateway';

Troubleshooting

Migration Failures

If a migration fails:

Check the error message for the specific issue
Fix the underlying problem (permissions, constraints, etc.)
Re-run rack-gateway migrate

Migrations are transactional—partial failures won’t leave the database in an inconsistent state.

Connection Issues

If the gateway can’t connect to the database:

# Test connectivity
psql $DATABASE_URL -c "SELECT 1;"

# Check if host is reachable
nc -zv $PGHOST $PGPORT

# Verify SSL settings
psql "$DATABASE_URL?sslmode=require" -c "SELECT 1;"

Slow Queries

Identify slow queries:

-- Enable query logging (if not already)
ALTER SYSTEM SET log_min_duration_statement = '1000';  -- Log queries > 1s

-- Check for long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Disk Space

Check database size:

SELECT pg_size_pretty(pg_database_size('rack_gateway'));

-- Size by table
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Docker Development

When using the Docker development stack:

Migrate

task docker:db:migrate

Reset

task docker:db:reset

Connect to Database

docker exec -it rack-gateway-postgres-1 psql -U postgres -d gateway_dev

Best Practices

Always run migrations before starting the app - Include in deployment scripts
Use automated backups - Configure RDS backup retention
Monitor connection usage - Prevent connection exhaustion
Keep audit logs - Don’t delete before retention period expires
Test restore procedures - Verify backups actually work