MM3 Sqlite to PostgreSQL migration?
Hi,
We are currently running a MM3 instance in "production" with a SQLite backend (at ulists.org). It was at first a test, but a lot of people have already started to use the service and now we are kind of stuck (but happy ;-) ).
First, I've seen a lot of DBLock issues in the logs and I think there also is a race condition happening with Held Messages that leads to subsequents 500 until you manually copy fake email files (If I'm correct, the held message file is deleted, then the SQL query is made but fails. The result is a system in an inconsistent state: there's a held message in DB, but no more on disk).
Second, I've tried to manually migrate to PostgreSQL but did not succeed because of a lot typing issues and differences between these two databases. Barry told me there's an SQLAlchemy port that is soon to be released, at least in alpha stage. Do you think it could help to achieve such a migration? I'm willing to help testing this new backend and, if someone can guide me with the MM stuff, write migration scripts.
Thanks and again, MM3 is really an awesome suite of software! :)
Guillaume
Hey Guillaume,
The new SQLAlchemy backend has just been merged as you've probably seen. It should upgread seamlessly from the previous Storm backend, but nothing has been done to make transition from SQLite to PostgreSQL possible. If I were you I would take the following steps: table from SQLite to PostgreSQL
- upgrade to the new SQLAlchemy-based branch and let mailman do the migration
- set your mailman config to point to the PostgreSQL DB
- start mailman to have it create the DB schema
- now run an SQLAlchemy-based script that would migrate the data of each
Step 4 is of course the most complicated, but you can iterate on each table using the metadata instance. Use 'Metadata.sorted_tables' to iterate in the proper order (based on foreign key references). You should only have to create one connection for each DB, run 'Metadata.reflect()' on each, iterate on the SQLite tables and insert in the PostgreSQL.
Let me know if that works. Good luck, Jim ;-)
Aurélien
2014-11-01 18:36 GMT+01:00 Guillaume Libersat <glibersat@sigill.org>:
Hey Guillaume,
The new SQLAlchemy backend has just been merged as you've probably seen. It should upgread seamlessly from the previous Storm backend, but nothing has been done to make transition from SQLite to PostgreSQL possible. If I were you I would take the following steps: table from SQLite to PostgreSQL
- upgrade to the new SQLAlchemy-based branch and let mailman do the migration
- set your mailman config to point to the PostgreSQL DB
- start mailman to have it create the DB schema
- now run an SQLAlchemy-based script that would migrate the data of each
Step 4 is of course the most complicated, but you can iterate on each table using the metadata instance. Use 'Metadata.sorted_tables' to iterate in the proper order (based on foreign key references). You should only have to create one connection for each DB, run 'Metadata.reflect()' on each, iterate on the SQLite tables and insert in the PostgreSQL.
Let me know if that works. Good luck, Jim ;-)
Aurélien
2014-11-01 18:36 GMT+01:00 Guillaume Libersat <glibersat@sigill.org>:
participants (2)
-
Aurelien Bompard -
Guillaume Libersat