Database Migrations in CI/CD Without Downtime
What you build: an API under constant load, a column rename that breaks every request, then the same change with zero errors using expand and contract. Every step tested.

✍️ Translate this blog into Hindi, Tamil or Telugu
Code deploys are easy to undo: roll back to the old version. Database changes are not. Rename a column while the app is running, and every request that uses the old name fails until someone fixes it by hand. In this lab you run a small API under constant traffic, break it with the "quick" migration, then do the same change with zero errors using the expand and contract pattern, a tiny migration runner like the ones CI/CD pipelines use, and a lock timeout that keeps a migration from freezing the app.
Who it is for: developers and DevOps engineers who deploy apps with a database. Time: about 1 hour. Cost: one small VM for about 10 minutes, covered by the free trial credit.
Every command below was run for real by the CareerByteCode Team, and every output is the real result we got. We created the VM from a Cloud Shell-like Linux machine and ran the lab on the VM over SSH; you can do the same from Cloud Shell in your browser.
🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโน499Build the projects →Part A: why database changes break deploys
During a deploy, for a while, the old app and the new database (or the new app and the old database) run together. A migration is safe only if both versions of the app work with the database at every moment.

Words you will use:
- Migration: a numbered SQL file that changes the database (
001_create_customers.sql,002_...). Tools such as Flyway, Liquibase and Alembic apply them in order and remember which ones ran. - schema_migrations: the table where the runner records each applied file, so the next pipeline run applies only new ones.
- Expand and contract (also called parallel change): 1) expand, add the new thing next to the old one and keep both in sync; 2) deploy the app that uses the new thing; 3) contract, remove the old thing once nothing uses it.
- Lock: a database change needs an exclusive lock on the table. If a long query holds the table, the change waits, and every app query after it waits too.
- lock_timeout: how long a migration may wait for a lock before giving up.
- Real-life example: you do not rename a road overnight. You put up the new name next to the old one, wait until maps and drivers use it, then take the old sign down.
Before you start
A Google Cloud project with billing linked (the free trial works). Open Cloud Shell (the >_ icon at the top of the console) and set your project (put your own id between the quotes):
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable compute.googleapis.comPart B: the lab
Step 1: a VM for the lab
Why: PostgreSQL, the app and the "pipeline" all run on one small VM (e2-small).
# One small VM for the lab: PostgreSQL, the app and the "pipeline" all run here
gcloud compute instances create mig-vm --zone=us-east1-b --machine-type=e2-small --image-family=debian-12 --image-project=debian-cloud --boot-disk-size=20GB > /dev/null 2>&1 && echo "mig-vm created"
until gcloud compute ssh mig-vm --zone=us-east1-b --tunnel-through-iap --command="true" > /dev/null 2>&1; do sleep 10; done; echo "SSH works"mig-vm created
SSH worksFrom here on: open an interactive SSH session with the VM using the command below. Once you see the VM terminal prompt (you@mig-vm:~$), run the next boxes there. Every command from Step 2 to Step 4 runs on the VM, in your home folder there, and every file below is saved there too (for example nano ~/app.py, paste, Ctrl+O, Enter, Ctrl+X):
gcloud compute ssh mig-vm --zone=us-east1-b --tunnel-through-iapStep 2: PostgreSQL, the app and the first migration
Why: the app is a tiny customer API. Version 1 reads and writes the column name; version 2 uses full_name. The version comes from a file, read when gunicorn (the web server) starts or reloads its workers, which is our "deploy". On the VM, save it as ~/app.py:
# app.py: a tiny customer API. Version 1 uses the column "name", version 2 uses "full_name".
# The version comes from the file version.txt, read when gunicorn starts or reloads its workers (our "deploy").
# GET /customers/<id> reads one row, POST /customers adds one. Runs on port 8000.
import os
import psycopg
from flask import Flask, jsonify, request
app = Flask(__name__)
VERSION = open(os.path.join(os.path.dirname(__file__), "version.txt")).read().strip()
COL = "name" if VERSION == "1" else "full_name"
DSN = "host=localhost dbname=shop user=app password=" + open(os.path.expanduser("~/.apppass")).read().strip()
@app.get("/customers/<int:cid>")
def get_customer(cid):
with psycopg.connect(DSN) as db:
row = db.execute(f"SELECT id, {COL} FROM customers WHERE id = %s", (cid,)).fetchone()
return (jsonify(id=row[0], name=row[1]), 200) if row else ("not found", 404)
@app.post("/customers")
def add_customer():
with psycopg.connect(DSN) as db:
cid = db.execute(f"INSERT INTO customers ({COL}) VALUES (%s) RETURNING id", (request.json["name"],)).fetchone()[0]
return jsonify(id=cid), 201
@app.get("/version")
def version():
return f"app version {VERSION} uses column {COL}\n"The migration runner applies every new file in migrations/, each in its own transaction with a 3-second lock timeout, and records it in schema_migrations. Save it as ~/migrate.sh:
#!/bin/bash
# migrate.sh: apply every migrations/*.sql file that is not yet in the table schema_migrations, in name order,
# each in one transaction with a 3 second lock timeout (so a migration waits briefly instead of blocking the app).
set -e
PSQL="psql -h localhost -U app -d shop -qAt -v ON_ERROR_STOP=1"
export PGPASSWORD=$(cat ~/.apppass)
$PSQL -c "CREATE TABLE IF NOT EXISTS schema_migrations (version text PRIMARY KEY, applied_at timestamptz DEFAULT now())"
for f in migrations/*.sql; do
v=$(basename $f .sql)
if [ -n "$($PSQL -c "SELECT 1 FROM schema_migrations WHERE version = '$v'")" ]; then continue; fi
echo "applying $v"
( echo "BEGIN; SET LOCAL lock_timeout = '3s';"; cat $f; echo "INSERT INTO schema_migrations (version) VALUES ('$v'); COMMIT;" ) | $PSQL
done
echo "applied: $($PSQL -c "SELECT string_agg(version, ', ' ORDER BY version) FROM schema_migrations")"The first migration creates the table with 50 customers. Make the folder with mkdir -p ~/sql and save it as ~/sql/001_create_customers.sql:
CREATE TABLE customers (id serial PRIMARY KEY, name text NOT NULL, created_at timestamptz DEFAULT now());
INSERT INTO customers (name) SELECT 'Customer ' || g FROM generate_series(1, 50) g;Save ~/load.sh from Step 3 now as well (the next box makes it executable). Then run the setup on the VM:
# PostgreSQL 15, a database "shop" with its own user, and the app (Flask behind gunicorn, 2 workers)
sudo apt-get update -qq > /dev/null && sudo apt-get install -y -qq postgresql python3-venv > /dev/null 2>&1; psql --version
openssl rand -hex 12 > ~/.apppass; chmod 600 ~/.apppass # a lab-only password, never printed
sudo -u postgres psql -qc "CREATE USER app PASSWORD '$(cat ~/.apppass)'" && sudo -u postgres psql -qc "CREATE DATABASE shop OWNER app" && echo "database shop ready"
python3 -m venv .venv && . .venv/bin/activate && pip install -q "flask==3.1.*" "gunicorn==23.*" "psycopg[binary]==3.2.*" 2>&1 | grep -v notice | tail -1
chmod +x migrate.sh load.sh && mkdir -p migrations && cp sql/001_create_customers.sql migrations/ && ./migrate.sh
echo 1 > version.txt && .venv/bin/gunicorn -w 2 -b 127.0.0.1:8000 --daemon --pid gunicorn.pid app:app && sleep 3 && curl -s localhost:8000/versionpsql (PostgreSQL) 15.20 (Debian 15.20-0+deb12u1)
database shop ready
applying 001_create_customers
applied: 001_create_customers
app version 1 uses column nameStep 3: the baseline under load
Why: to see a failure, you need traffic. load.sh calls the API 10 times a second (a read and a write each time) and counts the answers by HTTP status:
#!/bin/bash
# load.sh <seconds>: call the API 10 times a second (reads and writes) and count the answers by HTTP status
end=$((SECONDS + $1)); : > load.log
while [ $SECONDS -lt $end ]; do
curl -s -o /dev/null -w "%{http_code}\n" localhost:8000/customers/$((RANDOM % 50 + 1)) >> load.log
curl -s -o /dev/null -w "%{http_code}\n" -H "Content-Type: application/json" -d '{"name": "Test User"}' localhost:8000/customers >> load.log
sleep 0.2
done
echo "requests: $(wc -l < load.log), by status: $(sort load.log | uniq -c | awk '{printf "%s x%s ", $2, $1}')"# The app under load for 20 seconds, before any change: every answer should be 200 or 201
./load.sh 20requests: 146, by status: 200 x73 201 x73
Every request answered with 200 (read) or 201 (created).
Break it: rename the column in one step
The "quick" migration renames name to full_name while version 1 of the app is running. Save it as ~/sql/002_rename_column.sql:
-- The quick way: rename the column in one step. The running app (version 1) still asks for "name"
ALTER TABLE customers RENAME COLUMN name TO full_name;# Break it: the "quick" migration renames the column while version 1 of the app is running and serving users
cp sql/002_rename_column.sql migrations/ && ./migrate.sh
./load.sh 15NOTICE: relation "schema_migrations" already exists, skipping
applying 002_rename_column
applied: 001_create_customers, 002_rename_column
requests: 110, by status: 500 x110Every one of the 110 requests failed with HTTP 500: the running app still asks for name. In a real deploy, this lasts until the new app version is out everywhere, and if the new version has a problem and you roll it back, the old version fails again. We undo it by hand:
# Undo it the same way (rename back) and remove the bad file from the pipeline
PGPASSWORD=$(cat ~/.apppass) psql -h localhost -U app -d shop -qAt -c "ALTER TABLE customers RENAME COLUMN full_name TO name; DELETE FROM schema_migrations WHERE version = '002_rename_column';"
rm migrations/002_rename_column.sql && ./load.sh 10requests: 74, by status: 200 x37 201 x37Fix it, step 1: expand
Why: add full_name next to name, copy the data, and add a trigger (a database function that runs on every insert and update) that fills whichever column is empty. Now version 1 (writes name) and version 2 (writes full_name) both keep both columns correct. Save it as ~/sql/002_expand_full_name.sql:
-- EXPAND: add the new column next to the old one, copy the data, and keep both in sync for writes from either version
ALTER TABLE customers ADD COLUMN full_name text;
UPDATE customers SET full_name = name WHERE full_name IS NULL;
CREATE FUNCTION customers_sync_names() RETURNS trigger AS $$
BEGIN
IF NEW.full_name IS NULL THEN NEW.full_name := NEW.name; END IF;
IF NEW.name IS NULL THEN NEW.name := NEW.full_name; END IF;
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER customers_sync BEFORE INSERT OR UPDATE ON customers FOR EACH ROW EXECUTE FUNCTION customers_sync_names();We run it while traffic flows (the load runs in the background for 30 seconds):
# Fix it, step 1 EXPAND: add full_name next to name, copy, keep both in sync. Version 1 keeps working
cp sql/002_expand_full_name.sql migrations/
(./load.sh 30 > load-expand.txt &) ; sleep 5; ./migrate.sh; sleep 27; cat load-expand.txtNOTICE: relation "schema_migrations" already exists, skipping
applying 002_expand_full_name
applied: 001_create_customers, 002_expand_full_name
requests: 216, by status: 200 x108 201 x108216 requests, 0 errors. Version 1 never noticed.
Fix it, step 2: deploy version 2
Why: switch the app to full_name with a graceful reload (kill -HUP): gunicorn starts new workers with the new code and lets the old ones finish their requests.
# Step 2 DEPLOY: switch the app to version 2 (uses full_name) with a graceful reload while traffic keeps flowing
(./load.sh 20 > load-deploy.txt &) ; sleep 5
echo 2 > version.txt && kill -HUP $(cat gunicorn.pid) && sleep 3 && curl -s localhost:8000/version
sleep 14; cat load-deploy.txt
PGPASSWORD=$(cat ~/.apppass) psql -h localhost -U app -d shop -qAt -c "SELECT 'rows: ' || count(*) || ', name empty: ' || count(*) FILTER (WHERE name IS NULL) || ', full_name empty: ' || count(*) FILTER (WHERE full_name IS NULL) FROM customers"🎓⭐ Recommended by CareerByteCode · Live trainingCloud & DevOps Live Cohort Internshipโน2,360See the training →app version 2 uses column full_name
requests: 140, by status: 200 x70 201 x70
rows: 338, name empty: 0, full_name empty: 0140 requests during the deploy, 0 errors. All 338 rows have both columns filled: rows written by version 1 got full_name from the trigger, and rows written by version 2 got name.
Fix it, step 3: contract
Why: only now that no copy of version 1 runs, remove the trigger and the old column, and make full_name required. Save it as ~/sql/003_contract_drop_name.sql:
-- CONTRACT (only after every app copy runs version 2): stop syncing, make the new column required, drop the old one
DROP TRIGGER customers_sync ON customers;
DROP FUNCTION customers_sync_names();
ALTER TABLE customers ALTER COLUMN full_name SET NOT NULL;
ALTER TABLE customers DROP COLUMN name;# Step 3 CONTRACT: only now that no copy of version 1 runs, drop the old column. Still no errors
cp sql/003_contract_drop_name.sql migrations/
(./load.sh 20 > load-contract.txt &) ; sleep 5; ./migrate.sh; sleep 17; cat load-contract.txt
PGPASSWORD=$(cat ~/.apppass) psql -h localhost -U app -d shop -qAt -c "SELECT string_agg(column_name, ', ' ORDER BY ordinal_position) FROM information_schema.columns WHERE table_name = 'customers'" | sed 's/^/columns now: /'NOTICE: relation "schema_migrations" already exists, skipping
applying 003_contract_drop_name
applied: 001_create_customers, 002_expand_full_name, 003_contract_drop_name
requests: 146, by status: 200 x73 201 x73
columns now: id, created_at, full_name146 requests, 0 errors, and the table now has only full_name.

Step 4: why the lock timeout matters
Why: we start a long query (20 seconds) that holds the table, then try a migration that needs an exclusive lock. Without a timeout, the migration would wait, and every app query would queue behind it: a full outage caused by a one-line change. With lock_timeout = '3s':
# Why the lock timeout? A long transaction holds a lock; the migration waits 3 s, gives up, and the app is not blocked
PGPASSWORD=$(cat ~/.apppass) psql -h localhost -U app -d shop -qAt -c "BEGIN; SELECT count(*) FROM customers; SELECT pg_sleep(20); COMMIT;" > /dev/null &
sleep 2; printf 'ALTER TABLE customers ADD COLUMN email text;\n' > migrations/004_add_email.sql
./migrate.sh 2>&1 | grep -E "applying|lock timeout|applied" ; echo "migration exit code: ${PIPESTATUS[0]} (it will be retried by the next pipeline run)"
wait; ./migrate.sh | tail -1applying 004_add_email
ERROR: canceling statement due to lock timeout
migration exit code: 3 (it will be retried by the next pipeline run)
NOTICE: relation "schema_migrations" already exists, skipping
applied: 001_create_customers, 002_expand_full_name, 003_contract_drop_name, 004_add_emailThe migration gave up after 3 seconds (canceling statement due to lock timeout), the app was never blocked, and the runner exited with an error so the pipeline stops. When the long query finished, the next run applied it.

How this fits a CI/CD pipeline
- A pipeline step runs
migrate.sh(or Flyway, Liquibase, Alembic) before the new app version is deployed, with a lock timeout. - Only expand migrations go out with a release; the app must work with the database before and after them.
- The contract migration ships in a later release, once the old app version is gone everywhere.
- A failed migration stops the pipeline; the next run retries it, because applied files are recorded.
POC: prove it works
- The one-step rename failed all 110 requests (Break it).
- Expand, deploy and contract had 0 errors in 502 requests in total, with both versions of the app running at some point (Fix it).
- The lock timeout stopped a migration from blocking the app, and the retry succeeded (Step 4).
Clean up (delete everything)
Type exit to leave the VM, then run this in Cloud Shell:
# Clean up: delete the VM (the database, app and files go with it)
gcloud compute instances delete mig-vm --zone=us-east1-b --quiet 2>&1 | tail -1
echo "lab VMs left: $(gcloud compute instances list --filter='name=mig-vm' --format='value(name)' | wc -l)"Deleted [https://www.googleapis.com/compute/v1/projects/your-project-id/zones/us-east1-b/instances/mig-vm].
lab VMs left: 0FAQ
Do I always need a trigger? No. If only one app version writes at a time, a backfill is enough. The trigger is what makes it safe while two versions write at the same time.
What other changes need expand and contract? Renaming or splitting a column, changing a type, making a column required, moving data to another table. Adding a new nullable column or a new table is usually safe in one step.
What about big tables? Copy data in batches, and create indexes with CREATE INDEX CONCURRENTLY (it cannot run inside a transaction, so run it as its own step).
Can I roll back a migration? You can write "down" migrations, but rolling back data is risky. Expand and contract avoids the need: at every step, the previous app version still works.
Which tool should I use? Flyway and Liquibase (Java world, any language), Alembic (Python), Prisma or Knex (Node), or your framework's own. They all do what migrate.sh does, with more checks.
Troubleshooting
| You see | Why | Fix |
|---|---|---|
| HTTP 500 right after a migration | The running app uses a name that changed | Expand and contract instead of rename |
| A migration hangs and the app freezes | It waits for a lock while app queries queue behind it | Set lock_timeout and retry later |
canceling statement due to lock timeout | A long query held the table | Find it in pg_stat_activity, then re-run the pipeline |
| The same migration runs twice | The runner does not record applied files | Keep a schema_migrations table, as migrate.sh does |
| New rows miss the new column | No trigger during the expand phase | Add the sync trigger, or backfill again before the contract |
What next
- Build the whole pipeline around it: DevOps Project for Your Resume: Full CI/CD on Google Cloud.
- Run the database as a managed service: Cloud SQL for PostgreSQL: Create, Connect and Back Up.
- More hands-on posts: ByteBlog. Practice in ByteLabs and see the courses.
Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.
⭐ Keep learning with CareerByteCode
Hand-picked to go deeper on this topicGet CareerByteCode Team's next blog by email
One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.
We email you once to confirm. By subscribing you agree to the privacy policy.
Related blogs
CareerByteCode TeamMonorepo CI/CD: Build Only What Changed
What you build: a monorepo pipeline on Cloud Build that builds, tests and deploys only the changed service to Cloud Run, rebuilds all on a shared library change, plus 2 real breaks: no .git and a docs
#beginners
CareerByteCode TeamCloud Build Hands-On: Pipelines Without Managing Servers
What you build: a build, test and push pipeline in Cloud Build, in your own Google Cloud account. Every step, command and screenshot explained for beginners.
#cicd
CareerByteCode TeamHelm Chart CI/CD: Lint, Test and Publish Charts Automatically
What you build: a Helm chart pipeline: lint, kubeconform, helm-unittest, then publish to Artifact Registry with immutable versions, plus a real failing test, a refused overwrite, and install/upgrade/r
#cicd
CareerByteCode TeamFlux CD vs Argo CD: Hands-On GitOps Comparison
What you build: Argo CD and Flux side by side on one GKE cluster from the same Git repo, with real numbers: footprint, time to apply a Git change, drift correction, and broken-commit handling.
💬 Comments (0)
Top · NewestSign in to comment