Devops Database

Overview

Manage PostgreSQL database operations for the Bestays platform, including Alembic migrations, backups, restoration, and database administration for both development and production environments.

When to Use This Skill

Use this skill when:

• Creating new database migrations (alembic revision)
• Applying migrations (make migrate, alembic upgrade head)
• Rolling back migrations (alembic downgrade)
• Backing up database (development or production)
• Restoring database from backup
• Accessing database shell for queries or inspection
• Troubleshooting database connection or schema issues
• Managing pgvector extension for vector embeddings

Database Configuration

Development Environment

Container: bestays-db-dev Image: postgres:16-alpine Port: localhost:5433 → container:5432 Database: bestays_dev User: bestays_user Password: bestays_password (from .env)

Connection String (from host):

postgresql://bestays_user:bestays_password@localhost:5433/bestays_dev

Connection String (from containers):

postgresql+asyncpg://bestays_user:bestays_password@postgres:5432/bestays_dev

Data Persistence: postgres_data Docker volume (survives make down)

Extensions:

• pgvector - Vector similarity search for embeddings

Production Environment

Container: bestays-db-prod Image: postgres:16-alpine Port: Not exposed (internal network only) Database: From $POSTGRES_DB environment variable User: From $POSTGRES_USER environment variable Password: From $POSTGRES_PASSWORD environment variable

Connection String (from containers):

postgresql+asyncpg://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}

Data Persistence: postgres_data Docker volume + backup directory mounted at /backups

Alembic Migrations

Alembic is used for database schema versioning and migrations.

Configuration

Location: apps/server/alembic/ Config File: apps/server/alembic.ini Migrations Directory: apps/server/alembic/versions/ Environment Script: apps/server/alembic/env.py

File Naming Template: YYYYMMDD_HHMM-{rev}_{slug}.py Example: 20251030_1045-abc123def456_add_users_table.py

Creating Migrations

Auto-Generate Migration (Recommended)

From host machine:

# 1. Start services if not running
make dev

# 2. Enter backend container
make shell-server

# 3. Auto-generate migration from model changes
alembic revision --autogenerate -m "Add users table"

# 4. Exit container
exit

What it does:

• Compares current models (SQLAlchemy) with database schema
• Generates migration script with detected changes
• Creates file in apps/server/alembic/versions/

Review the generated migration:

# Check the latest migration file
ls -lt apps/server/alembic/versions/ | head -2
cat apps/server/alembic/versions/YYYYMMDD_HHMM-*_add_users_table.py

⚠️ Important: Always review auto-generated migrations! They may:

• Miss some changes (indexes, constraints)
• Include unintended changes
• Need manual data migrations

Manual Migration

When to use manual migrations:

• Complex data transformations
• Renaming columns (Alembic can't detect renames)
• Adding data (seed data, lookup tables)
• Custom SQL operations

Create empty migration:

make shell-server
alembic revision -m "Migrate user data"
exit

Edit the migration file:

# apps/server/alembic/versions/YYYYMMDD_HHMM-xxx_migrate_user_data.py
def upgrade() -> None:
    # Add your migration logic here
    op.execute("""
        UPDATE users
        SET email_verified = true
        WHERE created_at < '2025-01-01'
    """)

def downgrade() -> None:
    # Add rollback logic here
    op.execute("""
        UPDATE users
        SET email_verified = false
        WHERE created_at < '2025-01-01'
    """)

Applying Migrations

Development

Apply all pending migrations:

make migrate

Equivalent to:

make shell-server
alembic upgrade head
exit

Apply specific number of migrations:

make shell-server
alembic upgrade +1    # Apply next migration
alembic upgrade +2    # Apply next 2 migrations
exit

Apply to specific revision:

make shell-server
alembic upgrade abc123def456
exit

Production

Apply migrations in production:

# SSH into production server
ssh user@production-server

# Navigate to project
cd /path/to/bestays-monorepo

# Apply migrations (with production compose file)
docker-compose -f docker-compose.prod.yml exec backend alembic upgrade head

Best Practice: Test migrations in staging environment first!

Rolling Back Migrations

Downgrade one migration:

make shell-server
alembic downgrade -1
exit

Downgrade to specific revision:

make shell-server
alembic downgrade abc123def456
exit

Downgrade all (back to empty database):

make shell-server
alembic downgrade base
exit

⚠️ Warning: Downgrading can cause data loss! Always backup first.

Migration History

View current revision:

make shell-server
alembic current
exit

View migration history:

make shell-server
alembic history
exit

View pending migrations:

make shell-server
alembic history --verbose
exit

Database Backups

Creating Backups

Development backup:

docker-compose -f docker-compose.dev.yml exec postgres \
  pg_dump -U bestays_user bestays_dev > backups/dev-backup-$(date +%Y%m%d-%H%M%S).sql

Production backup:

docker-compose -f docker-compose.prod.yml exec postgres \
  pg_dump -U $POSTGRES_USER $POSTGRES_DB > backups/prod-backup-$(date +%Y%m%d-%H%M%S).sql

Compressed backup:

# Add | gzip > file.sql.gz to compress
docker-compose -f docker-compose.dev.yml exec postgres \
  pg_dump -U bestays_user bestays_dev | gzip > backups/backup.sql.gz

Database Restoration

Restore Development Database

From SQL dump:

# 1. Stop services
make down

# 2. Start only PostgreSQL
docker-compose -f docker-compose.dev.yml up -d postgres

# 3. Wait for PostgreSQL to be ready
sleep 5

# 4. Drop and recreate database
docker-compose -f docker-compose.dev.yml exec postgres psql -U bestays_user -d postgres -c "DROP DATABASE IF EXISTS bestays_dev;"
docker-compose -f docker-compose.dev.yml exec postgres psql -U bestays_user -d postgres -c "CREATE DATABASE bestays_dev OWNER bestays_user;"

# 5. Restore from backup
cat backups/dev-backup-20251030-104530.sql | docker-compose -f docker-compose.dev.yml exec -T postgres psql -U bestays_user -d bestays_dev

# 6. Start all services
make up

From compressed backup:

gunzip -c backups/dev-backup-20251030-104530.sql.gz | \
  docker-compose -f docker-compose.dev.yml exec -T postgres psql -U bestays_user -d bestays_dev

Restore Production Database

⚠️ CRITICAL: Always test restoration procedure in staging first!

Production restoration:

# 1. SSH into production server
ssh user@production-server

# 2. Put site in maintenance mode (if possible)
# ... maintenance mode steps ...

# 3. Stop backend to prevent writes
docker-compose -f docker-compose.prod.yml stop backend

# 4. Create backup of current state (safety!)
docker-compose -f docker-compose.prod.yml exec postgres \
  pg_dump -U $POSTGRES_USER $POSTGRES_DB > backups/pre-restore-backup-$(date +%Y%m%d-%H%M%S).sql

# 5. Drop and recreate database
docker-compose -f docker-compose.prod.yml exec postgres psql -U $POSTGRES_USER -d postgres -c "DROP DATABASE IF EXISTS $POSTGRES_DB;"
docker-compose -f docker-compose.prod.yml exec postgres psql -U $POSTGRES_USER -d postgres -c "CREATE DATABASE $POSTGRES_DB OWNER $POSTGRES_USER;"

# 6. Restore from backup
cat backups/prod-backup-20251030-020000.sql | \
  docker-compose -f docker-compose.prod.yml exec -T postgres psql -U $POSTGRES_USER -d $POSTGRES_DB

# 7. Verify restoration
docker-compose -f docker-compose.prod.yml exec postgres psql -U $POSTGRES_USER -d $POSTGRES_DB -c "\dt"

# 8. Start backend
docker-compose -f docker-compose.prod.yml start backend

# 9. Remove maintenance mode
# ... remove maintenance mode ...

Database Shell Access

psql (PostgreSQL Shell)

Development:

make shell-db

Production:

docker-compose -f docker-compose.prod.yml exec postgres psql -U $POSTGRES_USER -d $POSTGRES_DB

Common psql commands:

-- List tables
\dt

-- Describe table structure
\d users
\d+ users  -- With detailed info

-- List indexes
\di

-- List views
\dv

-- Show table sizes
\dt+

-- List functions
\df

-- List schemas
\dn

-- Execute SQL from file
\i /path/to/file.sql

-- Output to file
\o /tmp/output.txt
SELECT * FROM users;
\o

-- Quit
\q

Running SQL Queries

Quick query from host:

# Development
docker-compose -f docker-compose.dev.yml exec postgres \
  psql -U bestays_user -d bestays_dev -c "SELECT COUNT(*) FROM users;"

# Production
docker-compose -f docker-compose.prod.yml exec postgres \
  psql -U $POSTGRES_USER -d $POSTGRES_DB -c "SELECT COUNT(*) FROM users;"

Query from SQL file:

cat query.sql | docker-compose -f docker-compose.dev.yml exec -T postgres \
  psql -U bestays_user -d bestays_dev

pgvector Extension

The database includes pgvector extension for vector similarity search (LLM embeddings).

Verify pgvector Installation

-- In psql
\dx
-- Should show: pgvector extension

Using pgvector

Create table with vector column:

CREATE TABLE embeddings (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)  -- OpenAI embedding dimension
);

-- Create index for similarity search
CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops);

Insert vectors:

INSERT INTO embeddings (content, embedding)
VALUES ('Sample text', '[0.1, 0.2, 0.3, ...]');

Similarity search:

-- Find similar vectors
SELECT content, embedding <=> '[0.1, 0.2, 0.3, ...]' AS distance
FROM embeddings
ORDER BY embedding <=> '[0.1, 0.2, 0.3, ...]'
LIMIT 10;

Troubleshooting

Migration Fails

Error: "Target database is not up to date"

# Check current revision
make shell-server
alembic current

# Check history
alembic history

# Stamp database to specific revision if needed
alembic stamp head

exit

Error: "Can't locate revision"

# Verify migration files exist
ls apps/server/alembic/versions/

# Regenerate migration if needed
make shell-server
alembic revision --autogenerate -m "Regenerate migration"
exit

Connection Refused

Check if database is running:

make status

Check database health:

make check

Check connection from backend:

make shell-server
echo $DATABASE_URL
python -c "import asyncpg; print('Connection OK')"
exit

Database Locked

Error: "Database is locked" or "Could not access database"

Solution: Stop all connections:

# Restart database
make restart-db

# Or restart all services
make restart

Backup/Restore Fails

Error: "Permission denied"

Solution: Check file permissions:

ls -l backups/
chmod 644 backups/*.sql

Error: "Database does not exist"

Solution: Create database first:

docker-compose -f docker-compose.dev.yml exec postgres \
  psql -U bestays_user -d postgres -c "CREATE DATABASE bestays_dev OWNER bestays_user;"

Related Skills

• devops-local-dev - Docker Compose orchestration and service management

Key Files

• apps/server/alembic.ini - Alembic configuration
• apps/server/alembic/versions/ - Migration files
• apps/server/alembic/env.py - Migration environment
• docker-compose.dev.yml - Development database config
• docker-compose.prod.yml - Production database config

Quick Reference

Common Tasks

Create migration:

make shell-server
alembic revision --autogenerate -m "Description"
exit

Apply migrations:

make migrate

Backup development database:

docker-compose -f docker-compose.dev.yml exec postgres \
  pg_dump -U bestays_user bestays_dev | gzip > backups/backup-$(date +%Y%m%d-%H%M%S).sql.gz

Access database shell:

make shell-db

Check migration status:

make shell-server
alembic current
alembic history
exit

Rollback one migration:

make shell-server
alembic downgrade -1
exit