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 Talk:MediaWiki on 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

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!)