diff options
| -rw-r--r-- | changelog.d/13195.misc | 1 | ||||
| -rw-r--r-- | docs/postgres.md | 8 | ||||
| -rwxr-xr-x | synapse/_scripts/synapse_port_db.py | 34 |
3 files changed, 43 insertions, 0 deletions
diff --git a/changelog.d/13195.misc b/changelog.d/13195.misc new file mode 100644 index 0000000000..5506f767b3 --- /dev/null +++ b/changelog.d/13195.misc @@ -0,0 +1 @@ +Check that `auto_vacuum` is disabled when porting a SQLite database to Postgres, as `VACUUM`s must not be performed between runs of the script. \ No newline at end of file diff --git a/docs/postgres.md b/docs/postgres.md index cbc32e1836..f2519f6b0a 100644 --- a/docs/postgres.md +++ b/docs/postgres.md @@ -143,6 +143,14 @@ to do step 2. It is safe to at any time kill the port script and restart it. +However, under no circumstances should the SQLite database be `VACUUM`ed between +multiple runs of the script. Doing so can lead to an inconsistent copy of your database +into Postgres. +To avoid accidental error, the script will check that SQLite's `auto_vacuum` mechanism +is disabled, but the script is not able to protect against a manual `VACUUM` operation +performed either by the administrator or by any automated task that the administrator +may have configured. + Note that the database may take up significantly more (25% - 100% more) space on disk after porting to Postgres. diff --git a/synapse/_scripts/synapse_port_db.py b/synapse/_scripts/synapse_port_db.py index d3b4887f69..642fd41629 100755 --- a/synapse/_scripts/synapse_port_db.py +++ b/synapse/_scripts/synapse_port_db.py @@ -621,6 +621,25 @@ class Porter: self.postgres_store.db_pool.updates.has_completed_background_updates() ) + @staticmethod + def _is_sqlite_autovacuum_enabled(txn: LoggingTransaction) -> bool: + """ + Returns true if auto_vacuum is enabled in SQLite. + https://www.sqlite.org/pragma.html#pragma_auto_vacuum + + Vacuuming changes the rowids on rows in the database. + Auto-vacuuming is therefore dangerous when used in conjunction with this script. + + Note that the auto_vacuum setting can't be changed without performing + a VACUUM after trying to change the pragma. + """ + txn.execute("PRAGMA auto_vacuum") + row = txn.fetchone() + assert row is not None, "`PRAGMA auto_vacuum` did not give a row." + (autovacuum_setting,) = row + # 0 means off. 1 means full. 2 means incremental. + return autovacuum_setting != 0 + async def run(self) -> None: """Ports the SQLite database to a PostgreSQL database. @@ -637,6 +656,21 @@ class Porter: allow_outdated_version=True, ) + # For safety, ensure auto_vacuums are disabled. + if await self.sqlite_store.db_pool.runInteraction( + "is_sqlite_autovacuum_enabled", self._is_sqlite_autovacuum_enabled + ): + end_error = ( + "auto_vacuum is enabled in the SQLite database." + " (This is not the default configuration.)\n" + " This script relies on rowids being consistent and must not" + " 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;" + ) + return + # Check if all background updates are done, abort if not. updates_complete = ( await self.sqlite_store.db_pool.updates.has_completed_background_updates() |