TikiPostgres

Install Tikiwiki with a PostgreSQL database (aka postgres or pgsql).
This small HowTo? is the start of my watch work on postgresql tikiwiki compliance. Environment is a GNU/Linux debian with 1.8.1 and with 1.9 versions.-- mose

Table of contents

• Install & configure postgres
• Install Tikiwiki
  • Version 1.8.1 tarball
  • Version 1.9 cvs HEAD
  • Tikiwiki setup
• Regenerating the SQL file for latest features/bug fixes
• Known Issues
  • Store on disk not on db
  • No possibility to update DB without loose of data (?)
  • Tiki User Tasks
  • Saving large amounts of text.

Install & configure postgres

Make sure that the postgres module for php is installed, or things will silently fail later on.

Be sure that your postgres server is configured to accept password accounts :

root# vi /etc/postgresql/pg_hba.conf
...
local   all         all                                             password
host    all         all         127.0.0.1         255.255.255.255   password

Create a db user You may use any other auth scheme, that one is just easy and secure.

root# su - postgres
postgres$ createuser -P tikiwiki
Enter password for new user:
Enter it again:
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
postgres$ createdb -O tikiwiki tikiwiki
CREATE DATABASE
postgres$ exit
root# psql -U tikiwiki tikiwiki
Password:
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.

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

tikiwiki=> \q
root#

Install Tikiwiki

Version 1.8.1 tarball

Get the tarball from http://sourceforge.net/project/showfiles.php?group_id=64258&release_id=193747

mose$ tar xjf tikiwiki-1.8.1.tar.bz2
mose$ cd tikiwiki-1.8.1
mose$ psql -U tikiwiki tikiwiki < db/tiki-1.8-pgsql.sql
Password:

Version 1.9 cvs HEAD

mose$ cvs -z3 -d:ext:mose@cvs.sf.net:/cvsroot/tikiwiki co -d tiki19_psql tikiwiki
mose$ cd tiki19_psql
mose$ psql -U tikiwiki tikiwiki < db/tiki-1.9-pgsql.sql
Password:

Note: the pgsql output can generate some messages and say there were errors. You can ignore the errors saying ERROR: table "bla_bla" does not exist but if there are parsing errors it usually causes that some database tables are not to be created. It means the convertscripts/*.php file didn't worked properly and needs to be fixed to produce right syntax for PostgreSQL and then it must be run again (see below how to do it) to fix the tiki-1.9-pgsql.sql file.

Tikiwiki setup

You don't need to use tiki-install.php web install, the following manual install is enough

mose$ sudo ./setup.sh mose www-data 02777
mose$ vi db/local.php
<?php
$db_tiki     = 'pgsql';
$host_tiki   = 'localhost';
$user_tiki   = 'tikiwiki';
$pass_tiki   = '***';
$dbs_tiki    = 'tikiwiki';
?>

Then go to http://vhost/ or any url you configured in apache for going to tiki, connect as admin/admin, change admin password, and you can now configure tiki as any other tiki.

Regenerating the SQL file for latest features/bug fixes

When there's a change in db/tiki.sql file you can regenerate the tiki-<version>-pgsql.sql file yourself (if it wasn't done by someone else already) using the following commands (example, versions can differ):

cd db/convertscripts/
php mysql3topgsql72.php
mv 1.9.to_pgsql72.sql ../tiki-1.9-pgsql.sql

Do not edit the tiki-1.9-pgsql.sql file manually when it's not really needed ! If you don't backup the file it can be overwritten next time you update your Tiki

Known Issues

Store on disk not on db

As there are problems for handling binaries in tiki with postgres it's better to always choose storage on filesystem for file galleries, image galleries, forum attachments as well as for trackers and wiki.

No possibility to update DB without loose of data (?)

There's still no DB upgrade script written like for MySQL (tiki-1.8to1.9.sql)

Tiki User Tasks

They don't save. Only the following message appears:

Saving large amounts of text.

This will generate a postgres error because the size of the text generates a btree index on the row which is too large for postgres. The error says something to the extent:

There is a sort of backdoor workaround and that is to modify two indices manually in postgres on the data column and put a where clause to limit the indexing size. Eg. where char_length("data") < 1000.

... gather here more information about specific needs with pgsql use ...

---