Airflow db restore completes but no built-in check confirms recovered metadata matches the backup

Answer to the Question

After airflow db restore completes, the safest way to confirm that the restored metadata matches what was captured is to perform a two‑stage check:

1. Verify that the critical tables contain the same number of rows as in the backup (or that the row counts are within expected tolerances).
2. Confirm that the application can read encrypted values, which implicitly verifies the Fernet key and that the restore did not corrupt the encryption column.

Likely Explanation

Airflow’s backup format is a plain SQL dump of the PostgreSQL schema. The restore command simply feeds that dump back into the database. Because the dump contains only DDL and INSERT statements, there is no built‑in checksum. The absence of a verification step is a known limitation; most teams rely on manual row‑count or application‑level checks.

Confirmed Facts

• The dag, connection, variable, and task_instance tables hold the core orchestration state.
• Running airflow db check after the restore ensures the schema is compatible with the Airflow binary.
• Encrypted variables are stored in the variable table; decryption fails if the Fernet key has changed.

Verification Steps for a PostgreSQL Back‑up

1. Prepare a baseline. If you have a pre‑restore snapshot of the database or a list of row counts, keep it handy. If not, you can generate it now from the backup file by running the dump against a temporary PostgreSQL instance.

   psql -U airflow -d temp_restore -f backup.sql
   SELECT tablename, COUNT(*) FROM pg_tables WHERE schemaname='public' GROUP BY tablename;
   

2. Count rows in the restored database.

   psql -U airflow -d airflow
   SELECT 'dag' AS table_name, COUNT(*) AS rows FROM dag
   UNION ALL
   SELECT 'connection', COUNT(*) FROM connection
   UNION ALL
   SELECT 'variable', COUNT(*) FROM variable
   UNION ALL
   SELECT 'task_instance', COUNT(*) FROM task_instance;
   

   Compare each count to the baseline. A mismatch indicates incomplete restoration or a schema drift.
3. Check application connectivity.

   airflow db check
   airflow dags list
   

   The first command verifies the schema version; the second confirms the DAG processor can read the dag table.
4. Validate the Fernet key.
   • Choose a variable that is known to be encrypted (e.g., my_secret).
   • Attempt to read it:

     airflow variables get my_secret
     

     If the command prints the plaintext, the key matches; if it prints an error or garbled text, the key is wrong.
5. Optional – Diff airflow dags list output. Capture the output before restore (e.g., airflow dags list > dags_before.txt) and after restore (airflow dags list > dags_after.txt). A simple diff will show missing or altered DAG definitions. Note that the list reflects the current DAG processor state, so differences may also stem from code changes rather than the restore.

What to Ask Next

Do you have the exact row counts (or a checksum) of the critical tables from the backup before the restore? Knowing those numbers allows a precise comparison and removes ambiguity in the verification step.

Practical Tips

• Always run airflow db upgrade after restoring a database that came from an older Airflow release.
• Keep the Fernet key in a secure location and restore it before attempting to read encrypted variables.
• Automate the row‑count comparison in a CI job to catch drift early.

Conclusion

While Airflow does not ship a checksum validator, a small set of row‑count checks, schema version verification, and a Fernet‑key read test provide a defensible assurance that the restored metadata matches the backup.