1 (edited by Spinner 2008-06-30 07:34)

Topic: Moving databases from postgres to mysql - Howto?

Hi,
We're moving an entire punBB forum to a new server and will have to migrate
from postgres to mysql.
I did a pg_dump on the existing punBB-tables using:
pg_dump --host=oldhost--username=uzer--file pg_punbb.sql --password -t 'punbb_*' dbname

The file doesn't exactly look like the kind of file I would try to pipe into mysql so I was wondering how one should do this. Both sites are running 1.2.17.

This probably qualifies as a FAQ, but I didn't find it there.
/Ken


//Comment 3 days later...
//Hmmm, am I really left in the cold with this?

Re: Moving databases from postgres to mysql - Howto?

This is how I solved it at the end.

1. Dump from postgres with the -D option and remove everything from the file
except all the INSERT's. It is also easiest to edit all the addresses, and things
that should be changed for the new server, by running scripts directly on the
sql dumpfile. Transfer the file to the new server.

2. Copy the entire punbb directory to the new server. Remove the config.php or make an identical installation with the same mods etc. Run the install script so that it creates all the necessary tables in mysql. Run install scripts for any mods too.

3. Drop all data from all mysql tables on the new server. (delete from table etc)

4. Pipe the entire postgres dump into mysql.

5. In case you copied the entire directory structure of punbb from the old server, clean the cache directory, otherwise it will contain some erroneous addresses.

6. Make sure you also copy the avatar image folder to the new server.

7. Voila!

Re: Moving databases from postgres to mysql - Howto?

Spinner wrote:

7. Voila!

Cool.
Though this is not the work for human and must be automated/simplified.
There must be a MySQL-compatible export from PostgreSQL (and vice versa)...

Carpe diem

Re: Moving databases from postgres to mysql - Howto?

Yes, it should be automated, and preferably so that it is totally generic for postgres and mysql, and independent of the application using the database. This is the very point of using sql, and I am surprised that there is so little done in this area. mysql and postgres totally dominate the web/open source communities, but I suppose there is too much religion about them for people to migrate back and forth very often...

Actually, I had another problem along the way. I was dumping from Postgres using:

pg_dump -D --host=localhost --username=pun --file pg_punbb.sql --password -t 'punbb_*' pundb

Postgres wants to place keywords in double quotes, so some of this output can't be piped into mysql without first cleaning these double quotes. I did so using a sed script.
Save this into a file called e.g. pgfix and make it executable:

#!/bin/sed -f
s/"language"/language/g
s/"password"/password/g
s/"location"/location/g
s/"owner"/owner/g

While at it, one can also place a few more lines into this script to replace all references
to the old address of the site, for example:

s/www.oldpunsite.com/www.newpunsite.com/g

Then just execute:

./pgfix < pg_punbb.sql > fixedpg_punbb.sql

In this way one can change not only the settings for the new punbb site, but also
all references used by users in their posts.
In a previous version of pg_dump there was a flag that forced pg_dump not to place
double quotes in the output, but this has been depracted and the flag means something
else now (won't say what I think about such ill-documented changes...).

This was a pretty clumsy writedown on how to do the migration, and it could be made better, but I'm on vacation and just had to get this migration done very quickly. Hopefully it helps someone. It would be good if someone polished up the information and placed it into the FAQ.