PostgreSQL

Semantic MediaWiki 1.8[edit]

PostgreSQL support appears to be broken in SMW 1.8 (bug 42659), at least for PostgreSQL 9.1. You may want to use SMW 1.7.1 instead.

Prerequisites[edit]

MediaWiki with PostgreSQL 8.3 or higher must be installed beforehand. The database cluster must have been initialized with UTF-8 encoding. PL/PgSQL must be installed in the database.

When the default client encoding is other than UTF8 in postgresql.conf, MediaWiki needs a patch.

Prior to setup, ensure that the MediaWiki tables are accessible without explicit schema qualification. If typing \dt in psql does not list dozens of MediaWiki tables, then you may need to adjust the search path. For example:

 ALTER DATABASE wikidb SET search_path TO mediawiki,public;
 -- where "mediawiki" is the schema name as listed by \dn
 -- then restart psql. \dt should now list the tables

The MediaWiki user does not need to be the owner of the database, but appropriate rights are necessary, e.g. to create/execute PL/PgSQL scripts.

Installation[edit]

Installation should proceed as usual. Before creating any pages, the following statement must be executed against the server (e.g. using psql or PgAdmin):

 select setval('smw_ids_smw_id_seq', (select max(smw_id) from smw_ids), 'true');

1.4.2 still contains a bug in includes/storage/SMW_SQLStore2.php, apply the following patch:

940c940
<               $res = $db->query('(SELECT smw_id, smw_title, smw_sortkey, COUNT(*) as count FROM ' .
---
>               $res = $db->query('(SELECT smw_id, smw_title, COUNT(*) as count, smw_sortkey FROM ' .

PostgreSQL 9 and bytea_output[edit]

Beginning with PostgreSQL 9, the output format of the bytea datatype must be set to 'escape'. You can change this parameter for the database you are using by running the following command, replacing 'mywiki' with the name of your database:

 ALTER DATABASE mywiki SET bytea_output = 'escape';

Alternatively, you can change it for all databases by adding this to the end of your postgresql.conf file (and reloading Postgres):

 bytea_output = 'escape' # Semantic MediaWiki (1.5.6) still expects the old 'escape' format

Otherwise the output will default to the new 'hex' format. Semantic properties of type 'text' will be garbled in query results. Instead of seeing text, you will see large numbers such as this: \x506f736974696f6e2077697468207265736f75726365732061747461636865642e.

Limitations[edit]

Currently the PostgreSQL support has only been tested with the login user being the owner of the schema that contains the MediaWiki tables.

Example installation[edit]

(installation notes for installing semanticmediawiki/postgresql from scratch on CentOS 5.2)

In this example apache is running in a OpenVZ partition contained within a XEN 32bit paravirtualized DomU running on the 64bit Dom0 Main Server. The PostgreSQL database server is running on a different host without virtualization.

Please note that CentOS 5.2 does not include PostgreSQL 8.3 by default, use the packages from http://yum.pgsqlrpms.org (and corresponding php-pgsql packages as well).

Here is what needs to be done:

create a virtual web server on apache
create a user (e.g. semanticweb.org or whatever your wiki is about) within your postgresql database, having superuser privileges
ensure that the user can successfully access the database from the webserver by adding it in pg_hba.conf on the database server
create a schema named like the user (e.g. semanticweb.org) within your database, having user semanticweb.org as owner
download and unpack the mediawiki software, e.g. http://download.wikimedia.org/mediawiki/1.14/mediawiki-1.14.0rc1.tar.gz
move the unpacked files to your webserver root, chgrp -R apache *
chmod g+w config, access http://[your server]/config/index.php
Verify settings; note that eaccelerator may not be recognized (despite the fact that phpinfo() reports it's installed)
after successful installation/table creation move LocalSettings.php to the parent directory from config/
apply the patch to ensure MediaWiki uses UTF8 encoding on the database in includes/db/DatabasePostgres.php
download and install SemanticMediaWiki according to the INSTALL file
after "Database installation and upgrade", adjust the sequence in the database (see "Installation" above)

now your installation should work properly.