Module 2: Database & Redis Hosting

Data migration: from local to the cloud

Next →

You have your app deployed with managed Postgres in the cloud. But your data is local. How do you move everything? This capsule covers two approaches: pg_dump/pg_restore (a snapshot) and Alembic migrations in production (schema-only). Plus patterns for zero-downtime once you already have users.

Approach 1: pg_dump + pg_restore (a complete snapshot)

For moving schema + data from one Postgres to another.

Step 1: dump from local

pg_dump \
    --host=localhost \
    --port=5432 \
    --username=postgres \
    --dbname=myapp_dev \
    --format=custom \
    --no-owner \
    --no-acl \
    --file=backup.dump

The options explained:

  • --format=custom: an efficient binary format (recommended).
  • --no-owner: don't include owner names (they'd cause an error in the cloud).
  • --no-acl: don't include grants (the cloud handles permissions differently).
  • --file: the output file.

For large databases (>1GB), add:

  • --jobs=4: parallel dump (faster).
  • --compress=9: maximum compression.

Step 2: restore into the cloud

pg_restore \
    --host=db.cloudprovider.com \
    --port=5432 \
    --username=postgres \
    --dbname=postgres \
    --no-owner \
    --no-acl \
    --clean \
    --if-exists \
    backup.dump

--clean: drop existing objects first (a schema reset). --if-exists: no error if the objects don't exist.

Careful: --clean deletes existing data in the cloud. Only use it if the cloud DB is empty or if you want a total overwrite.

Step 3: verify

psql -h db.cloudprovider.com -U postgres -d postgres -c "
    SELECT schemaname, tablename, n_live_tup
    FROM pg_stat_user_tables
    ORDER BY n_live_tup DESC;
"

You verify that the tables and row counts match your local ones.

Approach 2: Alembic migrations in production (schema-only)

If your local has little data or you want a clean start:

Setup

Procfile:

release: alembic upgrade head
web: uvicorn app.main:app --host 0.0.0.0 --port $PORT

Render runs release before every deploy. It applies migrations automatically.

Workflow

  1. Create the migration locally: alembic revision --autogenerate -m "...".
  2. Test it locally: alembic upgrade head.
  3. Commit + push.
  4. Render deploys: release runs alembic upgrade head against the cloud DB.
  5. The app deploys anew with the updated schema.

For initial data (seeds)

Create a scripts/seed.py script:

import asyncio
from app.database import SessionLocal
from app.models import Category, User


async def seed():
    async with SessionLocal() as session:
        # Initial categories
        category_data = [
            {"id": 1, "name": "Electronics"},
            {"id": 2, "name": "Books"},
        ]
        for c in category_data:
            existing = await session.get(Category, c["id"])
            if not existing:
                session.add(Category(**c))

        await session.commit()
        print("Seeded categories")


if __name__ == "__main__":
    asyncio.run(seed())

Run it once after the deploy:

# Render dashboard → Service → Shell
python scripts/seed.py

Or integrate it into the app's startup (careful with concurrency).

Migration with existing data (if you have users)

If your app ALREADY has users in the cloud DB and you want to add new data without losing the existing data:

Approach: data-only insert

# Dump only the data from specific tables
pg_dump \
    --data-only \
    --table=categories \
    --table=tags \
    --host=local \
    --dbname=local_db \
    > seed_data.sql

Restore:

psql -h cloud-db -U postgres -d postgres < seed_data.sql

Careful: if IDs collide (a category with id=1 already exists in the cloud), you get an error.

Mitigation: use INSERT ... ON CONFLICT DO NOTHING:

INSERT INTO categories (id, name) VALUES (1, 'Electronics')
ON CONFLICT (id) DO NOTHING;

Zero-downtime schema migration

When you already have users in the cloud and need to change the schema (add a column, rename, etc.):

The expand-contract pattern (covered in guide #13 module 5):

  1. Expand: add the new column (nullable, optional).
  2. Backfill: populate the new column with appropriate values.
  3. Migrate code: the app uses the new column.
  4. Contract: make the column NOT NULL or drop the old column.

Each step is a separate deploy. Zero downtime between deploys.

Applied to Alembic:

# Migration 1: expand
def upgrade():
    op.add_column('users', sa.Column('email_verified', sa.Boolean, nullable=True))

# Migration 2 (after several deploys): contract
def upgrade():
    op.alter_column('users', 'email_verified', nullable=False, server_default='false')

Migration validation

After any migration in prod, verify:

# Connect to the cloud DB
psql "postgresql://..."

# Schema check
\d users
# verify columns, types, constraints

# Data check
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE email_verified IS NULL;

# Indexes
\di

If something looks off, roll back (Alembic):

alembic downgrade -1

(Careful: rollbacks can lose data. Back up first.)

Backup automation before any migration

Before complex migrations:

# Backup
pg_dump --format=custom -h cloud-db -d postgres > backup_pre_migration_$(date +%Y%m%d_%H%M%S).dump

# Run migration
alembic upgrade head

# If something fails, restore
pg_restore --clean -h cloud-db -d postgres backup_pre_migration_xxx.dump

Managed providers (Supabase, Neon) have automatic backups, but don't rely on that alone. A manual backup before important changes.

Performance tips for large migrations

If your data is massive (millions of rows):

Tip 1: parallel dump

pg_dump --jobs=4 --format=directory --file=dump_dir/ ...

--format=directory enables parallelism.

Tip 2: restore with jobs

pg_restore --jobs=4 --format=directory dump_dir/

Tip 3: temporarily disable indexes

The restore is faster without recreating indexes. Create them afterwards with CREATE INDEX CONCURRENTLY.

Tip 4: pause replication during the restore

If your cloud DB has replicas, pausing avoids a flood of WAL.

Common gotchas

1. pg_dump version mismatch.

pg_dump version 14 vs PostgreSQL 16
# Error: server version is newer than pg_dump

pg_dump must be >= the version of the source Postgres.

# Use pg_dump with docker
docker run --rm postgres:16 pg_dump ...

2. Owner errors on restore.

ERROR: role "myuser" does not exist

The --no-owner flag prevents this.

3. Permission errors.

ERROR: must be owner of table users

Restore as superuser or with --no-acl.

4. Connection limit during the restore.

pg_restore with --jobs=8 opens 8 connections. That exceeds the free tier limit. Use --jobs=2.

5. Encoding mismatch.

Source UTF8, target SQL_ASCII = errors. Match the encoding beforehand:

pg_dump --encoding=UTF8 ...

6. Replicas out of date.

If the cloud DB has replicas, they can lag during the migration. Wait for replication to catch up before continuing.

Exercise: migrate your local DB to the cloud

Step 1: install pg_dump locally.

brew install postgresql@16  # mac
# or apt-get install postgresql-client-16

Step 2: dump from local.

pg_dump -h localhost -U postgres -d myapp_dev --format=custom > backup.dump

Verify the file was created.

Step 3: restore into the cloud.

Get the connection string from Supabase/Neon.

pg_restore \
    --host=YOUR-CLOUD-HOST \
    --port=5432 \
    --username=postgres \
    --dbname=postgres \
    --no-owner \
    --no-acl \
    --clean \
    --if-exists \
    backup.dump

Step 4: verify.

psql "postgresql://..." -c "SELECT schemaname, tablename, n_live_tup FROM pg_stat_user_tables;"

Compare it with local.

Step 5: update DATABASE_URL on Render.

Render dashboard → Environment → DATABASE_URL → the cloud connection string.

Save → re-deploy.

Step 6: smoke test.

curl https://my-api.onrender.com/health/ready
# You expect db: ok

curl https://my-api.onrender.com/api/whatever
# You expect a response with the migrated data

Summary and next step

What you learned:

  • pg_dump/pg_restore: a complete snapshot. Good for the initial migration.
  • Alembic in production: schema-only via the release command. Good for ongoing schema changes.
  • Data-only inserts: for seeds or specific data without touching the schema.
  • Zero-downtime: the expand-contract pattern (guide #13 module 5).
  • Manual backups before important migrations.
  • Gotchas: version mismatch, owner errors, connection limits.

In the next capsule we go to Redis hosting: Upstash vs Redis Cloud. A cloud cache layer for your app.

Resources

  1. PostgreSQL — pg_dump — reference.
  2. PostgreSQL — pg_restore — reference.
  3. Alembic — Production deployments — patterns.
  4. Supabase — Migration guide — specific.

Capsule 05 of 08 — Module 2 — Deployment & System Design Guide

← PreviousConnection pooling: the critical conceptNext →Redis hosting: Upstash vs Redis Cloud