Check that auto_vacuum is disabled when porting a SQLite database to Postgres, as VACUUMs must not be performed between runs of the script.
#13195
Conversation
Signed-off-by: Olivier Wilkinson (reivilibre) <[email protected]>
reivilibre
force-pushed
the
rei/port_db_ensure_no_autovacuum
branch
from
6686191 to
2846b88
Compare
| " be used if the database could be vacuumed between re-runs.\n" | ||
| " To disable auto_vacuum, you need to stop Synapse and run the following SQL:\n" | ||
| " PRAGMA auto_vacuum=off;\n" | ||
| " VACUUM;" |
There was a problem hiding this comment.
Why do you need to vacuum here after disabling autovacuum? To ensure there is no vacuum in progress when the script runs?
There was a problem hiding this comment.
You can't change the auto_vacuum setting without VACUUMing if there are existing tables
There was a problem hiding this comment.
You can't change the
auto_vacuumsetting withoutVACUUMing if there are existing tables
Does that mean the vacuum should happen first?
There was a problem hiding this comment.
No; you set the PRAGMA then VACUUM to make it take effect.
An example line is synapse/synapse/_scripts/synapse_port_db.py Line 421 in b036685 but feel free to look all over the script — the tables that are being migrated are paginated by rowid. Notable is the |
The portdb script relies on
rowids being consistent.VACUUMdoes not uphold that.auto_vacuumis not enabled by default (unless SQLite was compiled with an option to make it default), but I'm aware thatsome community members manually VACUUM and I wouldn't be surprised if some of them enabled
auto_vacuumthemselves.Doing so is dangerous for the portdb script as it means that some rows may be omitted from the copy.
For safety's sake, let's prohibit
auto_vacuumwhen porting a database and document that manualVACUUMare also not to be done.