What is a database migration, and why should you back up first?
Database migrations can break production apps or delete data. Learn what migrations are, how to prepare, and why a backup comes first.
What is a database migration, and why should you back up first?
Your developer says, “We need to run a migration.” You say, “Sure,” because it sounds like routine maintenance.
Then the app stops working.
A database migration is a planned change to the structure or location of your database. It might add a column for a new feature, rename a table, move your data to a new provider, or upgrade PostgreSQL to a newer version. Most migrations are safe when prepared properly — but a mistake can affect every screen, customer, and transaction in your app.
The safest rule is simple: take a reliable backup before you change the database, and know how you would restore it. A migration script is not a backup. Git is not a backup. A replica is not necessarily a backup either.
What does “database migration” mean?
Think of your database as a filing cabinet and its schema as the filing system: which drawers exist, what each form contains, and how records relate to one another. A migration changes that filing system without throwing away the documents.
Common examples include:
- Adding a
subscription_statuscolumn for a new billing feature - Creating a table for support tickets
- Changing a column from text to a date
- Moving from one PostgreSQL project to another
- Upgrading to a newer PostgreSQL release
- Renaming or removing an old field
Migration files are useful because they record changes in a repeatable way. But a migration can still fail halfway through, contain an incorrect assumption, or work in testing and break production data.
Source: Microsoft — Database design basics
- 1
Plan the change
Decide what structure or data needs to change and why.
- 2
Back up production
Create an independent, restorable copy before touching live data.
- 3
Test on a copy
Run the migration against a non-production database and check the result.
- 4
Apply the migration
Run the approved change against production with monitoring in place.
- 5
Verify the app
Test sign-in, key customer flows, records, files, and permissions.
- 6
Keep the rollback option
If something is wrong, restore or reverse the change using a prepared plan.
Why migrations go wrong
A migration can fail for technical reasons, but the business impact is easy to understand: customers may be unable to sign in, orders may not save, reports may show incorrect information, or an important field may be permanently removed.
The most common risks are:
The script was tested on different data. A migration that works on a small development database may time out or behave differently when production contains millions of rows.
The change is not reversible. Adding a column is usually easier to undo than dropping one. If a migration deletes or transforms data, a “down” script may not be able to reconstruct the original values.
The application and database change at different times. New code may expect a column that does not exist yet, while old code may fail when the database changes underneath it. Safer releases often add compatible structure first, deploy code second, and remove old structure later.
The backup does not contain everything. A PostgreSQL dump may capture tables and schema, but your application can also depend on authentication settings, uploaded storage objects, edge functions, secrets, and configuration outside the database itself.
Nobody tested the restore. A backup job that says “completed” only proves that a file was created. It does not prove the file can rebuild a working project.
Source: Heroku — Planning your PostgreSQL migration
Backup vs. migration file vs. replica
These tools solve different problems. Confusing them is one of the easiest ways to create a false sense of safety.
| Tool | What it is for | What happens if a bad migration runs? |
|---|---|---|
| Migration file | Recording and applying a planned structure change | It may repeat the mistake unless corrected |
| Git repository | Tracking application and migration code | It usually does not contain live customer data |
| Replica | Keeping another database reasonably close to the primary | Accidental changes can replicate too |
| Backup | Creating a recoverable copy from before the change | You can restore the earlier state |
Supabase provides daily database backups and, on eligible plans, point-in-time recovery. Those features can be useful, but you should still understand what they include, how long they are retained, and how you would access them during an incident.
Source: Supabase — Database backups
| Question | Why it matters | Good answer | |
|---|---|---|---|
| Do we have a fresh backup? | Gives you a known point to recover to. | Yes, created immediately before the change. | |
| Can we restore it? | A backup that cannot be restored is not useful in an emergency. | Yes, tested on a separate project. | |
| Does it include files? | Database rows may only contain metadata for uploaded files. | Database, storage, auth, and functions are covered. | |
| Can we pause or reverse the change? | Limits downtime and data loss if the migration fails. | Rollback or restore steps are written down. | |
| Who verifies the app? | A successful SQL command does not prove the product works. | Someone tests real customer workflows. |
A practical PostgreSQL migration checklist
You do not need to become a PostgreSQL administrator to ask for sensible safeguards. Before approving a production migration, ask your developer to confirm these steps:
1. Describe the change in plain English. What tables, fields, indexes, or records will change? Is any data being deleted or transformed?
2. Create an independent backup. Save it outside the production database provider. A copy in a separate account or storage provider is safer than relying on one system for both the live data and its recovery copy.
3. Test the migration on a recent copy. Test data should resemble production data closely enough to reveal size, permissions, and compatibility problems.
4. Decide how much downtime is acceptable. Some changes finish quickly; others lock or rewrite large tables. Schedule risky work when fewer customers are active.
5. Write the recovery steps before starting. Include who makes the decision, where the backup is located, how to restore it, and how to verify the result.
6. Test the important workflows. Check login, creating and editing records, payments or orders, emails, file uploads, admin permissions, and any integrations that depend on the changed data.
7. Keep the pre-migration backup. Do not immediately overwrite your last known-good copy. Keep it until the new version has been observed and verified.
3
copies of your data
2
different storage media
1
copy kept off-site
How Supakeep helps before and after a migration
Supakeep gives accidental database owners a simple independent safety copy before a risky change. It backs up database data plus the surrounding pieces your app relies on — including storage, authentication, and edge functions — and sends the copies to your Google Drive.
That matters because a migration may succeed at the SQL level while your app is still missing files, authentication configuration, or server-side logic. Supakeep does not store your data permanently; it passes through and lands in a location you control. The dashboard gives you a clear way to check that the backup ran instead of asking you to become a backup expert.
The best time to set this up is before migration day, not after something breaks. Use it alongside your migration workflow: take a fresh backup, test the change, verify the app, and keep the old copy until you are confident the new version is working.
Related reading: Logical vs. physical PostgreSQL backups: which one does your app need?, PostgreSQL WAL explained: why it is not a backup, How to restore a Supabase project from a SQL backup, Backups vs. replication: what's the difference?, and Supabase branching vs. backups: why you still need both.
Sources
- 1PostgreSQL — Migration between releases: https://www.postgresql.org/docs/current/migration.html
- 2Supabase — Database backups: https://supabase.com/docs/guides/platform/backups
- 3Supabase — Backup and restore using the CLI: https://supabase.com/docs/guides/platform/migrating-within-supabase/backup-restore
- 4Heroku — Planning your PostgreSQL migration: https://www.heroku.com/blog/planning-your-postgresql-migration/
- 5Google Cloud — Database migration concepts and principles: https://cloud.google.com/architecture/database-migration-concepts-principles-part-1
- 6Microsoft — Database design basics: https://support.microsoft.com/en-us/access/database-design-basics
- 7Liquibase — PostgreSQL schema migration: https://www.liquibase.com/blog/postgres-schema-migration
FAQ
What is a PostgreSQL database migration?
It is a planned change to a PostgreSQL database’s structure, data, or location. Examples include adding a table, changing a column, moving to another project, or upgrading PostgreSQL.
Should I back up PostgreSQL before running a migration?
Yes. Create a fresh, independent backup before changing production. Keep it somewhere separate from the live database and confirm that it can be restored.
Can I roll back a database migration?
Sometimes. A carefully written reverse migration can undo some structural changes, but deleted or transformed data may not be recoverable through a script. A pre-migration backup gives you a safer recovery option.
Is a migration file a backup?
No. A migration file describes a change to the database structure. It normally does not contain your live customer records, uploaded files, authentication data, or a complete restore path.
Is a PostgreSQL replica the same as a backup?
No. A replica can improve availability, but it may copy accidental deletions, corruption, or bad migrations from the primary. A backup is a separate recoverable point in time.
What should I test after a database migration?
Test the workflows customers use: sign-in, creating and editing records, payments or orders, file uploads, emails, permissions, and integrations. Also compare important row counts and check for errors in logs.
Does Supabase automatically back up my database?
Supabase documents daily database backups and point-in-time recovery for eligible plans. You should check your plan’s coverage and retention, and maintain an independent backup for recovery scenarios you control.
How does Supakeep help with database migrations?
Supakeep creates an independent copy before the migration and stores it in your Google Drive. It covers database data plus storage, authentication, and edge functions, helping you recover without manually assembling every part of your project.