Migrating from MySQL to PostgreSQL (or back)

I had to migrate from MySQL to PostgreSQL yesterday. At first I tried to use use some convertion script but they didn't work. Possibly because I use MySQL 4.0. In case anyone else needs to do this I thought I could document the procedure here. Most of this will probably apply if you need to go the other way too, with only minor modifications.

1. Dump your MySQL database with

mysqldump punbb > mysql-dump.sql

2. Edit mysql-dump.sql and delete anything but the "INSERT INTO ..." entries for categories, forums, groups, posts, rank, topics, users. Replace all back quotes `around identifiers with double quotes ", a global search and replace worked for me.

3. Create a new postgres database with createdb, and setup a version of PunBB to use this new postgres database by running install.php.

4. Dump the new postgres database with the command

pg_dump punbb-pg > postgres-dump.sql

5. Edit postgres-dump.sql and delete all the "COPY ..." statements for the corresponding tables that you DID NOT delete in mysql-dump.sql. You will also need to update some of the counters at the end of postgres-dump.sql, these read "SELECT pg_catalog.setval(...)", you need to use a higher number than you insert in mysql-dump.sql.

6. Create a second postgres database.

7. Import the data into the new postgres database with

psql punbb-pg2 < mysql-dump.sql
psql punbb-pg2 < postgres-dump.sql

If this fails, figure out what went wrong, edit the dumps, recreate punbb-pg2 and try again.

8. Check if your new database works!