Schema migration rollback gap in pfSense 2.7.x after config.xml restore

Question

Evaluate rollback safety after a schema-changing upgrade in pfSense 2.7.x and later, where the configuration database schema version is tracked in /conf/version.xml and upgrades trigger migration scripts that rewrite tables.

The documented restore path only replaces config.xml. It does not revert the underlying schema changes, so the database can remain in a newer state after a restore. The safe-rollback procedure described for 2.6.x involving export, reinstall of the prior version and import does not apply cleanly to 2.7.x due to changes in the FreeBSD base and package set, making a simple version downgrade unsupported. A failed mid-migration leaves a partially migrated state with manual intervention required and no automated schema rollback.

There is an unresolved behavior around the pfSense-updater plugin and retention of pre-upgrade schema artifacts.

Can the updater be instructed to preserve a copy of pre-upgrade schema files under /var/db/pfSense-upgrade/*.sql for manual rollback, and is that retention documented? Is there a supported rollback path for schema changes in 2.7.x that keeps package databases such as pfBlockerNG or Snort consistent with a restored config.xml? What is the expected compatibility boundary when config.xml is restored to a prior version after a schema migration has completed?

Tasadduq Burney · Answer

Direct answer
No. The pfSense-updater in 2.7.x cannot be instructed to preserve pre-upgrade schema dumps under /var/db/pfSense-upgrade/*.sql, and that retention is not documented. There is no supported rollback path for schema changes in 2.7.x that keeps package databases such as pfBlockerNG or Snort consistent with a restored config.xml. Restoring an older config.xml after a schema migration has completed leaves the system in a mismatched state: the configuration is old while the schema version and database tables remain new.
Confirmed facts

/conf/version.xml records the current schema version. Upgrade scripts are applied forward only and are not reversible.
Restoring config.xml does not revert version.xml or the underlying database schema. The database can remain in a newer state after a restore.
No documented mechanism exists to have the updater preserve pre-upgrade .sql schema files under /var/db/pfSense-upgrade/*.sql.
Package databases are not automatically re-initialized on config restore, so a schema downgrade can cause inconsistencies unless manually repaired.

Likely explanation
The /var/db/pfSense-upgrade directory is used for upgrade logs and temporary artifacts, not for durable schema dumps. Migration scripts rewrite tables in place. Because 2.7.x changes the FreeBSD base and package set, the 2.6.x procedure of export, reinstall prior version, import is not supported cleanly. A failed mid-migration leaves a partially migrated state with manual intervention required and no automated schema rollback.
Verification and safe checks for this case

Check schema version now: cat /conf/version.xml
Inspect upgrade artifacts: ls -la /var/db/pfSense-upgrade
Compare timestamps of config.xml and version.xml against a pre-upgrade backup if one exists.

Do not edit version.xml or run manual SQL against the config database without a full system backup. Manual rollback may corrupt the configuration database.
One missing diagnostic detail that changes the recommendation: was the 2.7.x upgrade completed successfully and do you have a pre-upgrade backup of both config.xml and version.xml taken immediately before the upgrade? A complete pre-upgrade system backup is the only safe recovery path; config.xml alone is insufficient.

Schema migration rollback gap in pfSense 2.7.x after config.xml restore

1 answer

Direct answer

Confirmed facts

Likely explanation

Verification and safe checks for this case

Your perspective belongs here.

0 question comments