Providing practical examples since 1998

Things look quiet here. But I've been doing a lot of blogging at
dan.langille.org because I prefer WordPress now.
Not all my posts there are FreeBSD related.
I am in the midst of migrating The FreeBSD Diary over to WordPress
(and you can read about that here).
Once the migration is completed, I'll move the FreeBSD posts into the
new FreeBSD Diary website.

Phorum - installation, upgrade, and conversion from MySQL to PostgreSQL13 July 2003

Phorum and PostgreSQL
represent the most stable and robust tools in their respective classes. Phorum is a
"web based message board written in PHP". PostgreSQL is the most advanced open source database
available. Both are tools which I have used for several years and which I recommend to others.
If you're looking for a message board or a database, these are what I think you should get.

The objective of this process is to convert an existing MySQL Phorum to use PostgreSQL instead.
Why? Because I can. And because, in my opinion, PostgreSQL is a better database. I continue to use
MySQL for some existing tasks, but for all new tasks, I use PostgreSQL. In this regard, the FreeBSD
Diary is the last holdout; it is the only place that I use MySQL. That will change soon.
In some ways, this article advocates PostgreSQL, but for the most part, it is a Phorum how-to.

This article will not concentrate on the PostgreSQL
details as I'm going to assume you have previous experience. If you need some PostgreSQL help, this
previous article will help.

The agenda for today....

This is not a simple one-step procedure. We will be doing several tasks and using tools you may
not be familiar with. I had to start over again more than once.

Today we will do the following:

Download and install the latest Phorum

Upgrade our existing MySQL Phorum (to a new location)

Create a PostgreSQL database

Install Phorum for the PostgreSQL installation

Configure the PostgreSQL Phorum

Create our PostgreSQL forums

Dump the MySQL data

Import the MySQL Phorum data into the PostgreSQL Phorum

You will find all of the above steps detailed in this article.

Upgrade first, then migrate

It is important to note that we will be first upgrading our existing MySQL Phorum and then
we will migrate that data to PostgreSQL. This means we will set up Phorum twice. The first
time it will be for MySQL. The second time it will be for PostgreSQL. The steps are essentially
the same, expect for the selection of the appropriate database engine.

I am also assuming that you are using PostgreSQL 6.5 or newer. If not, you will have to adjust
your database selection accordingly during the configuration stage.

For the MySQL upgrade, I used the Phorum administration interface to taken down Phorum until I had completed
the upgrade. I then took the production MySQL database and loaded it into another database and used
that for the Phorum upgrade.

But first, a little strategy

Recall that the major objective is the conversion of a existing MySQL Phorum to use PostgreSQL.
I also want to retain all the existing threads, user logins, URLs, etc. Central to this conversion is
the necessity that both Phorums be running the same version of Phorum. This is why the MySQL installation
is upgrade first.

Essential to retaining the same URLs is the use of the same forum numbers
in the new Phorum as were used in the old Phorum. People will have book marks.
Search engines will have results stored away. I want all of these to continue to be valid.

The conversion process itself is simple. I'm just copying the table data, nothing else. The biggest
problem
associated with this conversion is the table creation. Instead of trying to get the MySQL => PostgreSQL
translation correct, I decided to let Phorum create the tables and I'd just transfer over the bare data.
There are other options. You might want to want to comment upon this approach and suggest alternatives.

During this conversion process, I will be making frequent backups of the database. That will save me
time should I make a mistake during my creation of new forums.

Take it offline

I suggest taking your Phorum offline via its administration interface. This will prevent
any updates by your Phorum users and ensure that the new database contains the same
data as the old database.

The next step should be a backup of your MySQL data. My original MySQL article
has some rudimentary backup steps.

Download and install the latest Phorum

To Download the latest Phorum, following the instructions found in the
Phorum Installation article.
I usually install phorum in the phorum
of my main website. But for this upgrade, I will install it at
phorum-3.4.3a.
I will move it to phorum later. I will
also create a temporary MySQL database, copying in the original data.
This approach ensure that I leave my original data untouched. Should
anything go wrong, no harm is done.

Upgrade our existing MySQL Phorum (to a new location)

I created a new MySQL database (phorum_upgrade),
dumped the original database, and loaded it into the new database. All Phorum
upgrades acted on this new database.

For upgrading your MySQL Phorum, you can follow the instructions in the
Phorum Installation article.
Keep
in mind that you should choose MySQL, not PostgreSQL, as the database.
Also remember to click on "Check here is this is an upgrade".

Create a PostgreSQL database

You now just upgraded your MySQL installation. Now it is time to create the
database for the PostgreSQL installation of Phorum.

$ createdb phorum
CREATE DATABASE

Install Phorum for the PostgreSQL installation

The safe procedure is to install Phorum to new location
(say phorum-postgresql),
get it working, then rename the original Phorum, and move the new Phorum installation
into place.

Configuring the PostgreSQL Phorum is very similar to the MySQL upgrade process we did in the
previous example.
You will be going through the entire setup process again, but this time
specifying PostgreSQL instead of MySQL.

After you have configured the PostgreSQL Phorum installation, you need to create the Phorum tables
which will receive the data from the MySQL installation.

In this section I will create the new forums under PostgreSQL using the information from
the existing
MySQL installation. In my case, I will be duplicating the forums I already have and using the
same table names and forum numbers. I obtained this information from MySQL and from the Phorum
administration web pages.

It is important that I use the same table names or the data import will not work without manual manipulation
of the data. If you are using
different table names, you will have to amend the exported data accordingly.

New forums can be created using the Phorum Admin pages (at /phorum/admin/index.php
of your installation).
Select New Forum from the Forum Maintenance. In my
case, I wanted to create the FreeBSD Support forum first, because that is forum #1 in my Phorum
installation. So I filled in the following fields with these values:

Name: FreeBSD Support

Description: Ask for help here

Table Name: support

Then I clicked on Add.

There is my first forum. But I have more forums to create. I might make an error and have to start over. Now is the time to take a backup:

pg_dump phorum > ~/phorum.sql

I clicked on Main Menu and repeated the process for the other forums, in forum number order, and
each time checking that I had the correct forum number. And each time I did a new backup to a new
file. Just in case.

At the end of it all, I did another backup. This backup will be used to restore should I encounter
any problems while I'm loading data.

Dump the MySQL data

It's easy to dump the data.
This produced output which I could import directly into PostgreSQL. I had to do some manual changes to
amend some table names, but that was it.

Yes, your PostgreSQL-based Phorum is up and running. However, if anyone tries to post a new message,
it will fail. The sequences used to create an id for a new message needs to be reset to take
into consideration the messages which you imported. This can be done using the Phorum Administration
interface.

When viewing the thread messages (i.e. /list.php?f=2&collapse=0), the date fields are correct, but I'm seeing the oldest messages listed first. Clicking on Older Messages or Newer Messages gives me the same result set again (i.e starting at the oldest messages).

I confirmed this problem with this query:

select id, datestamp, modifystamp from support order by id desc;

If modifystamp is zero, then you have imported the old data, not the new data.

The first step in a plan...

This was the first step in a very cunning plan. The next step was the creation of
a new polling script which allows website users to submit polls for the consideration
of the webmaster. This was an idea which had been sitting around for quite some time.
Witness the existing poll which has been sitting around for nearly a year!

That polling script is ready for your use. PGVote
has been around for a week or so. It's based upon an existing polling script but
has a heavy concentration on PostgreSQL.

Go PostgreSQL!

Yes. I'm on the move to using PostgreSQL and only PostgreSQL on my websites. If you've not
used it yet, I suggest you try it. Consider that PostgreSQL is to MySQL as FreeBSD is to Linux.
You already know how good FreeBSD is. Now go try PostgreSQL.