Changing the app without losing the old work

A schema is not written once. The app grows, a column needs a new meaning, and the question becomes what happens to the rows that already exist. This chapter is about changing the shape of data without breaking the code that reads it, and about knowing you can get the data back if something goes wrong anyway.

Prepare a small local database

This exercise needs PostgreSQL running locally and its client tools installed. Use a local role allowed to create databases, and fresh names that do not belong to another project. In a terminal:

createdb tasks_training
psql tasks_training

Inside psql, create the source table and synthetic rows:

create table tasks (
  id bigint generated always as identity primary key,
  title text not null,
  done boolean not null default false
);
insert into tasks (title, done)
values ('buy thread', false), ('finish the hem', true);

The migration SQL below runs against this disposable table. Exit psql with \q before running terminal commands such as pg_dump; psql tasks_training opens it again. If you use a managed test database instead, use its documented connection and restore tools rather than assuming these local commands address it.

A migration is a versioned change

A migration is a file that changes the schema, kept in git next to the code, and applied in order. A migration tool records which files have already run on each database, so dev and prod run the same files in the same order and end up with the same shape.

migrations/
  001_create_tasks.sql
  002_add_due_date.sql
  003_add_status.sql

Run every new migration on your dev database first, with realistic data in it, before it goes anywhere near prod.

Changing a column without breaking anything

The to-do app's tasks table has a done column, true or false. You want three states, todo, doing and done. The tempting migration is one step: drop done, add status.

The trouble is that a deploy is not instant. For a while, old code is still running: servers that have not restarted, tabs that are still open, and the old version you roll back to if the new one misbehaves. All of it expects done. Drop the column and that code breaks, including the rollback you were counting on.

The change needs a period when old and new representations coexist. The order matters because writers can change a row while you are moving its data.

Add. Add a nullable column so existing code still works.

alter table tasks add column status text;

Coordinate the writers. Deploy a compatibility version that writes both done and status together in the same transaction, but still reads done. Do not introduce the new doing state yet. Wait until every writer uses this version, including background jobs. If you cannot retire old writers reliably, use a tested database synchronization mechanism or a maintenance window instead. Otherwise an old writer can change done while leaving a non-null status stale.

Backfill, then switch readers. Once writes keep both representations consistent, fill the remaining null values. On large tables, use controlled batches.

update tasks
set status = case when done then 'done' else 'todo' end
where status is null;

Validate the mapping and check that no null values remain. You can then require valid status values.

alter table tasks alter column status set not null;
alter table tasks add constraint tasks_status_valid
  check (status in ('todo', 'doing', 'done'));

Deploy readers that use status, while retaining compatibility writes for the rollback period. Decide how doing maps to the old boolean before enabling it; the old representation cannot express all three states. Keeping both columns does not by itself make every rollback semantically safe.

Drop. When no supported reader needs done, retire the compatibility writes. Only then remove the column in a separate migration.

alter table tasks drop column done;

This pattern is called expand and contract. Its useful part is the transition design: which versions can read and write at each step, and what you can still roll back to.

Having a backup is not restoring one

A backup is a file or a snapshot. Restoring is the procedure that turns it back into a working database, and that procedure has many ways to fail: a tool version that does not match, missing database roles, a command nobody remembers, a backup that turns out to be empty, a restore that takes three hours when you expected ten minutes. You only know it works after you have done it.

Use a synthetic local tasks database for this exercise, not a dump of real customer data. The commands assume PostgreSQL client tools are installed and your local role can create databases. Use fresh database names you created for the exercise.

pg_dump --format=custom --file=tasks.dump tasks_training
createdb tasks_restore_test
pg_restore --no-owner --dbname=tasks_restore_test tasks.dump
psql tasks_restore_test -c 'select count(*) from tasks;'

Here tasks_training is the disposable source database you populated for the exercise. Compare row counts, representative values and required constraints with the restored copy; matching counts alone does not establish a correct restore. Point a local copy of the app at the restored database by changing DATABASE_URL in your dev environment, and click around. Note how long the whole thing took and write the steps down where you will find them on a bad day. After checking that tasks_restore_test is the disposable copy you created, remove it with dropdb tasks_restore_test and delete the synthetic dump. A real restore procedure also needs an explicit policy for where backup copies may be stored and who may read them.

Managed database providers usually offer automatic backups and point-in-time recovery, and a restore there typically creates a new database rather than overwriting the old one. The same rule applies. Try the restore once, on purpose, while nothing is on fire.

Before the next migration

Pick the next schema change you are planning and write it as add, backfill, switch and drop, even if some steps turn out to be empty. Before production, establish an applicable backup and recovery procedure and rehearse the migration and restore in an isolated environment. A backup from an earlier schema needs its matching application and migration history too.