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. :-)