Upgrading PostgreSQL 8.2 to 8.3 (mediawiki)

Filed in: web Add comments

This upgrade was a real pain.
Though there are many resources on the web, which talk about this kind of upgrade (and the best one in my opinion is this one: http://julien.danjou.info/blog/index.php/post/2008/05/19/Upgrading-mediawiki-from-PostgreSQL-82-to-83), all of them fall short of giving a succinct but precise instruction for this upgrade. I tried to follow the aforementioned article, but it still gave some errors.
So I’ve done something similar, but a little bit different, finally importing the 8.2 database without a single error.
First of all, I dumped the original database in two parts – schema and data.
I took the schema part and divided it in two: schema definition and constraints and indexes. From the former I removed all references to tsearch2 functions, in the latter checked that the “search_path” is correct for every bulk of updates. Then I wanted to import the new tsearch2 functions that come with PostgreSQL 8.3.
So I wrote a simple script which I used in order to import the db several times, fixing bugs and eliminated all the problems which were still present:

export PGUSER=postgres
dropdb wikidb
createdb wikidb
psql -q wikidb < /usr/share/pgsql/contrib/tsearch2.sql
echo "Inserting wiki schema"
psql -q wikidb < wiki_schema.sql
echo "Inserting data"
psql -q wikidb < wiki_data.sql
echo "Inserting constraints"
psql -q wikidb < wiki_schema_constraints.sql

At the end all files were imported without a single error.
Then I discovered, that I’ll have to update mediawiki from 1.11.0 to 1.12.0, since the former doesn’t work with PostgreSQL 8.3
The update had only one problem (missing file – it’s mentioned in the link above), which was easy to remedy.
Upon using the mediawiki I found out, that I’ve to grant access to one of the tables.
It was probably due to the fact, that it was added to schema by user other than wikiuser:

GRANT ALL ON TABLE protected_titles TO wikiuser;

Seems to work fine so far.

2 Responses to “Upgrading PostgreSQL 8.2 to 8.3 (mediawiki)”

  1. Ian Says:

    Why this step?

    psql -q wikidb < /usr/share/pgsql/contrib/tsearch2.sql

    Doesn’t 8.3 include tsearch in core? I think you just installed the contrib version right on top of the in-core version, which will work fine but doesn’t solve the problem…


  2. Roman Says:

    If I get it right, then the actual implementation, e.g. a shared object is in fact a part of the PostgreSQL 8.3, while the /usr/share/pgsql/contrib/tsearch2.sql adds these functions to the database in question.


Leave a Reply