On Thu, Aug 8, 2024 at 11:19 AM IEM Network Operation Center (IOhannes m zmölnig) <noc@iem.at> wrote:
On 8/7/24 12:27, Odhiambo Washington via Mailman-users wrote:
On Wed, Aug 7, 2024 at 11:52 AM IEM Network Operation Center (IOhannes m zmölnig) <noc@iem.at> wrote:
is a feature-request warranted, or am i worrying too much?
A feature to automate your migrations? Have you consulted ChatGPT yet? :-)
should i ?-P
anyhow, i'm happy to say that my migrations have completed by now, and my new instance is now running with mailinglists previously hosted on either an MM2 instance or an older MM3 instance. so far i have not discovered any problems (well, apart from xapian eating all my (rather small, as this is only a VM) partition; but that is of course not related to anything we discussed here).
what i did was in a nutshell: thanks again, Odhiambo and Mark for your hints!
- create a dump of the database of the old mm3 instance (e.g. 'pg_dump -Fc mailman3web')
- drop any existing databases from the new mm3 instance
- import the dump into the new instance
- run db migrations
- import pck files from the old mm2 instance
- import archives from the old mm2 instance
however, i can report that my fears about migrating between mm3 instances by means of DB dumps have (at least partially) become true. 0. i'm using mailman3 as packaged in Debian, and PostgreSQL as the backend. before running the actual migrations, i've experimented a bit on the new instance (creating and deleting test lists), to see if the core configuration is working as expected. so i already had the databases up and running, filled with a bit of data, before attempting the migrations.
- for whatever reasons, the db-names have changed between 3.2.1 ("mailman3", "mailman3web") and 3.3.8 ("mailman3.db", "mailman3web.db"). i strongly suspect that this is a problem of the Debian packaging of mailman3 (i'm pretty sure that i only ever accepted the defaults when installing the mailman3 Debian packages).
As you've rightly discovered, that issue can only be addressed by the Debian package maintainers.
- since the db names changed, i tried to be clever (but i'm not a knowledgeable postgreSQL admin) and tried to only import the data-part of the dump ("pg_restore --data"), re-using the existing tables. this of course miserably failed.
I suspect failed because of the schema differences from 3.2.1 to 3.3.8. Well, I am also NOT experienced with PgSQL administration, but I think that for data to be imported from one table to another, the column definitions MUST match.
- my next attempt to import the data into an existing DB, was to rename
the new db to the old name, import and rename back. this also failed miserably, most notably because the import insisted on assigning key IDs that were already in use. (that is: because i had already created and tested test-lists, the new instance already contained an email with hyperkitty_email.id=1; the dump from the old instance also contained an email with hyperkitty_email.id=1; so the import failed. this example is made up from memory, as i have not kept all the error logs). so this also failed.
That is expected. Maybe you could have vacuumed the tables completely.
- i ended up, deleting the entire database, and import it from scratch.
after that, ran 'mailman-web migrate' to update the DB schema to the current one. this again failed with one hyperkitty-related migration that would create new tables. it turned out, that the new hosts uses postgreSQL-15.7.0 (the old one uses postgresql-11), and afaik since psql15 only a database *owner* can create new tables. after i made my mailman3-db-user the owner, all migrations succeeded.
That's a known issue here already. Something to do with access privileges to the SCHEMA public.
- finally i imported my mm2 mailinglists and archives, which went rather smoothly.
Sure. The only issue people have had with that was to do with some small inconsistencies in the archives. There is a tool that can be used to check the archives and fix such.
some of my problems obviously come from me not being a DB-manager by
profession (e.g. new constraints in psql15,...) but others, like "3.", seem to hint at a core problem in the suggested migration strategy: if the to-be-imported db contains unique-keys that are already taken, the import simply fails (or at best, you get a partial import). i do not see how such a migration can ever work if you try to merge to mm3 instances where the dbs have a considerable amount of entries in the same tables (as you will always get constraint conflicts).
And that is the point that I did not reach/go beyond when I talked about having consistency in the DBs. But if the destination server had no lists, then it would probably not have been too difficult. Again, that's stuff for DB deep-dive.
to summarize, migrations must:
- *first* import old mm3 data into an *empty* database (so you cannot import multiple mm3 instances into a single new one)
- import mm2 data afterwards
I believe there is a way around that because I recently did import an ML from one site to a live site.
hence my idea for a special export/import format (and command), that
would only contain the actual structured data, but without all the extra db-specific things (like unique ids for referencing between tables).
Oh, yes! I am sure the Developers will be happy to get such a tool as a contribution from the community.
-- Best regards, Odhiambo WASHINGTON, Nairobi,KE +254 7 3200 0004/+254 7 2274 3223 In an Internet failure case, the #1 suspect is a constant: DNS. "Oh, the cruft.", egrep -v '^$|^.*#' ¯\_(ツ)_/¯ :-) [How to ask smart questions: http://www.catb.org/~esr/faqs/smart-questions.html]