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
- 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 (3)
-
Andreas Barth -
Michael Richardson -
Stephen J. Turnbull