The FreeBSD Diary

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.

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

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.

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.

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.

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".

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

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.

Follow the installation instructions at Phorum Installation.

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.

Again, follow the configuration instructions at Phorum Installation.

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

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.

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.

$ mysqldump -u root -p --complete-insert --no-create-info phorum article_feedback article_feedback_bodies article_feedback_xref newforum1 newforum1_bodies newpets newpets_bodies success success_bodies tips tips_bodies > data.sql

To import the data, I did this:

$ psql phorum < data.sql

That was it. Phorum was up and running under PostgreSQL!

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.

If you see a message similar to this:

ERROR: Cannot insert a duplicate key into unique index pets_bodiespri_key
Insert Into pets_bodies (id, body, thread) values (1, 'what pets?', 1)
ERROR: Cannot insert a duplicate key into unique index petspri_key
Insert Into pets (id, author, userid, email, datestamp, subject, host, thread, parent, email_reply, approved, msgid) values ('1', 'Dan', '1', 'dan@example.org', '2003-08-23 12:46:13', 'Pets?', 'bast.example.org', '1', '0', '', 'Y', '<1d887548dfe7596f550ccc7e765203c1.FreeBSDPets>')

...then you need to reset your sequences. To reset the sequence, do this:

1. From the Main Menu of Phorum Admin, click on "Manage Forums/Folders".
2. Click on one of your forums
3. Click on "Reset Sequence"
4. Repeat the above steps 2-3 for all forums

• When viewing collapsed threads (i.e. /list.php?f=2&collapse=1), all date fields are "31-12-69 16:00".
• 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).

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

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.

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.

Oh, and yes, I think Phorum is pretty damn good too.