Database Setup and Migrations

​

Database Requirements

​

PostgreSQL Version

​

Local Development

# Using notifications-local (recommended)
# Check https://github.com/alphagov/notifications-local

# Or install via Homebrew
brew install postgresql@15
brew services start postgresql@15

​

Database Setup

​

Initial Database Creation

# Create the database
createdb notification_api

# Run migrations
flask db upgrade

​

Connection Configuration

# Primary database (read/write)
export SQLALCHEMY_DATABASE_URI='postgresql+psycopg2://user:password@localhost:5432/notification_api'

# Bulk/read replica (optional, falls back to primary)
export SQLALCHEMY_DATABASE_URI_BULK='postgresql+psycopg2://replica1:5432,replica2:5432/notification_api'

​

Database Architecture

​

Dual Database Binds

1. Default bind - Primary database for transactional queries
2. Bulk bind - Read replica for analytics and reporting queries

• Sets readonly = True on connections
• Uses replica-specific timeouts and worker settings
• Falls back to primary if no replica is configured

​

Connection Pool Settings

SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_size": 5,              # Connections per process
    "pool_timeout": 30,          # Seconds to wait for connection
    "pool_recycle": 300,         # Recycle connections after 5 minutes
    "connect_args": {
        "connect_timeout": "5",  # Connection timeout
        "tcp_user_timeout": "5000",  # TCP timeout (milliseconds)
        "options": "-c statement_timeout=1200000"  # 20 minute fallback
    }
}

​

Performance Configuration

• DATABASE_STATEMENT_TIMEOUT_MS - Default: 1,200,000ms (20 minutes)
• DATABASE_MAX_PARALLEL_WORKERS - Optional, uses database default if not set
• Optimized for short transactional queries

• DATABASE_STATEMENT_TIMEOUT_REPLICA_MS - Default: 1,200,000ms (20 minutes)
• DATABASE_MAX_PARALLEL_WORKERS_REPLICA - Optional
• Allows longer-running analytical queries

​

Connection Event Handlers

# At connection time:
1. Check if connected to primary or replica (pg_is_in_recovery())
2. Set appropriate statement_timeout
3. Set max_parallel_workers_per_gather (if configured)
4. Record inet_server_addr for metrics
5. Set readonly mode for bulk bind

​

Database Migrations

​

Migration System

​

Creating Migrations

# Auto-generate migration from model changes
flask db migrate -m "Description of changes"

# Manually create migration
flask db revision -m "Description of changes"

# Edit the generated migration file
vim migrations/versions/<revision>.py

# Rename to sequential number (project convention)
mv migrations/versions/<hash>_description.py migrations/versions/0123_description.py

​

Running Migrations

# Apply all pending migrations
flask db upgrade

# Apply specific migration
flask db upgrade <revision>

# Downgrade to previous version
flask db downgrade

# Downgrade to specific version
flask db downgrade <revision>

# Show current version
flask db current

# Show migration history
flask db history

​

Migration in Docker

# Using entrypoint
docker run notifications-api migration

# Using make
make run-migrations-with-docker

​

Migration Safety Features

​

1. Advisory Locks

# Session-level lock using "alembic" as identifier
lock_id = struct.unpack(">q", struct.pack("8s", b"alembic"))[0]
connection.execute("SELECT pg_advisory_lock(:id)", {"id": lock_id})

​

2. Lock Timeout

connection.execute("SET lock_timeout = 1000")  # 1000ms

​

3. Retry Logic

retry_on_lock_error(
    func=run_migrations_online,
    max_retries=10,
    delay_secs=10
)

​

4. Transaction Per Migration

context.configure(
    transaction_per_migration=True
)

​

5. Current Head Tracking

# Writes to migrations/.current-alembic-head
if engine.url.database == "notification_api":
    with open(".current-alembic-head", "w") as f:
        f.write(context.get_head_revision() + "\n")

​

Testing Migrations

# Test migration downgrade
./scripts/test_sql_migration_downgrade.sh

# Lint migrations locally
./scripts/lint_sql_migrations_locally.sh

# Lint migrations in CI
./scripts/lint_sql_migrations_ci.sh

​

Checking for Pending Migrations

# Check if there are new migrations
make check-if-migrations-to-run

# This runs:
python3 scripts/check_if_new_migration.py

​

Production Migration Strategy

​

Pre-Deployment

1. Test migrations thoroughly in staging environment
2. Check lock requirements - Ensure migrations can acquire locks
3. Review migration SQL - Verify generated DDL is safe
4. Plan for rollback - Have downgrade script ready

​

Deployment Sequence

1. Run migrations first (before deploying code)
   flask db upgrade
   
2. Verify migration success
   flask db current
   
3. Deploy application code
4. Monitor for errors

​

Zero-Downtime Migrations

1. Additive changes first - Add columns/tables without removing old ones
2. Deploy code that works with both old and new schema
3. Backfill data if needed
4. Remove old schema in subsequent migration after code is deployed

​

High-Risk Migrations

1. Run during low-traffic period
2. Increase lock_timeout if appropriate
3. Consider manual execution rather than automatic deployment
4. Have rollback plan ready

​

Database Maintenance

​

Test Database Management

# Drop all test databases
make drop-test-dbs

# Drop test databases in Docker
make drop-test-dbs-in-docker

• test_notification_api_master - Master test database
• test_notification_api_gw0, test_notification_api_gw1, etc. - Worker databases

​

Monitoring

• db_connection_total_connected - Total connections held by server
• db_connection_total_checked_out - Connections currently in use
• db_connection_open_duration_seconds - How long connections are held

• bind - “default” or “bulk”
• inet_server_addr - Actual database server IP (primary vs replica)
• method, host, path - Request context

​

Backup and Recovery

1. Use PostgreSQL continuous archiving (WAL archiving)
2. Take regular base backups
3. Retain backups according to retention policy
4. Test restore procedures regularly

# Restore base backup
pg_restore -d notification_api /path/to/backup

# Recover to specific timestamp
recovery_target_time = '2024-03-03 14:30:00'

​

Troubleshooting

​

Connection Pool Exhausted

1. Increase SQLALCHEMY_POOL_SIZE
2. Check for connection leaks (unclosed sessions)
3. Verify pool_recycle is set to prevent stale connections
4. Monitor db_connection_total_checked_out metric

​

Statement Timeout

1. Optimize slow queries
2. Increase DATABASE_STATEMENT_TIMEOUT_MS if appropriate
3. Use bulk bind for long-running analytics queries
4. Add indexes to improve query performance

​

Lock Timeout During Migrations

1. Run migration during low-traffic period
2. Wait for automatic retry (up to 10 attempts)
3. Manually terminate blocking queries if safe
4. Consider increasing lock_timeout for specific migration

​

Replica Lag

1. Monitor replication lag
2. Use primary database for time-sensitive queries
3. Increase replica resources if consistently lagging
4. Check network connectivity between primary and replica

​

Connection Refused

1. Verify database is running: pg_isready -h <host> -p <port>
2. Check SQLALCHEMY_DATABASE_URI is correct
3. Verify network connectivity and firewall rules
4. Check PostgreSQL pg_hba.conf allows connections
5. Verify credentials are correct