moving from sqlite3 to postgresql
Hi, I installed mailman3 a few years ago on lists.sandelman.ca aka lists.tcpdump.org.
My upgrade was in part a response to the HTTP based attacks that used the signup form on mailman2 to attack people via SMTP with DOS. I know the IETF mailman2 install {now gone} had some kind of delay implemented in the web form before you could submit. I didn't understand why. I installed via debian packages, and I'd really prefer to stay with that. (Not what many maintainers would say, I know. Essential that it work if mailman3 is going to take over) There were problems, probably with the hyperkitty integration with subsequent updates seemed to solve.
I installed with sqlite3, since it seemed if all work was going to go through the mailman3 daemon, that it would handle all the concurrency issues. This has not been the case. I get weekly complaints about the database being locked. Sometimes this goes way on it's own, but sometimes it does not.
A good way to tickle this is to go the web interface, go to a list with 200 held messages, and then having told it to show me 100+ messages per page, to then tell it to discard them all. So slow. Before I realized what was going on, I'd open multiple tabs to try to make this concurrent.
So I tried to switch last week from sqlite3 to postgresql. I used pgloader with a configuration file attached below. This seemed easier than a sqlite3 dump/psql, as the SQL requires some massaging that seeemed to require more brainpower than I had at the time. I then edited /etc/mailman3/mailman.cfg to suit. I had set up the postgresql database under the "list" owner, so unix-domain socket permissions, and restarted.
I then saw some errors: lists not found! I visited the web interface. NO LISTS. DAMN. I put it back. I haven't figured out what went wrong yet. I will clone the VM and try again. I will report whatever I find here.
=== load database from '/var/lib/mailman3/mailman.db' into postgresql:///mailman3
with include drop, create tables, create indexes, reset sequences
set work_mem to '16MB', maintenance_work_mem to '512 MB';
ps: I'd pay some $$ money for email support consulting.
Michael Richardson writes:
My upgrade was in part a response to the HTTP based attacks that used the signup form on mailman2 to attack people via SMTP with DOS. I know the IETF mailman2 install {now gone} had some kind of delay implemented in the web form before you could submit. I didn't understand why.
Partly it was a successor to TMDA (a challenge-response system to make senders verify they could receive mail at the source address, which weeds out a lot of spam), and partly a hack where a mailing list (which cannot be posted to) is used as a database of "community members", ie, anybody who has passed the challenge for any list is allowed to post to all the "public" lists (and vice versa: getting banned from that list is "IETF death").
I installed via debian packages, and I'd really prefer to stay with that. (Not what many maintainers would say, I know. Essential that it work if mailman3 is going to take over)
That's really Debian's problem, and it's a hard one since the whole point of using Debian packages for mission-critical applications is that they're stable. Unfortunately, the email and web environments are not, and dealing with changes in them is often quite impractical given the Debian release cycle.
I installed with sqlite3, since it seemed if all work was going to go through the mailman3 daemon, that it would handle all the concurrency issues.
The problem is that sqlite3 is intended to be a single-threaded system, but Mailman 3 by default spawns around 15 daemons that may try to access the database. I would expect you would have warnings that the database is locked frequently. Not sure what you mean by "Sometimes this goes way on it's own, but sometimes it does not".
Note that our primary purpose for the sqlite3 configuration is so unit tests don't have to spin up a full-scale PostgreSQL or MySQL database.
So I tried to switch last week from sqlite3 to postgresql. I used pgloader with a configuration file attached below.
Can't help you with that offhand, never used pgloader. Whatever method you use to load up the pg database, I would recommend doing some psql cave-diving into the PostgreSQL database to confirm it's sane before trying to configure Mailman to use that database.
ps: I'd pay some $$ money for email support consulting.
See https://wiki.list.org/COM/Mailman%20consulting%20services
Per my .sig, I'm on the list above but please go through channels linked there rather than talking to me personally about business.
-- GNU Mailman consultant (installation, migration, customization) Sirus Open Source https://www.siriusopensource.com/ Software systems consulting in Europe, North America, and Japan
Stephen J. Turnbull <steve@turnbull.jp> wrote: >> I installed via debian packages, and I'd really prefer to stay with that. >> (Not what many maintainers would say, I know. Essential that it work if >> mailman3 is going to take over)
> That's really Debian's problem, and it's a hard one since the whole
> point of using Debian packages for mission-critical applications is
> that they're stable. Unfortunately, the email and web environments
> are not, and dealing with changes in them is often quite impractical
> given the Debian release cycle.Yes, I'm aware of this tusscle. There are some ways to split this up.
>> I installed with sqlite3, since it seemed if all work was going to
>> go through the mailman3 daemon, that it would handle all the
>> concurrency issues.
> The problem is that sqlite3 is intended to be a single-threaded
> system, but Mailman 3 by default spawns around 15 daemons that may try
> to access the database. I would expect you would have warnings that
> the database is locked frequently. Not sure what you mean by
> "Sometimes this goes way on it's own, but sometimes it does not".Meaning, sometimes whatever has the lock releases it everything proceeds. Sometimes, it's still stuck a few days later, and I restart the mailman daemons.
> Note that our primary purpose for the sqlite3 configuration is so unit
> tests don't have to spin up a full-scale PostgreSQL or MySQL database.Fair enough. It was not obvious when installing that I should not use sqlite3.
>> So I tried to switch last week from sqlite3 to postgresql.
>> I used pgloader with a configuration file attached below.
> Can't help you with that offhand, never used pgloader. Whatever
> method you use to load up the pg database, I would recommend doing
> some psql cave-diving into the PostgreSQL database to confirm it's
> sane before trying to configure Mailman to use that database.Yes, I'm gonna have to do that. At least I'll start by counting number of records in each table, and then see if there is some systematic lack.
>> ps: I'd pay some $$ money for email support consulting.
> See https://wiki.list.org/COM/Mailman%20consulting%20services
> Per my .sig, I'm on the list above but please go through channels
> linked there rather than talking to me personally about business.Understood.
Michael Richardson writes:
Stephen J. Turnbull <steve@turnbull.jp> wrote: > that they're stable. Unfortunately, the email and web > environments are not, and dealing with changes in them is > often quite impractical given the Debian release cycle.
Yes, I'm aware of this tusscle. There are some ways to split this up.
Sure but that's work for the Debian maintainers.
> Not sure what you mean by "Sometimes this goes way on it's > own, but sometimes it does not".Meaning, sometimes whatever has the lock releases it everything proceeds. Sometimes, it's still stuck a few days later, and I restart the mailman daemons.
This sounds to me like your runners are crashing, as Mark suggested. But even if that's resolved, you're still going to have a performance issue (lock contention for seconds or even minutes maybe[1]) even if you don't get stuck for days.
Fair enough. It was not obvious when installing that I should not use sqlite3.
Yeah, there's a sort of warning somewhere that we strongly recommend a full-featured RDBMS system, but we should probably make it a lot more prominent and explain why.
Yes, I'm gonna have to do that. At least I'll start by counting number of records in each table, and then see if there is some systematic lack.
I wonder if the routines in SQLAlchemy are expecting features (such as indexes) from your PostgreSQL DB that sqlite3 doesn't have, and so don't get copied across by pgloader. That kind of thing is why Mark suggests creating the DB by hand, then using Mailman to initialize, and finally do an SQL dump and load of the tables.
Footnotes: [1] Mailman "shouldn't" be holding on to locks while doing time- consuming processing such as sending mail, but I can't promise it doesn't.
-- GNU Mailman consultant (installation, migration, customization) Sirus Open Source https://www.siriusopensource.com/ Software systems consulting in Europe, North America, and Japan
- On 4/20/25 11:24, Stephen J. Turnbull wrote:
This sounds to me like your runners are crashing, as Mark suggested. But even if that's resolved, you're still going to have a performance issue (lock contention for seconds or even minutes maybe[1]) even if you don't get stuck for days.
Fair enough. It was not obvious when installing that I should not use sqlite3.
Yeah, there's a sort of warning somewhere that we strongly recommend a full-featured RDBMS system, but we should probably make it a lot more prominent and explain why.
Strong ACK. Anecdotally, because I went the SQLite3 -> PostgreSQL route just recently:
I've initially set up mailman3 and Hyperkitty with the SQLite3 backend, since the system/lists is/are relatively small and the lists are mostly low-volume, so I figured it would be a good idea to go with the least complicated option.
Importing archives (which tend to be big in comparison to the incoming volume since they go back to 2011) took on the order of 10 hours or more for big lists, with django threads dying every now and then and locking messages appearing in the log.
I've since setup PostgreSQL and reconfigured both mailman and Hyperkitty to use it. Instead of migrating the old data from the SQLite3 database to the PostgreSQL database, I'm just re-importing the mailman 2.1 data again, since it didn't change and the whole sqlite->pgsql move process seems so finicky and error-prone.
Importing one of the biggest archives is orders of magnitude faster at 43 % completion after just half an hour. In comparison, the same progress would have taken 6 hours with the SQLite3 backend.
As far as I can see, using the SQLite3 backend should be strongly discouraged, even for small systems, and this should be properly mentioned in the documentation. It's certainly useful in an automated testing suite, but there's probably no production system that would benefit from using the SQLite3 backend.
Mihai
- Michael Richardson (mcr@sandelman.ca) [250418 18:55]:
So I tried to switch last week from sqlite3 to postgresql.
I did that migration and cannot recommend it, rather direct start with postgres. Having said this, I wrote in 2021
==<== Stop all external connections to your systems for the whole migration.
Loading the database with any of the tools from postgresql in a fresh database, e.g. pgloader.
Setting up another database and dump both databases.
Merge the data from the pgloader-database and structure (sequences etc) from the new database.
Happy fixing of anything not loading into yet another database ( = the actual database), e.g. broken tables - especially the tables with intervals won't load without manual action. And make sure at least this database has encoding UTF8 (and especially not SQL_ASCII). E.g. by adding -E UTF8 to the createdb call. Or call your createcluster with e.g. --locale de_DE.UTF-8. That's recommended also if you start directly with postgresql as backend BTW :)
And then, just look where you get exceptions and keep on fixing until there are no anymore. And continue to look at your logfiles. Reenable connections sometimes during that and see what great exceptions can happen if e.g. lmtp doesn't recognire an list because there are non-ascii characters somewhere and your locale is broken (see above). ==>==
Good luck.
Andi
participants (4)
-
Andreas Barth -
Michael Richardson -
Mihai Moldovan -
Stephen J. Turnbull