When updating to a new version of the HA core I routinely select the option to do a backup before updating. The Recorder and LTSS databases are on an external PostgreSQL “homeassistant” database.
When doing the update I would like to have the external database snapshotted as well. I’ve searched in vain for some kind of hook that would allow me to run a command that will trigger a DB snapshot (or backup using pg_dump) at the proper time (i.e. after HA has stopped and no external database activity is expected) and wait until the snapshot is complete before continuing the update.
Would a trigger on homeassistant_final_write that runs a shell command to trigger the DB snapshot do the trick? I would not want the update to fail if it takes longer than 10 seconds to take the snapshot. I’ve seen schema update failures because of this, not to mention complaints about Recorder and LTSS init failures. Blindly retrying the update when this happens always seems like an invitation to DB corruption.
A related question is whether this approach will also work for vanilla backups (i.e. no Core update)?