Manual:Upgrading PostgreSQL

Translate this page

The PostgreSQL versioning system consists of three numbers: major, minor, and revision. If either the major or the minor number has changed, it is considered a major upgrade, otherwise it is a simple binary upgrade.

Binary upgrade

A binary upgrade is when you are moving from one revision to another, for example from 8.2.7 to 8.2.8. No database dump is required. The steps to install the new version are:

Run 'make' if you are installing from source
Stop the database
Install the new binaries via 'make install' or your package management system
Start the database
Restart Apache

Visit the Special:Version page to make sure you are now running the newer version.

Major upgrades

When going from one major version to another, a dump and restore of the database is required. Here are the basic steps:

Set your wiki as read-only mode (use Manual:$wgReadOnly and Manual:$wgSiteNotice )
Dump the cluster (e.g. all databases) using the command pg_dumpall > filename
Install the new version ('make install' or with a packaging system)
Create a new data directory with createdb -O www-data wikidb
Customize your postgresql.conf file based on the old one, and update pg_hba.conf if needed. Configure a different port, then startup the database.
Import the new database by running psql -f filename
Change the port number to the old one (that the wiki is using) in postgresql.conf
Stop the existing database, and reload the new one (which should now be listening on the correct port)
Run the update.php script in the MediaWiki maintenance directory
Restart Apache so it picks up fresh connections
Put the wiki in read/write mode (remove the Manual:$wgReadOnly setting)
Test, test, test
Remove the old database data directory when satisfied with the new version

Moving to version 8.3

Moving from version 8.2 to 8.3 or greater can be challenging, as the tsearch2 module was moved into core, and there will be some import problems. Here's one way to do the upgrade. Comments of your own experience are welcome.

Dump the schema of your database using pg_dumpall --schema-only > schema_file. (but use a better file name)
Edit this file and replace all instances of public.tsvector with just plain tsvector. Find any 'CREATE INDEX' lines that have public.gin_tsvector_ops and remove that string entirely.
Install the new version ('make install' or use your packaging system)
Create a new data directory with the initdb -D newdatadir command
Configure postgresql.conf and pg_hba.conf as needed. Set a different temporary port, then start up the database.
Import the schema you created above by doing psql -p #### -f schema_file
- It is normal to see lots of tsearch2 errors. It is difficult to spot the more serious errors amongst those, but any errors referencing mediawiki tables (e.g 'page' or 'pagecontent') should be fixed.
For each MediaWiki database, update the trigger functions using this file
Set your wiki as read-only mode
Dump the data only by using pg_dumpall --data-only > data_file.
Import the data by running psql -p #### -f data_file
(everything is now the same as a normal upgrade above)
Change the port number to the old one (that the wiki is using)
Stop the existing database, and reload the new one (which should now be listening on the correct port)
Run the update.php script in the MediaWiki maintenance directory.
Restart Apache so it picks up fresh connections.
Put the wiki in read/write mode
Remove the old database data directory

Note: Mediawiki code plays fast and loose with datatypes; unfortunately version 8.3 of PostgreSQL is a lot pickier about such things. Adding the following to your database will help with 99% of the problem queries:

 CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
 CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;

Upgrade to 8.3 and remove obsolete tsearch2

Moving from PostgreSQL version 8.2 to 8.3 or greater can be challenging, as the tsearch2 module was moved into core engine, so tsearch2 objects should not be migrated.

PostgreSQL 8.3 provides a tsearch2 compatibility layer to run old applications, but with recent MediaWiki versions (tested on 1.13.0) this layer is not required.

This method was tested on a Debian Lenny, where PostgreSQL 8.2 and 8.3 can be installed at the same time. Debian provides some wrapper scripts so you can connect to the required database instance using the --cluster option. On other distributions you have to use the --port option to select the database instance.

This is the rationale of the process:

Dump the old database (PostgreSQL 8.2).
Make the list of objects contained into the dump.
Filter the list removing tsearch2 objects and some obsolete MediaWiki functions.
Make the new database into PostgreSQL 8.3.
Create the plpgsql language into the new database.
Add the MediaWiki functions.
Restore the database into PostgreSQL 8.3, using the filtered list.
Run the maintenance/update.php script.

Here are the details.

As the database owner, make the dump and generate the list of database objects. The simple script mediawiki_upgrade_dblist_filter [1] is used to remove objects that should not be restored.

If you installed the plpgsql language and the tsearch2 extension as the PostgreSQL administrator, while the rest of the MediaWiki database is owned by another user, you can safely filter-out all the objects owned by the PostgreSQL user.

pg_dump --cluster 8.2/main -Fc -U dbuser -W -d dbname > dbname.dump
pg_restore --cluster 8.2/main --list dbname.dump > dbname.dump.list
./mediawiki_upgrade_dblist_filter <dbname.dump.list >dbname.dump.list.filtered

As the PostgreSQL administrator (usually the PostgreSQL user), create the new database:

psql --cluster 8.3/main

CREATE USER "dbuser" PASSWORD '******';
CREATE DATABASE "dbname" OWNER "dbuser";
\connect dbname
CREATE LANGUAGE plpgsql

As the database owner, create the required MediaWiki functions and restore the database using the filtered list:

psql --cluster 8.3/main -U dbuser -W -d dbname -f sql/postgres/patch-tsearch2funcs.sql
pg_restore --cluster 8.3/main -U dbuser -W -L dbname.dump.list.filtered -d dbname dbname.dump

Finally run the MediaWiki update utility:

cd maintenance
php update.php

Useful references

Upgrading PostgreSQL 8.3 to 9.x

…