On 07/17/2018 10:05 PM, Joe Steeve wrote:

On Tue, 2018-07-17 at 22:04 -0700, Mark Sapiro wrote:

...

You would need to stop Mailman core and all Django processes including cron jobs and WSGI processes to ensure nothing is accessing the SQLite database, update the database section in mailman.cfg and the DATABASES settings in your Django settings, use SQLite tools to dump the relevant database(s) and PostgreSQL tools to create and load them, and finally restart Mailman core and all Django processes.

Fantastic. I'll give it a shot and update.

It occurs to me, it might be trickier than that in that dumping the SQLite database(s) and then loading them into PostgreSQL might result in table/column definitions in PostgreSQL that are inconsistent with the models because of differences in what SQLite and PostgreSQL support.

If this is the case, it might be necessary to let Mailman core and Django create (empty) PostgreSQL tables and then just load the data from SQLite.

-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan

--On 17. Juli 2018 um 22:04:12 -0700 Mark Sapiro <mark@msapiro.net> wrote:

On 07/17/2018 07:27 PM, Joe Steeve wrote:

...

I have an installation on SQLite. I want to migrate it to PostgreSQL. Is that feasible? Any gotchas I need to watch for?

It is feasible.

You would need to stop Mailman core and all Django processes including cron jobs and WSGI processes to ensure nothing is accessing the SQLite database, update the database section in mailman.cfg and the DATABASES settings in your Django settings, use SQLite tools to dump the relevant database(s) and PostgreSQL tools to create and load them, and finally restart Mailman core and all Django processes.

I have only now started playing around with Mailman 3.2, because I never managed to get the earlier releases to work, and reading through the documentation I thought that such situations could be handled with Alembic, as described here under "Database Migrations":

<http://mailman.readthedocs.io/en/latest/src/mailman/docs/database.html>

Is that not the case, and if not, in what cases can or should it be used?

.:.Sebastian Hagedorn - Weyertal 121 (Gebäude 133), Zimmer 2.02.:.
             .:.Regionales Rechenzentrum (RRZK).:.

.:.Universität zu Köln / Cologne University - ✆ +49-221-470-89578.:.

Sebastian Hagedorn writes:

I have only now started playing around with Mailman 3.2, because I never managed to get the earlier releases to work, and reading through the documentation I thought that such situations could be handled with Alembic, as described here under "Database Migrations":

<http://mailman.readthedocs.io/en/latest/src/mailman/docs/database.html>

Is that not the case, and if not, in what cases can or should it be used?

No. What is being discussed in the thread is changing the backend RDBMS, behind the SQL schema. Alembic operates in front of the SQL schema, managing the relationship between Python objects and the SQL schema.

As the "Migration" section states, Alembic is used to handle changes in the model (ie, the Python objects), so that they are reflected in the SQL used to access the backend. Users don't need to use it unless they customize the Python objects (ie, patch Mailman themselves). If *we* change the model between versions, Alembic will automatically be invoked to update the SQL, but the user doesn't need to do anything about it.

The potential issues Mark refers to involve differences in the way the different backend implementations handle SQL (this includes issues like default encodings for strings). These should be few because all the supported backends support standard SQL, but we can't guarantee there will be none. Differences in implementation also mean that you can't use the more efficient and compact dump formats that most implementations provide, because they are implementation- dependent. You need to use the textual format that is actually SQL programs that individually create each table, insert each record, create any needed indices, and so on.