Upgrading MediaWiki from PostgreSQL 8.2 to 8.3

jd — Mon, 19 May 2008 11:56:00 +0200

With my server migration I switched from PostgreSQL 8.2 to 8.3. However, I've installed MediaWiki for the awesome wiki on 8.2, but dumping from 8.2 and restoring to 8.3 just failed because of the tsearch2 extension, which is now part of PostgreSQL 8.3.

With the help of my friend Dimitri, the pgloader author, I managed to migrate the database from PostgreSQL 8.2 to 8.3. Here is how I did.

This was done with MediaWiki 1.12, and I doubt that older version supports PostgreSQL 8.3.

Dump the database schema

First, you need to dump the database schema, and then, the database data. We need to do it in two steps because we need to change the schema a bit. I assume that MediaWiki is installed in the mediawiki schema:

% pg_dump -s -n mediawiki wikidb > wikidb-schema.sql

Convert the schema

Then, edit your wikidb-schema.sql. When you installed tsearch2, you probably did it in the public schema like I did. So it created two or three data types used by MediaWiki wich are named public.datatype. These tsearch2 datatypes are now part of PostgreSQL, so they're in the pg_catalog schema. You just need to replace a few lines with public.datatype with pg_catalog.datatype. For example public.tsvector is now pg_catalog.tsvector. For your information gin_tsvector_ops is now pg_catalog.tsvector_ops.

Finished? Good, but that's not all. You need to split up the file in two parts: one with the tables creation, and another one with the constraints creation. Why? Because otherwise the data insertion will fail because of these constraints. That's not so hard, just split your files in wikidb-schema.sql with all CREATE TABLE and so one, and then you see that the SQL file begins to do some ADD CONSTRAINTS, just put this in another file, named wikidb-constraints-schema.sql. You'll also need to set the namespace before executing command in your second file, so do not forget to add a SET search_path = mediawiki, pg_catalog; line at the beginning.

Dump the database data

That's easy:

% pg_dump -a -n mediawiki wikidb > wikidb-data.sql

Create your new database

That's easy too:

% createuser -S -D -R -P -E wikiuser #(then enter the password)
% createdb -O wikiuser wikidb
% createlang plpgsql wikidb

Restore the database

That's even easier, but you need to respect this order, otherwise it will fail:

# Restore schema first
% psql wikidb < wikidb-schema.sql
# Then restore dat
% psql wikidb < wikidb-data.sql
# Then restore constraints
% psql wikidb < wikidb-constraints-schema.sql

Run update.php

Just go the the maintenance directory. Then you can try to run update.php, but it'll fail.

% php update.php
[...]
Could not open "/var/www/awesome.naquadah.org/wiki/maintenance/postgres/archives/patch-tsearch2funcs.sql".

Well it seems that MediaWiki people just forgot this file in the 1.12 archive.. You can grab it on the SVN repository here. When you've put it in the good directory, really run:

% php update.php
[...]

You should see no error. And now it works.

Give us some kudos

This is the last and hardest part of this tutorial. Since everything works, you should give kudos to Dimitri and me.

Boarding the Prometheus

jd — Sun, 18 May 2008 10:26:00 +0200

As I said a month ago, my main server Delmak was dying. Well it still runs (proof: you could read this blog some days ago).

Thanks to friends I host for free, they've kindly given enough money to buy a brand new server (C2D E8400, 4 GB RAM, 2x500 GB RAID 1) in order to replace the good old Delmak. This new box has been named Prometheus after the only BC-303 class battleship ever built.

Delmak was used to mainly run as a Web, mail and databases server. I decided to do use this server switch to change the server software I use.

The first mail server I setup was based on Exim 3, courier-{imap,pop}{-ssl,} with userdb files. That was... rough. Later I switched to Exim 4, using vexim, and MySQL as a back-end. That was something like 3 years ago I guess. Since then I never really touched that back. I added spamassassin and clamav filtering some months after, because some users asked for it. That's all.

So this week, I decided to switch away from this configuration. I do not understand Exim anymore anyway, so I decided to use Postfix which I often use and administrate at work. Obviously, I also now use PostgreSQL as database back-end, since it rocks, and since Postfixadmin supports it. By the way, be aware that the Debian package of postfixadmin is crappy (the configuration file is readable by anyone by default, with the database password in it). I also set up postgrey which is quite nice and efficient.

Well, then was time for amavisd-new installation, but I did not do it. Seriously, amavisd-new configuration is a bloody mess, as the language it is written in (yes, Perl).

So I switched to dspam which I heard is nice. Well, it seems to be for now, since it even supports clamav daemon usage directly, which is very very nice because that means I do not have to set up another thing for that.

I also switched from courier to dovecot, mainly because the latter seems to be faster and lighter. I then changed the default virtual_transport to Dovecot LDA. The main advantage of this is that the LDA updates the Dovecot index while delivering. It also supports quota, which I do not use and plug-ins, like the Sieve language for mail filtering.

So I decided to change my procmailrc to a new Sieve filter. My procmailrc is quite small since I only use regex to match lists and some mail address, so it has only something like 12 rules. And well, I did not do it since I discovered after some googling that Dovecot implementation of Sieve is grabbed from Cyrus which does not support variables for now. That means that the following procmailrc code:

:0:
* ^X-Mailing-List: <debian-.+@lists.debian.org>
* ^X-Mailing-List: <debian-\/[^@]+
list-debian-$MATCH/

which will translate to:

require [ "regex", "variables", "fileinto" ]
if header :regex "X-Mailing-List" "<debian-(.+)@"
{
    fileinto "lists.debian.${1}";
    stop;
}

But that won't work since Dovecot Sieve implementation does not support "variables". Well, since I'm not ready to list all the lists I'm subscribed to, Sieve is a no-go for now. I'll stick with procmail.

jd:/dev/blog - Tag - postgresql