Database Change Management

Database change management is the process of planning, tracking, testing, and deploying changes to a database schema and data across environments—development, staging, production—ensuring consistency, reliability, and rollback capability. It matters when multiple teams modify a shared database, when deployments must be zero-downtime, and when schema evolution must coexist with application code changes.

Schema Evolution and Migration

Versioning and Applying Migrations

Schema changes are expressed as migrations—idempotent, reversible SQL scripts with a unique version identifier. Each migration file is named with a prefix indicating its version, such as 20231015120000_create_users_table.sql or 002_add_indexes_to_orders.sql. The migration system tracks which versions are applied using a metadata table named __migrations with columns version (text), applied_at (timestamp), and description (text).

To apply migrations, run:

migrate apply --database postgres://user:pass@host:5432/mydb --path ./migrations

The migrate CLI tool reads all .sql files in the given directory, sorts them by filename, and applies each in sequence. If a migration fails, it records the failure in __migrations and stops.

The tool expects each migration file to start with a -- migration: <version> comment:

-- migration: 20231015120000
-- description: Create users table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

A missing -- migration: line causes the tool to skip the file or fail with:

ERROR: migration file missing version header: 001_initial_schema.sql

Rolling Back Migrations

To roll back a migration, use the migrate rollback command:

migrate rollback --database postgres://user:pass@host:5432/mydb --count 1

This rolls back the most recently applied migration. If --count 2 is given, it rolls back the last two migrations in reverse order. Each migration must contain a -- rollback section:

-- migration: 20231015120000
-- description: Create users table
-- rollback: Drop users table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- rollback
DROP TABLE users;

If the -- rollback section is missing, migrate rollback fails with:

ERROR: migration 20231015120000 missing rollback section

Migration Scripts with Logic

Complex migrations may include procedural logic. For example, a migration that populates data after a schema change:

-- migration: 20231020090000
-- description: Add user roles and populate default roles

CREATE TYPE user_role AS ENUM ('admin', 'editor', 'viewer');

CREATE TABLE user_roles (
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    role user_role NOT NULL,
    PRIMARY KEY (user_id, role)
);

-- populate default roles
INSERT INTO user_roles (user_id, role)
SELECT u.id, 'viewer'
FROM users u
WHERE u.email LIKE '%@example.com';

INSERT INTO user_roles (user_id, role)
SELECT u.id, 'editor'
FROM users u
WHERE u.email LIKE '%@company.com';

-- rollback: Remove roles and drop type
DROP TABLE user_roles;
DROP TYPE user_role;

When a migration contains multiple -- rollback sections, the tool applies the first one found and ignores the rest.

Data-Driven Change Management

Seeding Initial Data

Initial data is managed via seed files, similar to migrations but focused on data rather than schema. Seed files are named seed_<version>.sql and are applied after migrations.

migrate seed --database postgres://user:pass@host:5432/mydb --path ./seeds

A seed file may contain multiple INSERT statements, with optional -- seed: <version> header:

-- seed: 1
-- description: Populate default categories

INSERT INTO categories (name, description) VALUES
    ('Technology', 'Products and services related to computing'),
    ('Books', 'Physical and digital books'),
    ('Clothing', 'Apparel and accessories');

The tool checks for an existing __seeds table to track applied seeds. If a seed version is already applied, it skips the file unless --force is used.

Data Migration Scripts

For large-scale data transformations, use data migration scripts—SQL or application code—that run outside the migration tool. These scripts are versioned in Git and executed via a job scheduler or CI/CD.

A typical data migration job runs:

./scripts/migrate-users-data.sh --from 2023-01-01 --to 2023-12-31

The script connects to the production database and performs a series of UPDATE, INSERT, and DELETE statements, logging progress to a data_migration_log table:

CREATE TABLE data_migration_log (
    id SERIAL PRIMARY KEY,
    job_name TEXT NOT NULL,
    status TEXT CHECK (status IN ('started', 'running', 'completed', 'failed')),
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ,
    rows_processed BIGINT,
    error TEXT
);

If the script fails during an update, it updates the log entry and continues processing other records, but does not rollback the entire job unless explicitly configured.

Deployment and Rollback Strategies

Blue-Green Deployment with Database Switching

For zero-downtime deployments, use blue-green deployment where two database instances—blue and green—serve traffic. The current production database is blue. A new version of the application is deployed against the green database, which is populated with the latest schema and data.

The switch is managed via a switchover script:

./scripts/switchover.sh --from blue --to green --wait 30

The script performs the following steps:

1. Connects to the blue database and checks for active connections.
2. Waits for all connections to be idle or drained.
3. Updates the application_config table to point to the green database.
4. Updates the current_environment table with environment = 'green'.
5. Triggers a pg_reload_conf() call on the PostgreSQL instance.

The script fails with:

ERROR: database switch failed: no connections drained within 30 seconds

If the green database is not ready, it runs pg_isready -h green-db --timeout 30 before switching.

Rolling Back a Deployment

When a new version fails in production, roll back to the previous database state using a rollback-deployment script:

./scripts/rollback-deployment.sh --version 20231025140000 --from green --to blue

This script:

1. Updates application_config to point to the blue database.
2. Sets current_environment to blue.
3. Runs migrate rollback --database postgres://user:pass@host:5432/blue-db --count 1.
4. Triggers a pg_reload_conf() call on the blue database.

If the rollback fails, it logs the error to deployment_log and rolls back the application_config changes.

Monitoring and Failure Modes

Migration Tracking and Health Checks

A migration system must expose a health endpoint to monitor applied migrations:

GET /api/migrations/health HTTP/1.1
Host: db-monitor.example.com
Accept: application/json

Response:

{
  "status": "healthy",
  "applied_migrations": [
    {
      "version": "20231015120000",
      "applied_at": "2023-10-15T12:00:00Z",
      "description": "Create users table",
      "success": true
    },
    {
      "version": "20231020090000",
      "applied_at": "2023-10-20T09:00:00Z",
      "description": "Add user roles and populate default roles",
      "success": false,
      "error": "ERROR: cannot add foreign key constraint: foreign key constraint \"fk_user_roles_user_id\" not found"
    }
  ],
  "pending_migrations": 2,
  "last_check": "2023-10-25T10:00:00Z"
}

The status is unhealthy if any migration failed or if the __migrations table is inaccessible.

Silent Failure: Missing Migration Headers

A common silent failure occurs when a migration file lacks the -- migration: header, causing the migration tool to skip it without reporting. The tool assumes that migration files are named with a consistent version prefix, but if a file is named 001.sql instead of 001_create_users.sql, the tool may apply it as version 001 but fail to parse the version from the filename.

To detect this, use a migrate validate command:

migrate validate --path ./migrations

This checks that each file has a -- migration: <version> line, and that the filename matches the version. It reports:

WARNING: migration file 003_add_indexes.sql has mismatched version: expected 003, found 002

Confusing Migration Options

Two commonly confused options in migration tools are --dry-run and --force.

• --dry-run applies migrations but does not update the __migrations table. It shows what would happen without changing the database state.
• --force applies migrations even if the version is already in the table, overwriting the applied_at timestamp.

Using --dry-run without --force leads to a migration being applied but not recorded, causing future runs to re-apply the same migration.

Using --force without --dry-run applies migrations and updates __migrations, but does not allow previewing the changes.

The Forgotten Rollback Script

A migration system may track applied migrations, but the rollback script is often forgotten. When a new migration is added to production, the __migrations table is updated, but the -- rollback section is not tested. After a failure, the rollback script runs, but the DROP TABLE statement fails because the user_roles table has a foreign key constraint that depends on users, which is not present in the rollback script.

To catch this, run a migrate test-rollback command:

migrate test-rollback --database postgres://user:pass@host:5432/test-db --version 20231020090000

This creates a test database, applies the migration, then runs the -- rollback section and verifies that all expected schema changes are reversed.

Production-Only Failure: Migration Locking

In high-concurrency environments, multiple processes may attempt to apply migrations simultaneously. A migration tool may use a pg_advisory_lock to prevent race conditions:

SELECT pg_advisory_lock(12345);
-- apply migrations
SELECT pg_advisory_unlock(12345);

If a migration process fails during the lock, the next process may find the lock already held and skip the migration, leading to a partial or missing migration.

The tool should log:

INFO: migration lock acquired, waiting for 5s before applying changes
ERROR: migration lock not acquired within 10s: pg_advisory_lock(12345) timed out

To mitigate, run migrations with a --timeout 60 flag.

Best Practices

• Always include -- description: in every migration.
• Use -- rollback blocks for every migration, even simple ones.
• Validate migrations with migrate validate before deployment.
• Run migrate test-rollback in staging before promoting to production.
• Monitor __migrations and data_migration_log tables with alerting.
• Use --dry-run in CI/CD pipelines to preview migration impact.
• Use --force when applying migrations from a CI pipeline where idempotency is not guaranteed.

Database change management is not a one-time task but a continuous process of schema and data evolution—where every migration is a unit of change, and every rollback is a safety net.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.