Manual:PostgreSQL
From MediaWiki.org
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. MediaWiki requires PostGreSQL 8.1 or later, and a installation of the PHP PostGreSQL client library that supports protocol version 3 or later.
Certain scripts under the maintainance directory may not be very well updated for Postgres, so take care.
Contents |
[edit] Is PostgreSQL a good choice for MediaWiki?
Wikipedia uses MySQL so MediaWiki gets more testing on MySQL than PostgreSQL. While support for PostgreSQL is maintained by volunteers, most core functionality is working.
[edit] MediaWiki database migration
[edit] 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.
[edit] 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.
[edit] SQL compatibility
[edit] 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
[edit] Installing a fresh wiki on PostgreSQL
[edit] 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.
[edit] 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.
Now install postgresql 8.3:
You may not need all these packages, use aptitude show to see that they are and don't install them if you 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 # configuration for php PostgreSQL module extension=pgsql.so
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
[edit] 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!)
| Databases | |
|---|---|
| Engines: MySQL – PostgreSQL – SQLite – IBM DB2 | |
| Technical documentation: Schema (tables) – Access | |
