Manual:PostgreSQL

The purpose of this page is to collect information and experiences about running MediaWiki on PostgreSQL.

MediaWiki supports Postgres since 1.7 and currently big wikis run MediaWiki with Postgres with no technical problems, an example is Citizendium which as of 5 November 2008 uses MediaWiki 1.13.2 and PostgreSQL 8.3.

Certain scripts under the maintainance directory may not be very well updated for Postgres, so take care.

Is PostgreSQL a good choice for MediaWiki?
Wikipedia uses MySQL so MediaWiki gets more testing on MySQL than PostgreSQL. See also Manual talk:PostgreSQL.

However, someone once said that PostgreSQL is an open source project, while MySQL is an open source product, and PostgreSQL is under the BSDL while MySQL is under the GPL.

From MySQL to PostgreSQL
The mwdumper tool might help.

There is also mediawiki_mysql2postgres.pl in maintainance/postgres but it's not sure whether it really works.

From PostgreSQL to MySQL
See the above section, if you have any notes specific to this kind of conversion, communicate them here. A description of a successful PostgreSQL to MySQL conversion including a script can be found at http://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql.

SVN HEAD
With SVN HEAD of both MediaWiki and extensions as of 5 November 2008, the following may need to be checked for postgres compatibility: maintenance/postgres/compare_schemas.pl

Found FORCE INDEX string at line 39 of includes/specials/SpecialDeletedContributions.php Found IF string at line 1784 of includes/db/Database.php Found FORCE INDEX string at line 38 of extensions/DeletedContributions/DeletedContributions_body.php Found FORCE INDEX string at line 748 of extensions/FlaggedRevs/FlaggedRevs.class.php Found IF string at line 234 of extensions/Wikidata/OmegaWiki/Transaction.php Found CONCAT string at line 106 of extensions/Wikidata/OmegaWiki/SpecialSuggest.php Found CONCAT string at line 110 of extensions/Wikidata/OmegaWiki/SpecialSuggest.php Found FORCE INDEX string at line 1893 of extensions/BotQuery/query.php Found IF string at line 2434 of extensions/DynamicPageList/DynamicPageList2.php Found CONCAT string at line 2434 of extensions/DynamicPageList/DynamicPageList2.php Found IF string at line 2437 of extensions/DynamicPageList/DynamicPageList2.php Found CONCAT string at line 2437 of extensions/DynamicPageList/DynamicPageList2.php Found CONCAT string at line 2444 of extensions/DynamicPageList/DynamicPageList2.php Found IF string at line 2454 of extensions/DynamicPageList/DynamicPageList2.php Found CONCAT string at line 2454 of extensions/DynamicPageList/DynamicPageList2.php Found IF string at line 2462 of extensions/DynamicPageList/DynamicPageList2.php Found CONCAT string at line 2462 of extensions/DynamicPageList/DynamicPageList2.php Found CONCAT string at line 2541 of extensions/DynamicPageList/DynamicPageList2.php Found FORCE INDEX string at line 87 of extensions/GlobalUsage/GlobalUsageDaemon.php Found CONCAT string at line 127 of extensions/SyntaxHighlight_GeSHi/geshi/geshi/oracle11.php Found CONCAT string at line 114 of extensions/SyntaxHighlight_GeSHi/geshi/geshi/oracle8.php Found CONCAT string at line 108 of extensions/SyntaxHighlight_GeSHi/geshi/geshi/plsql.php Found CONCAT string at line 101 of extensions/SyntaxHighlight_GeSHi/geshi/geshi/mysql.php

tsearch2
The tsearch2 module needs to be installed to do the full-text searching of your wiki. How to install it depends a lot on how your Postgres installation is set up. Generally, once you have tsearch2 installed via your packaging system (e.g. postgresql-contrib-8.2.deb), you need to install tsearch2 into the database you are going to use. This generally involves running the tsearch2.sql script as a superuser inside the database you want to install to. For example, if your tsearch2.sql file is located at /usr/share/postgresql/contrib/tsearch2.sql, you have a superuser named postgres, and you are adding tsearch2 to the wikidb database, the following command can be used:

psql -U postgres wikidb -f /usr/share/postgresql/contrib/tsearch2.sql

Note that this command may issue some warnings, but they are generally safe to ignore. You can test if tsearch2 is successfully installed by running:

psql -U postgres wikidb -c "select relname from pg_class where relname ~ '^pg_ts'"

You should see four tables starting with "pg_ts". If not, then tsearch was probably not installed.

MediaWiki 1.14 with PostgreSQL 8.3 on Debian etch 4.0
First have a working backports setup: echo -e "\n" >> /etc/apt/sources.list echo http://www.backports.org/debian etch-backports main contrib non-free >> /etc/apt/sources.list

or use: emacs /etc/apt/sources.list and type this: deb http://www.backports.org/debian etch-backports main contrib non-free

aptitude update

aptitude install debian-backports-keyring

to install package:

aptitude -t etch-backports install "package"

NOTE THAT BACKPORTS DO NOT GET SECURITY UPDATES SOMETIMES AND ARE NOT AN OFFICIAL DEBIAN PROJECT, SO DONT USE BACKPORTS ON PRODUCTION.

There is no postGIS in 8.3 backports! Last postgis is 8.1!

Now install postgresql 8.3:

You may not need all these packages, use aptitude show to see that they are and don't instal them if youi don't need them.

aptitude -t etch-backports install postgresql-8.3 postgresql-contrib-8.3 phppgadmin postgresql-client-8.3

test whether it works: psql --version

now set up php to work with pgsql:

aptitude install php5-pgsql

cat /etc/php5/apache2/conf.d/pgsql.ini extension=pgsql.so
 * 1) configuration for php PostgreSQL module

Now set up superuser:

su - postgres postgres$ psql template1 Welcome to psql 8.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit

template1=# ALTER USER postgres PASSWORD 'SECRET'; ALTER ROLE template1=# \q

Now logout as postgres and login as debian root emacs /etc/phppgadmin/config.inc.php

make true to false in the variable about checking logins.

ln -s /usr/share/phppgadmin /var/www/phppgadmin

MediaWiki 1.14 with PostgreSQL 8.1 on Debian etch 4.0
You don't need all the packages I install, use aptitude show packagename to get an idea what one does and don't install it if you don't need it. aptitude install postgresql-8.1 postgresql-contrib-8.1 phppgadmin postgresql-8.1-postgis postgresql-plpython-8.1 postgresql-plperl-8.1 postgresql-client-8.1 Then do as for 8.3 above (without the backports or install!)