Phorum – installation, upgrade, and conversion from MySQL to PostgreSQL
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.
Follow the installation instructions at Phorum Installation.
Configure the PostgreSQL Phorum
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.
Create our PostgreSQL forums
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.
$ 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!
A few housekeeping items
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:
- From the Main Menu of Phorum Admin, click on “Manage Forums/Folders”.
- Click on one of your forums
- Click on “Reset Sequence”
- Repeat the above steps 2-3 for all forums
If your dates aren’t right!
I did encounter one problem which confused me for a while. When I first did the mysqldump,
I dumped the *old* database instead of the *new* database. As a result I saw these symptoms:
- 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).
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.
Oh, and yes, I think Phorum is pretty damn good too.
http://www.phpbb.com/
Better or worse than phorum.
No, I’m not trolling.
I’ve never used a BB but am interested.
Personally, I find phpbb slower and too much fluff.
???
in detail???
what are the major improvements in contrast to mysql??? i’m just using mysql as a sysadmin +some webdeveloper
skills. mainly its for some sysmgmt tools … is their a real need for switching ??? or just some nice special features (transaction …) ???
Your questions belong in the Support forum, not this article comments section.
There are many websites talking about PostgreSQL/MySQL. Try them.
The RSS link to this story seems to be invalid due to spaces in the URL.
I’m not seeing any spaces in the URL at <http://www.freebsddiary.org/news.php>. What RSS link are you seeing and where are you getting it from?