Manual talk:PostgreSQL

PostgreSQL and MW 1.13

I would not say that PostgreSQL with MediaWiki is working smoothly. I am talking about the current stable release http://svn.wikimedia.org/svnroot/mediawiki/branches/REL1_13/phase3 (rev 45131). To do the following tests, I deactivated any extensions. I have Postgresql 8.3 (Debian) running with MediaWiki and mod_php with Apache 2.2:

• The way, string escaping is done causes warnings in the postgresql error log ("WARNING: nonstandard use of \\ in a string literal at character 147"). However, it works and the warning could be deactivated in the configuration file of PostgreSQL but the correct way would be to use the character E before the string if it contains encoded characters.
• The number of "BEGIN" and "COMMIT" is not equal. There is one "additional" COMMIT at the end that also causes a warning in the log. Again, this is ugly but it works.
• During one single request (view) of a very small, simple page without external links, several database accesses occur that are unnecessary in my opinion: E.g., the key "winterrodeln_wiki:messages:de-formal:status" from the objectcache table was 9 times deleted and 4 times inserted (with the equal content). (I told Postgresql to show me the SQL commands in the log file so I discovered it).
• For the described page view, 5 fresh connections to the database were made and they were not recycled on following requests.
• BLOBs are escaped twice (I'm not 100% sure about this, but I have the feeling that this is the case when I studied the code).
• In DatabasePostgres.php comments like "# FIXME: HACK HACK HACK HACK debug" or "TODO: [...] not sure if the following test really trigger if the object" can be found.
• The performance on our server with MediaWiki/PostgreSQL is very poor (1 GHz/1 GB Machine): "Save page" lasts several seconds (especially when many users are requesting pages) and we already had a server crash because MediaWiki could not provide the pages fast enough.
• After changing to mySQL (see link below), the performance was good so it for sure had to do with the database.
• I wanted to improve the MediaWiki-PostgreSQL code, but I gave up because the database abstraction layer seemed too tailored to MySql: E.g. the function "addQuotes" that is used inside "makeList" and other places should, well, encode strings. _But_: For postgresql, (1) I need a handle to the database to do to proper encoding and (2) I should use different encoding functions for strings and BLOBS - but how should I distinguish it? Maybe I don't understand the concept well enough - but it's not that easy because many functions have a "@todo document function" as their description...

Please don't think that I'm disappointed of MediaWiki: I think it's great! (thank you all!)

I just would not recommend using Postgresql with MediaWiki until the integration gets, well, more mature. ;-) Phispi 21:27, 7 January 2009 (UTC)

mySQL to PostgreSQL

I described the process of changing from mySQL to PostgreSQL at http://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql. Phispi 22:31, 14 January 2009 (UTC)

The link above is for the opposite... postgres -> mysql.

In addition to Phispi's link above, I found a page on WikiBooks that was quite helpful:

• b:en:Converting MySQL to PostgreSQL

--DanielRenfro 18:53, 6 May 2011 (UTC)

tsearch2

I did this once; if this is the right page to read about why the postgreSQL install of M-W is complaining about tsearch, it might be nice if it had a link to ... where did I find it? on postgresql.org I think ... hafta go back through that process I guess.

assuming you have already created the database and the user that will operate the wiki, then you have to, as a suitably priviledged DB-user, issue the command:

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

assuming that the SQL command is located as above, in /usr/share/.... however the wiki dbuser needs also to be priviledged to access/use the newly created tsearch2 DB tables.

then when you hit 'install mediawiki' it actually works, and is using your postgres server instead of needing mysql

So, does it work well or not?

This page needs an executive summary.

Yes it works. 62.140.253.6 08:49, 23 August 2009 (UTC)

Works for me with pg 9.2. —The preceding unsigned comment was added by 194.187.175.249 (talk • contribs) 15:02, 23 December 2013 (UTC)

Performance

To enjoy reasonable performance (especially while using importDump.php) I had to:

create index natmakaONE on pagelinks(pl_namespace,pl_title);
create index natmakaTWO on imagelinks(il_to);
create index natmakaTHREE on categorylinks(cl_to,cl_sortkey);

Natmaka 21:35, 10 March 2011 (UTC)

From pg8 to pg9??

There are some Mediawiki HELP (!!) to upgrade!?? — Preceding unsigned comment added by 187.106.249.159 (talk • contribs) 2012-01-07T11:00

What's not working for you? --Tim Landscheidt 12:30, 19 April 2012 (UTC)

Sequences

I notice maintenance/postgres/tables.sql has this stuff at the top:

DROP SEQUENCE IF EXISTS user_user_id_seq CASCADE;
DROP SEQUENCE IF EXISTS page_page_id_seq CASCADE;
DROP SEQUENCE IF EXISTS revision_rev_id_seq CASCADE;
...

So, I take it that every table that has its own auto-incrementing primary key (rather than using a field from another table) should have that? What is the point of those drops? Also, do I need to create a new sequence if I'm adding a new auto-incrementing primary key (e.g. archive.ar_id or externallinks.el_id)? If so, do I need to add the new sequence not only to the maintenance/postgres/tables.sql file but also to the # new sequences if not renamed above section in PostgresUpdate.php? And if so, how do I know whether to put false or the primary key as the third parameter here:

array( 'addSequence', 'page_restrictions', false, 'page_restrictions_pr_id_seq' ),
array( 'addSequence', 'filearchive', 'fa_id', 'filearchive_fa_id_seq' ),
...

Thanks, Leucosticte (talk) 02:36, 24 October 2012 (UTC)

More clues when installing mediawiki 19.2 with pg

If there are no SQL drivers, an error message occurs:

Could not find a suitable database driver! You need to install a database driver for PHP. 
...

The message suggestion is to install mysql, BUT (!), it is wrong, you can install only pgsql,

sudo apt-get install php5-pgsql
sudo /etc/init.d/apache2 start

... Them, OK, restart the mediawiki installation process (link to restart install).