Tavi on PostgreSQL 7.4
Converting a Tavi 0.26 Wiki from MySQL 4.1 to PostgreSQL 7.4
This page documents the process of comverting a Tavi 0.26 Wiki running on MySQL 4.1, to run on PostgreSQL 7.4. It provides references to the previous works this process is based on, and to sources for the software required.
Abstract
Tavi is a PHP Wiki implementation running on a webserver. VEMS has been using Tavi for several years, however there have been ongoing problems with the MySQL database backend that Tavi requires. The problems appear to be index related, and have not been repeatable (therefore fixable) but have been addressed by regular dump and reloads. This should not be necessary, so an alternative was sought.
Tavi does not support databases other than MySQL by default so conversion to another Wiki rather than another database was initially considered. It is important not to lose data in the process, so either conversion may have been difficult. Fortunately, there is a patch to provide PostgreSQL support for Tavi 0.25, written by Juha-Mikko Ahonen and available from his website. Less fortunately, the performance when searching the database is not adequate (PostgreSQL does not support text indexes by default) and it has not been updated to the current version of Tavi.
The work by Ahonen was however a good place to start, so full credit must be given to him for the inspiration and initial patch.
The work described below extends Ahonens patch, updating it for 0.26 compatibility, refining the queries, and increasing search speed by an order of magnitude by using the TSearch2 functionality by Oleg Bartunov and Teodor Sigaev. The stable release of TSearch2 is provided with the PostgreSQL distribution.
Links to the software mentioned above are at the end of this document.
Aims
- Provide a high performance alternative to the MySQL 4.1 database.
- Document the process of converting version 0.26 of Tavi to PostgreSQL 7.4.
- Document the process of converting the default Tavi installation to the customised VEMS Tavi installation.
Prerequisites
- Apache webserver (any version).
- PHP 4 or 5 with MySQL and PostgreSQL support.
- Tavi 0.26 sources.
- MySQL 4.1 database with Tavi data (if conversion of existing data is required).
- PostgreSQL 7.4 installation with TSearch2 available.
- Shell access to run the conversion scripts.
- Perl support may be required if no existing Tavi config.php is available.
The Conversion Process
As VEMS is using Tavi 0.26 with Captcha, the first step is to update Juha-Mikko Ahonens PostgreSQL port of Tavi from 0.25 to 0.26. This is simple enough, merely requiring line numbers in the diff to be edited.
Link to Tavi PostgreSQL 0.25 -> Tavi PostgreSQL 0.26 diff
Link to Tavi MySQL 0.26 -> Tavi PostgreSQL 0.26 diff
The next logical step is optimisation of the PostgreSQL database queries, however to do this we need some test data loaded. So we'll change course and figure out how script the data transger from MySQL Tavi to PostgreSQL Tavi.
Downloads go here
Software:
Tavi Wiki:
Apache HTTP server:
PHP engine:
MySQL database:
PostgreSQL database:
Initial PostgreSQL patch for Tavi:
- http://tavi.sourceforge.net/TaviPatches/PostgreSQLSupport
- http://jmz.iki.fi/download.php/en/Tavi+Patches
TSearch2 full-text-index search engine for PostgreSQL: