Flyway & Liquibase

Flyway and Liquibase are the two most common JVM-based migration runners for PostgreSQL in enterprise pipelines. Both track applied changes, support CI, and integrate with Spring and release orchestration.

Recipe

# Flyway
flyway -url=jdbc:postgresql://localhost:5432/myapp \
  -user=app_migrator -password="$DB_PASS" \
  -locations=filesystem:db/migration \
  migrate
 
# Liquibase
liquibase --url=jdbc:postgresql://localhost:5432/myapp \
  --username=app_migrator --password="$DB_PASS" \
  --changeLogFile=db/changelog/db.changelog-master.yaml \
  update

When to reach for this: Multiple deploy targets, audit requirements, checksum validation, and teams already standardized on JVM tooling.

Working Example

Flyway layout:

db/migration/
  V1__baseline.sql
  V2__add_orders.sql
  V3__orders_index_concurrent.sql  # note: run outside txn - see docs

-- V2__add_orders.sql
SET ROLE app_owner;
CREATE TABLE app.orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total_cents integer NOT NULL
);
RESET ROLE;

Liquibase master changelog:

# db/changelog/db.changelog-master.yaml
databaseChangeLog:
  - include:
      file: changes/001-baseline.sql
      relativeToChangelogFile: true
  - include:
      file: changes/002-orders.yaml
      relativeToChangelogFile: true

# changes/002-orders.yaml
databaseChangeLog:
  - changeSet:
      id: 002-orders
      author: data-team
      changes:
        - sqlFile:
            path: 002-orders.sql
            relativeToChangelogFile: true
      rollback:
        - sql: DROP TABLE IF EXISTS app.orders;

What this demonstrates:

• Flyway: one SQL file per version, linear V{n}__ naming
• Liquibase: YAML/XML changelog with optional rollback blocks per changeSet
• Both record history in metadata tables automatically

Deep Dive

Comparison

PostgreSQL-Specific Settings

# flyway.conf
flyway.postgresql.transactional.lock=false
flyway.cleanDisabled=true

# Liquibase changeSet for CONCURRENT index
- changeSet:
    id: idx-concurrent
    runInTransaction: false
    changes:
      - sql:
          sql: CREATE INDEX CONCURRENTLY idx_orders_created ON app.orders (created_at);

• CREATE INDEX CONCURRENTLY cannot run in a transaction.
• Disable clean in production Flyway - it drops all objects.
• Use lock_timeout and statement_timeout in migration SQL (see safety rules).

CI Integration

flyway validate   # checksum drift detection
flyway info       # pending vs applied
liquibase status
liquibase validate

Gotchas

• Editing applied migration file - Checksum mismatch blocks deploy. Fix: New forward migration V4__fix.sql, never rewrite V2.
• Concurrent index inside transaction - Flyway default wraps in txn; migration fails. Fix: executeInTransaction=false per migration (Flyway) or runInTransaction: false (Liquibase).
• Liquibase rollback false confidence - Rollback SQL untested rots. Fix: Treat rollback as drill-only; prefer expand/contract forward fixes.
• Superuser migrator - Grants and RLS defaults wrong. Fix: app_migrator with SET ROLE app_owner.
• Different history per environment - Cherry-picked hotfix only in prod. Fix: Same git SHA through dev/staging/prod pipeline.

Alternatives

FAQs

Related

• Migrations Basics - versioned SQL principles
• Zero-Downtime DDL - concurrent operations
• Rollback Strategy - forward-only policy

External Related Links

• Awesome Postgres - Curated collection of tools, libraries, extensions, and resources.
• PostgreSQL Tutorials & Other Resources (Official List) - Curated list of recommended tutorials and external resources.
• PostgreSQL Mailing Lists / Community - Official mailing lists for help, development, and announcements.
• PostgreSQL 16 Administration Cookbook - Ciolli, Riggs et al.
• Learn PostgreSQL (2nd Edition) - Luca Ferrari & Enrico Pirozzi.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

3730363230327c343230316f6967637c6f692e73656469756765646f63