Liquibase ↔ Database Restore: Confirming Schema Sync After Backup Recovery

Direct answer

A restored backup is in sync with the Liquibase changelog only when the DATABASECHANGELOG table in the restored database matches the current changelog file and the active context/label filters are identical to those used when the backup was taken. Liquibase itself cannot distinguish “real schema drift” from “context‑only differences” because it validates solely against the DATABASECHANGELOG records and checksums; it does not diff the live schema unless you run liquibase diff.

Why false positives appear

• Context/label mismatch: If the backup was created while liquibase.contexts=prod (or a label) was active, the corresponding changesets are recorded in DATABASECHANGELOG. Restoring that backup under a different context (e.g., test) makes Liquibase report those changesets as “already executed”, not pending. The opposite—running with a context that excludes changesets that were applied at backup time—will show them as pending even though the schema objects exist.
• Checksum changes: Any edit to the changelog file after the backup (reformatting, comment changes, re‑ordering) changes the stored checksum. liquibase validate will flag a mismatch even if the physical schema is identical.
• Data‑level corruption: Liquibase tracks only DDL/DML execution, not row contents. Restored tables that lost rows or have corrupted data will pass Liquibase checks completely.

Steps to confirm true sync

1. Check pending changesets

   liquibase status --changelog-file=db/changelog.xml

   This reads the restored DATABASECHANGELOG and lists changesets not present there. If the list is empty, Liquibase believes the schema matches the changelog under the current context/label settings.
2. Validate checksums

   liquibase validate --changelog-file=db/changelog.xml

   Any checksum mismatch indicates the changelog file has diverged from the backup‑time version.
3. Diff against a reference snapshot (the only way to detect actual schema drift)

   liquibase diff --reference-url=jdbc:postgresql://ref-host/refdb \\\n  --url=jdbc:postgresql://restored-host/restoreddb \\\n  --changelog-file=db/changelog.xml

   The output shows missing/extra tables, columns, indexes, etc., independent of DATABASECHANGELOG.
4. Supplemental data‑integrity checks (run outside Liquibase)
   • Row‑count comparison for critical tables between the restored DB and a known‑good copy.
   • Checksum or hash of entire table data (e.g., md5sum of COPY ... TO STDOUT).
   • Application‑level smoke tests that exercise business logic.

When context/label matters

If the backup was taken with a non‑default context or label, you must reproduce that exact filter when running the commands above, otherwise status and validate will evaluate a different subset of changesets.

One diagnostic detail that changes the recommendation

What context/label values were active at backup time versus the current environment? If they differ, re‑run steps 1‑3 with the backup‑time context (e.g., --contexts=prod) to see the true pending set.

Assumptions & uncertainty

• Assumes Liquibase 4.x CLI behavior; older versions may have different flag names.
• Assumes the restored database includes the DATABASECHANGELOG table (i.e., a full schema+data backup, not a schema‑only dump).
• Data‑integrity checks are application‑specific; the commands above are generic examples.