Database Maintenance¶
Running Migrations¶
Local¶
```bash
Generate a migration after model changes¶
flask db migrate -m "description of change"
Apply migration¶
flask db upgrade
Roll back one migration¶
flask db downgrade ```
Production (Render)¶
Migrations run automatically on deploy via a pre-deploy script, OR manually:
- SSH into Render instance: Render Dashboard → Shell
- Run:
flask db upgrade
Always test migrations on local DB first
Run flask db upgrade on your local SQLite before deploying to production. A failed migration on PostgreSQL may require manual SQL rollback.
Local vs Production DB¶
| Local | Production | |
|---|---|---|
| Engine | SQLite (DSS_local.db) |
PostgreSQL (Render managed) |
| Location | instance/DSS_local.db |
Render internal URL |
| Migrations | Same migrations/ folder |
Same migrations/ folder |
| Pool settings | None (SQLite doesn't pool) | pool_recycle=180, pool_pre_ping=True |
| SSL | Not required | Required (external URL) |
Backup Strategy¶
PostgreSQL (Production)¶
Render provides automated daily backups on paid DB plans. On the free tier:
```bash
Manual backup via pg_dump (from Render shell or local with DB URL)¶
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql ```
S3 Data¶
Wasabi provides 11-nine durability internally. No separate backup of S3 objects is needed unless you want a second-region copy.
Key Maintenance Tasks¶
Check Database Health¶
```python
flask shell¶
from lenzeye_database import db result = db.session.execute('SELECT COUNT(*) FROM "user"') print(result.scalar()) # Should match expected user count ```
Recalculate Storage for a User¶
```python from admin_db_operations import calculate_storage_used_from_wasabi from lenzeye_BiodataStructure import User
user = User.query.filter_by(email='user@example.com').first() storage_gb = calculate_storage_used_from_wasabi(user.email) user.storage_used = storage_gb db.session.commit() ```
Clean Up Incomplete Multipart Uploads¶
Incomplete multipart uploads in Wasabi accumulate storage. List and abort them:
python
response = s3.list_multipart_uploads(Bucket=bucket_name)
for upload in response.get('Uploads', []):
s3.abort_multipart_upload(
Bucket=bucket_name,
Key=upload['Key'],
UploadId=upload['UploadId']
)
Run this monthly or after any crash during upload.
DB Connection Pool Settings¶
From lenzeye_database.py:
python
engine_options = {
'pool_pre_ping': True, # Test connection before use
'pool_recycle': 180, # Recycle connections every 3 min
'pool_timeout': 30, # Fail fast if pool exhausted
'connect_args': {'connect_timeout': 10} # PostgreSQL connection timeout
}
If DB connection errors appear after long idle periods, pool_pre_ping=True handles this automatically.
TL;DR¶
Migrations: flask db migrate + flask db upgrade. Same migrations/ folder for local (SQLite) and prod (PostgreSQL). Backups: Render automated backups (paid plan) or manual pg_dump. Maintenance: Monthly multipart upload cleanup in Wasabi. Recalculate storage via calculate_storage_used_from_wasabi() when values drift.