[Mailman-Developers] MM3 Sqlite to PostgreSQL migration?

[Aurelien Bompard]

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:
1. upgrade to the new SQLAlchemy-based branch and let mailman do the
migration
2. set your mailman config to point to the PostgreSQL DB
3. start mailman to have it create the DB schema
4. now run an SQLAlchemy-based script that would migrate the data of each
table from SQLite to PostgreSQL

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 at sigill.org>:

> 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
> _______________________________________________
> Mailman-Developers mailing list
> Mailman-Developers at python.org
> https://mail.python.org/mailman/listinfo/mailman-developers
> Mailman FAQ: http://wiki.list.org/x/AgA3
> Searchable Archives:
> http://www.mail-archive.com/mailman-developers%40python.org/
> Unsubscribe:
> https://mail.python.org/mailman/options/mailman-developers/aurelien%40bompard.org
>
> Security Policy: http://wiki.list.org/x/QIA9
>