> Do you know it's actually easy to treat the database as an ephemeral, versioned object? Nobody I've talked to does. You just back up the logical database, and rename the database name in the backup to a unique string (including a version, or datestamp, etc). Now you have versioned, uniquely-named database, so you can do immutable infrastructure. Load this backup into a database server (even the "live" database server, as it won't conflict with the old db name). Start a new WordPress container and pass env vars pointing to the new database name. Now you can pair a snapshot of the code with a snapshot of the database. Upgrade or downgrade in seconds, with confidence. (that is, after you've done all the manual work to upgrade, test, and fix in an ephemeral environment)
There is a good reason why nobody does that: it makes very little sense on any sort of web platform more complex than a static website. Indeed, as the WordPress database schema is quite normalized especially around metadata and taxonomies, there are many INSERT/UPDATE queries running/queued basically all the time on a busy website.
For example, 1ms after you've snapshot your DB and "versioned it" (renamed it rather) locally, it's already obsolete because 2 users have just logged in and their last logon timestamp got updated as metadata stored in the _usermeta table. Switch to the versioned snapshot in the meantime and you lost that information. This is Bad(r).
Even then, say 30s after making the snapshot and before you've "deployed" the code upgrade, a cronjob has triggered an image optimizer plugin, inserting various optimized copies of the image objects in the main _posts table (it also updated the metadata of all those images). Rollback the DB because your deployment caused a crash and you've lost images. Again, a big no-no.
The simplest WordPress-safe upgrade path in case of code changes impacting the database is for the code to support rollup/rolldown migration queries on the database: rename a meta_key when upgrading and rename it back when downgrading.
Use WP abstraction functions to interact with the database, and generally treat the database itself as a blind storage medium, to be restored only in extreme cases.
This is my point. The problem is WordPress's design. Despite that, there are better practices... but the best practice would be not to use a design from 20 years ago.
There is a good reason why nobody does that: it makes very little sense on any sort of web platform more complex than a static website. Indeed, as the WordPress database schema is quite normalized especially around metadata and taxonomies, there are many INSERT/UPDATE queries running/queued basically all the time on a busy website.
For example, 1ms after you've snapshot your DB and "versioned it" (renamed it rather) locally, it's already obsolete because 2 users have just logged in and their last logon timestamp got updated as metadata stored in the _usermeta table. Switch to the versioned snapshot in the meantime and you lost that information. This is Bad(r).
Even then, say 30s after making the snapshot and before you've "deployed" the code upgrade, a cronjob has triggered an image optimizer plugin, inserting various optimized copies of the image objects in the main _posts table (it also updated the metadata of all those images). Rollback the DB because your deployment caused a crash and you've lost images. Again, a big no-no.
The simplest WordPress-safe upgrade path in case of code changes impacting the database is for the code to support rollup/rolldown migration queries on the database: rename a meta_key when upgrading and rename it back when downgrading.
Use WP abstraction functions to interact with the database, and generally treat the database itself as a blind storage medium, to be restored only in extreme cases.