Generating a Liquibase changelog from an existing database with diffChangeLog

Desired outcome

Create a Liquibase formatted changelog (XML, YAML, JSON or SQL) that captures the current schema of a source database so it can be version‑controlled and applied to other environments.

Prerequisites

• Liquibase CLI installed and available in your PATH (tested with Liquibase 4.x).
• A JDBC driver for the target database type placed on the class‑path (e.g., postgresql-42.7.3.jar for PostgreSQL).
• Read‑only access to the source database (the user only needs to query metadata tables).
• Network connectivity from the machine running Liquibase to the database host.
• A writable directory where the generated changelog file will be stored.

Procedure

1. Open a terminal on the machine where Liquibase is installed.
2. Construct the JDBC URL for your source database. Replace placeholders with your actual values:
   • jdbc:postgresql://host:5432/mydb for PostgreSQL
   • jdbc:mysql://host:3306/mydb for MySQL
   • jdbc:oracle:thin:@//host:1521/ORCLPDB1 for Oracle
3. Run the diffChangeLog command, directing output to a file:

   liquibase \
     --changeLogFile=./output/changelog.xml \
     --url=jdbc:postgresql://db-host:5432/mydb \
     --username=liquibase_user \
     --password=******** \
     diffChangeLog

   The command inspects the source database, generates appropriate changeSets (e.g., createTable, addColumn, createIndex) and writes them to the file specified by --changeLogFile.

4. If you prefer a different format, change the file extension and optionally add --outputDefaultCatalog or --outputDefaultSchema as needed. For YAML:

   liquibase --changeLogFile=./output/changelog.yaml diffChangeLog

Expected checks

• Open the generated changelog and verify that each table, column, index, and constraint from the source appears as a changeSet. Missing objects may indicate a JDBC driver mismatch or insufficient privileges.
• Run a dry‑run against an empty schema to ensure the changelog can be applied:

  liquibase --url=jdbc:postgresql://empty-host:5432/emptydb \
    --username=liquibase_user \
    --password=******** \
    updateSQL

  Inspect the SQL output for syntax errors; the statement list should contain only DDL that creates the schema.

• Optionally validate the changelog internal consistency:

  liquibase validate

  Any validation warnings should be reviewed and corrected before using the file in production.

Recovery options

• If the diff includes objects you do not want (e.g., temporary tables, extensions), edit the changelog file to remove or comment out the unwanted changeSets.
• Re‑run diffChangeLog with filters to limit the scope:

  liquibase --changeLogFile=./output/changelog_filtered.xml \
    --includeObjects=table:public.* \
    --excludeObjects=table:tmp_.* \
    diffChangeLog

  The --includeObjects and --excludeObjects arguments accept comma‑separated lists of type:namePattern patterns.

• If the source database was altered during the diff (unlikely with read‑only access but possible if the user has write rights), restore the database from a known good backup and repeat the diff with refined filters.

Limitations

• Very large schemas can produce changelogs that exceed available memory; consider splitting the diff into multiple runs using object filters or generating changeSets per schema.
• The JDBC driver version must closely match the database server version. A mismatch may cause missing metadata (e.g., column types) and lead to incorrect or incomplete changeSets.
• Some database‑specific objects (e.g., Oracle materialized views, PostgreSQL extensions) may not be fully captured; manual addition may be required.

Practical verification

1. Create a fresh, empty target database (same vendor and version as source).
2. Apply the generated changelog:

   liquibase --url=jdbc:postgresql://target-host:5432/targetdb \
     --username=liquibase_user \
     --password=******** \
     update

3. Run liquibase status; it should report "No pending changesets".
4. Optionally run a second diffChangeLog comparing the source and target databases; an empty output indicates the schemas match.
5. Check liquibase.log for any WARN or ERROR entries during the diff and update steps; absence of severe messages indicates success.