Mailman3 Has 2 Databases. Whoops.

  • 2026-03-30
  • 5-minute read

At May First we have been carefully planning our migration of about 1200 lists from mailman2 to mailman3 for almost six months now. We did a lot of user communications, had several months of beta testing with a handful of lists ported over, and everything was looking good. So we kicked off the migration!

But, about 15% of the way through I started seeing sqlite lock errors. Wait, what? I carefully re-configured mailman3 to use postgres, not sqlite. Well, yes, but apparently that was for the database managing the email list configuration, not the database powering the django web app, which, incidentally, also includes hundresds of gigabytes of archives. In other words, the one we really need in postgres, not sqlite.

Moving from sqlite to postgres

Well that sucks. We immediately stopped the migration to deal with this.

I noticed that the web is full of useful django instructions on how to migrate your database from one database to antoher. However, if you read the fine print, those convenient looking “dumpdata loaddata” workflows are designed to move the table definitions and a small amount of data. In our case, even after just 15% of our lists moved, our sqlite database was about 30GB.

I considered some of the hacks to manage memory and try to run this via django, but eventually decided that pgloader was a more robust option. This option also allowed me to more easily test things out on a copy of our sqlite database (made while mailman was turned off). This way I could migrate and re-migrate the sqlite database over and over without impacting our live installation until I was satisfied it was all working.

My first decision was to opt out of pgloader’s schema creation. I used django’s schema creation tool by:

  • Turning off mailman3 and mailman3-web and changing the mailman web configuration to use the new postgresql database.
  • Running mailman-web migrate
  • Changing the mailman web configuration back to sqlite and starting everything again.

Note: I tried just adding new database settings in the mailman web configuration indexed to ’new’ - django has the ability to define different databases by name, then you can run mailman-web migrate --database new. But, during the migration, I caught django querying the sqlite database for some migrations that required referencing existing fields (specifically hyperkitty’s 0003_thread_starting_email). I didn’t want any of these steps to touch the live database so I opted for the cleaner approach.

Once I had a clean postgres schema, I dumped it so I could easily return to this spot.

Next I started working on our pgloader load file. After a lot of trial and error, I ended with:

LOAD DATABASE
    FROM sqlite:///var/lib/mailman3/sqlite-postgres-migration/mailman3web.clean.backup.db
    INTO postgresql://mailmanweb:xxxxxxxxxxx@localhost:5432/mailmanweb

WITH data only,
    reset sequences,
    include no drop,
    disable triggers,
    create no tables,
    batch size = 5MB,
    batch rows = 500,
    prefetch rows = 50,
    workers = 2,
    concurrency = 1

SET work_mem to '64MB',
    maintenance_work_mem to '512MB'

CAST type datetime to timestamptz drop default drop not null,
    type date to date drop default drop not null,
    type int when (= precision 1) to boolean using tinyint-to-boolean,
    type text to varchar using remove-null-characters;

The batch, prefetch, workers and concurreny settings are all there to ensure memory doesn’t blow up.

I also discovered that I had to make some changes to the schema before loading data. Mostly truncating tables that the django migrate command populated to avoid duplicate key errors:

TRUNCATE TABLE django_migrations CASCADE;
TRUNCATE TABLE django_content_type CASCADE;
TRUNCATE TABLE auth_permission CASCADE;
TRUNCATE TABLE django_site CASCADE;

And also, I had to change a column type. Apparently the mailman import process allowed an attachment file name that exceeds the limit for postgres, but was allowed into sqlite:

ALTER TABLE hyperkitty_attachment ALTER COLUMN name TYPE text

When pgloader runs, we still get a lot of warnings from pgloader, which wants to cast columns differently than django does. These are harmless (I was able to import the data without a problem).

And there are still a lot of warnings along the lines of:

2026-03-30T14:08:01.691990Z WARNING PostgreSQL warning: constraint “hyperkitty_vote_email_id_73a50f4d_fk_hyperkitty_email_id” of relation “hyperkitty_vote” does not exist, skipping

These are harmless as well. They appear because disable triggers disables foreign key constraints. Without it, we wouldn’t be able to load tables that require values in tables that have not yet been populated.

After all the tweaking, the import of our 30GB sqlite database took about 40 minutes.

Final Steps

I think the reset sequences from pgloader should take care of this, but just in case:

mailman-web sqlsequencereset hyperkitty mailman_django auth | mailman-web dbshell

And, just to ensure postgres is optimized, run this in the psql shell:

ANALYZE VERBOSE;

Last thoughts

I understand very well all the decisions the mailman3 devs made in designing the next version of mailman, and if I was in the same place I may have made them the same ones. For example, separating the code running the mailing list from the code managing the archives and the web interface makes perfectly good sense - many people might want to run just the mailing list part without a web interface. And building the web interface in django makes a lot of sense as well - why re-invent the wheel? I’m sure a lot of time and effort was saved by simply using the built in features you get for free with django.

But the unfortunate consequence of these decisions is that sys admins have a much harder time. Almost everyone wants the email lists along with the web interface and the archives. But nobody wants two different configuration files with different syntaxes and logic, not to mention two different command lines to use for maintenance and configuration with completely different APIs. Trying to understand how to change a default template or set list defaults requires a lot of research and usually you have to write a python script to do it.

I have finally come to the conclusion that mailman2 is designed for sys admins, while mailman3 is designed for developers.

Despite these short comings, I am impressed with the community and their quick and friendly responses to the questions of a confused sys admin. That might be more valuable than anything else.

debiansysadminemail

comments