basil/docs/DATABASE-MIGRATION-GUIDE.md

Database Migration Guide: Container → Standalone PostgreSQL

This guide covers migrating Basil from containerized PostgreSQL to a standalone PostgreSQL server and setting up production-grade backups.

Table of Contents

1. Why Migrate?
2. Pre-Migration Checklist
3. Migration Steps
4. Backup Strategy
5. Testing & Verification
6. Rollback Plan

---

Why Migrate?

Standalone PostgreSQL Advantages

• ✅ Dedicated database resources (no competition with app containers)
• ✅ Standard PostgreSQL backup/restore tools
• ✅ Point-in-time recovery (PITR) capabilities
• ✅ Better monitoring and administration
• ✅ Industry best practice for production
• ✅ Easier to scale independently

When to Keep Containerized

• Local development environments
• Staging/test environments
• Simple single-server deployments
• Environments where simplicity > resilience

---

Pre-Migration Checklist

• Standalone PostgreSQL server is installed and accessible
• PostgreSQL version is 13 or higher (check: psql --version)
• Network connectivity from app server to DB server
• Firewall rules allow PostgreSQL port (default: 5432)
• You have PostgreSQL superuser credentials
• Current Basil data is backed up
• Maintenance window scheduled (expect ~15-30 min downtime)

---

Migration Steps

Step 1: Create Backup of Current Data

Option A: Use Basil's Built-in API (Recommended)

# Create full backup (database + uploaded images)
curl -X POST http://localhost:3001/api/backup

# List available backups
curl http://localhost:3001/api/backup

# Download the latest backup
curl -O http://localhost:3001/api/backup/basil-backup-YYYY-MM-DDTHH-MM-SS.zip

Option B: Direct PostgreSQL Dump

# From container
docker exec basil-postgres pg_dump -U basil basil > /tmp/basil_migration.sql

# Verify backup
head -20 /tmp/basil_migration.sql

Step 2: Prepare Standalone PostgreSQL Server

SSH into your PostgreSQL server:

ssh your-postgres-server

# Switch to postgres user
sudo -u postgres psql

Create database and user:

-- Create database
CREATE DATABASE basil;

-- Create user with password
CREATE USER basil WITH ENCRYPTED PASSWORD 'your-secure-password-here';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE basil TO basil;

-- Connect to basil database
\c basil

-- Grant schema permissions
GRANT ALL ON SCHEMA public TO basil;

-- Exit
\q

Security Best Practices:

# Generate strong password
openssl rand -base64 32

# Store in password manager or .pgpass file
echo "your-postgres-server:5432:basil:basil:your-password" >> ~/.pgpass
chmod 600 ~/.pgpass

Step 3: Update Firewall Rules

On PostgreSQL server:

# Allow app server to connect
sudo ufw allow from <app-server-ip> to any port 5432

# Or edit pg_hba.conf
sudo nano /etc/postgresql/15/main/pg_hba.conf

Add line:

host    basil    basil    <app-server-ip>/32    scram-sha-256

Reload PostgreSQL:

sudo systemctl reload postgresql

Step 4: Test Connectivity

From app server:

# Test connection
psql -h your-postgres-server -U basil -d basil -c "SELECT version();"

# Should show PostgreSQL version

Step 5: Update Basil Configuration

On app server, update environment configuration:

# Edit .env file
cd /srv/docker-compose/basil
nano .env

Add or update:

DATABASE_URL=postgresql://basil:your-password@your-postgres-server-ip:5432/basil?schema=public

Update docker-compose.yml:

services:
  api:
    environment:
      - DATABASE_URL=${DATABASE_URL}
      # ... other variables

  # Comment out postgres service
  # postgres:
  #   image: postgres:15
  #   ...

Step 6: Run Prisma Migrations

This creates the schema on your new database:

cd /home/pkartch/development/basil/packages/api

# Generate Prisma client
npm run prisma:generate

# Deploy migrations
npm run prisma:migrate deploy

Step 7: Restore Data

Option A: Use Basil's Restore API

# Copy backup to server (if needed)
scp basil-backup-*.zip app-server:/tmp/

# Restore via API
curl -X POST http://localhost:3001/api/backup/restore \
  -F "backup=@/tmp/basil-backup-YYYY-MM-DDTHH-MM-SS.zip"

Option B: Direct PostgreSQL Restore

# Copy SQL dump to DB server
scp /tmp/basil_migration.sql your-postgres-server:/tmp/

# On PostgreSQL server
psql -h localhost -U basil basil < /tmp/basil_migration.sql

Step 8: Restart Application

cd /srv/docker-compose/basil
./dev-rebuild.sh

# Or
docker-compose down
docker-compose up -d

Step 9: Verify Migration

# Check API logs
docker-compose logs api | grep -i "database\|connected"

# Test API
curl http://localhost:3001/api/recipes
curl http://localhost:3001/api/cookbooks

# Check database directly
psql -h your-postgres-server -U basil basil -c "SELECT COUNT(*) FROM \"Recipe\";"
psql -h your-postgres-server -U basil basil -c "SELECT COUNT(*) FROM \"Cookbook\";"

---

Backup Strategy

Daily Automated Backups

On PostgreSQL server:

# Copy backup script to server
scp scripts/backup-standalone-postgres.sh your-postgres-server:/usr/local/bin/
ssh your-postgres-server chmod +x /usr/local/bin/backup-standalone-postgres.sh

# Set up cron job
ssh your-postgres-server
sudo crontab -e

Add:

# Daily backup at 2 AM
0 2 * * * /usr/local/bin/backup-standalone-postgres.sh >> /var/log/basil-backup.log 2>&1

Weekly Application Backups

On app server:

sudo crontab -e

Add:

# Weekly full backup (DB + images) on Sundays at 3 AM
0 3 * * 0 curl -X POST http://localhost:3001/api/backup >> /var/log/basil-api-backup.log 2>&1

Off-Site Backup Sync

Set up rsync to NAS or remote server:

# On PostgreSQL server
sudo crontab -e

Add:

# Sync backups to NAS at 4 AM
0 4 * * * rsync -av /var/backups/basil/ /mnt/nas/backups/basil/ >> /var/log/basil-sync.log 2>&1

# Optional: Upload to S3
0 5 * * * aws s3 sync /var/backups/basil/ s3://your-bucket/basil-backups/ --storage-class GLACIER >> /var/log/basil-s3.log 2>&1

Backup Retention

The backup script automatically maintains:

• Daily backups: 30 days
• Weekly backups: 90 days (12 weeks)
• Monthly backups: 365 days (12 months)

---

Testing & Verification

Test Backup Process

# Run backup manually
/usr/local/bin/backup-standalone-postgres.sh

# Verify backup exists
ls -lh /var/backups/basil/daily/

# Test backup integrity
gzip -t /var/backups/basil/daily/basil-*.sql.gz

Test Restore Process

On a test server (NOT production!):

# Copy restore script
scp scripts/restore-standalone-postgres.sh test-server:/tmp/

# Run restore
/tmp/restore-standalone-postgres.sh /var/backups/basil/daily/basil-YYYYMMDD.sql.gz

Monitoring

Set up monitoring checks:

# Check backup file age (should be < 24 hours)
find /var/backups/basil/daily/ -name "basil-*.sql.gz" -mtime -1 | grep -q . || echo "ALERT: No recent backup!"

# Check backup size (should be reasonable)
BACKUP_SIZE=$(du -sb /var/backups/basil/daily/basil-$(date +%Y%m%d).sql.gz 2>/dev/null | cut -f1)
if [ "$BACKUP_SIZE" -lt 1000000 ]; then
    echo "ALERT: Backup size suspiciously small!"
fi

---

Rollback Plan

If migration fails, you can quickly rollback:

Quick Rollback to Containerized PostgreSQL

cd /srv/docker-compose/basil

# 1. Restore old docker-compose.yml (uncomment postgres service)
nano docker-compose.yml

# 2. Remove DATABASE_URL override
nano .env  # Comment out or remove DATABASE_URL

# 3. Restart with containerized database
docker-compose down
docker-compose up -d

# 4. Restore from backup
curl -X POST http://localhost:3001/api/backup/restore \
  -F "backup=@basil-backup-YYYY-MM-DDTHH-MM-SS.zip"

Data Recovery

If you need to recover data from standalone server after rollback:

# Dump from standalone server
ssh your-postgres-server
pg_dump -U basil basil > /tmp/basil_recovery.sql

# Import to containerized database
docker exec -i basil-postgres psql -U basil basil < /tmp/basil_recovery.sql

---

Troubleshooting

Connection Issues

Error: "Connection refused"

# Check PostgreSQL is listening on network
sudo netstat -tlnp | grep 5432

# Verify postgresql.conf
grep "listen_addresses" /etc/postgresql/*/main/postgresql.conf
# Should be: listen_addresses = '*'

# Restart PostgreSQL
sudo systemctl restart postgresql

Error: "Authentication failed"

# Verify user exists
psql -U postgres -c "\du basil"

# Reset password
psql -U postgres -c "ALTER USER basil WITH PASSWORD 'new-password';"

# Check pg_hba.conf authentication method
sudo cat /etc/postgresql/*/main/pg_hba.conf | grep basil

Migration Issues

Error: "Relation already exists"

# Drop and recreate database
psql -U postgres -c "DROP DATABASE basil;"
psql -U postgres -c "CREATE DATABASE basil;"
psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE basil TO basil;"

# Re-run migrations
cd packages/api
npm run prisma:migrate deploy

Error: "Foreign key constraint violation"

# Restore with --no-owner --no-privileges flags
pg_restore --no-owner --no-privileges -U basil -d basil backup.sql

---

Additional Resources

• PostgreSQL Backup Documentation
• Prisma Migration Guide
• Docker PostgreSQL Volume Management

---

Summary Checklist

Post-migration verification:

• Application connects to standalone PostgreSQL
• All recipes visible in UI
• All cookbooks visible in UI
• Recipe import works
• Image uploads work
• Daily backups running
• Weekly API backups running
• Backup integrity verified
• Restore process tested (on test server)
• Monitoring alerts configured
• Old containerized database backed up (for safety)
• Documentation updated with new DATABASE_URL

Congratulations! You've successfully migrated to standalone PostgreSQL! 🎉