Manual:Upgrading PostgreSQL

Upgrading your version of Postgres that is running MediaWiki is fairly simple, except when moving to 8.3 from a lesser version. The Postgres versioning system consists of three number: 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:


 * 1) Run 'make' if you are installing from source
 * 2) Stop the database
 * 3) Install the new binaries via 'make install' or your package management system
 * 4) Start the database
 * 5) 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:


 * 1) Set your wiki as read-only mode
 * 2) Dump the cluster (e.g. all databases) using the command pg_dumpall > filename
 * 3) Install the new version ('make install' or with a packaging system)
 * 4) Create a new data directory with initdb -D newdatadir
 * 5) 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.
 * 6) Import the new database by running psql -p #### -f filename
 * 7) Change the port number to the old one (that the wiki is using) in postgresql.conf
 * 8) Stop the existing database, and reload the new one (which should now be listening on the correct port)
 * 9) Run the update.php script in the MediaWiki maintenance directory.
 * 10) Restart Apache so it picks up fresh connections.
 * 11) Put the wiki in read/write mode
 * 12) Remove the old database data directory

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.


 * 1) Dump the schema of your database using pg_dumpall --schema-only > schema_file. (but use a better file name)
 * 2) 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.
 * 3) Install the new version ('make install' or use your packaging system)
 * 4) Create a new data directory with the initdb -D newdatadir command
 * 5) Configure postgresql.conf and pg_hba.conf as needed. Set a different temporary port, then start up the database.
 * 6) Import the schema you created above by doing psql -p #### -f schema_file
 * 7) * 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.
 * 8) For each MediaWiki database, update the trigger functions using this file
 * 9) Set your wiki as read-only mode
 * 10) Dump the data only by using pg_dumpall --data-only > data_file.
 * 11) Import the data by running psql -p #### -f data_file
 * 12) (everything is now the same as a normal upgrade above)
 * 13) Change the port number to the old one (that the wiki is using)
 * 14) Stop the existing database, and reload the new one (which should now be listening on the correct port)
 * 15) Run the update.php script in the MediaWiki maintenance directory.
 * 16) Restart Apache so it picks up fresh connections.
 * 17) Put the wiki in read/write mode
 * 18) Remove the old database data directory

Note: Mediawiki code plays fast and loose with datatypes; unfortunately version 8.3 of Postgres 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;