Migrating PunBB 1.3 from MySQL to PostgreSQL on CentOS 5

I successfully migrated the PunBB 1.3.4 database from MySQL to PostgreSQL on CentOS 5.

Software versions:

• PunBB: 1.3.4
• MySQL: 5.0.77
• PostgreSQL: 8.1.18
• PHP: 5.1.6

To perform migration, follow these steps:

1. Uninstall all PunBB extensions.
2. Create a dump of the MySQL database:
   

   mysqldump forum --compatible=postgresql --default-character-set=utf8 --skip-extended-insert -u root -p > forum_old.sql
   

3. Exctract only the tables data from the dump, not the schema:
   

   grep "INSERT INTO" forum_old.sql > forum_old_converted.sql
   

4. Create a PostgreSQL user and the new database:
   

   postgres=# CREATE USER "forum" with password 'super_password';
   postgres=# CREATE DATABASE "forum" ENCODING 'UTF-8' OWNER "forum";
   

5. Remove config.php from the site DocumentRoot.
6. Create PostgreSQL database tables: run install.php from the forum admin directory and follow the instructions.
7. Upload config.php to the site DocumentRoot.
8. Create the dump of the PostgreSQL database schema without the data:
   

   pg_dump forum --create --schema-only -U user_name -h 127.0.0.1 > forum_new_schema.sql;
   

9. Recreate the PostgreSQL database without the tables data:
   

   postgres=# DROP DATABASE "forum";
   psql forum -U user_name -h 127.0.0.1 < forum_new_schema.sql;

10. Import the old MySQL database into the new PostgreSQL database:
    

    psql forum -U user_name -h 127.0.0.1 < forum_old_converted.sql;

11. Update PostgreSQL sequence objects:
    

    SELECT pg_catalog.setval('bans_id_seq', (SELECT MAX(id) FROM bans), true);
    SELECT pg_catalog.setval('categories_id_seq', (SELECT MAX(id) FROM categories), true);
    SELECT pg_catalog.setval('censoring_id_seq', (SELECT MAX(id) FROM censoring), true);
    SELECT pg_catalog.setval('forums_id_seq', (SELECT MAX(id) FROM forums), true);
    SELECT pg_catalog.setval('groups_g_id_seq', (SELECT MAX(g_id) FROM groups), true);
    SELECT pg_catalog.setval('posts_id_seq', (SELECT MAX(id) FROM posts), true);
    SELECT pg_catalog.setval('ranks_id_seq', (SELECT MAX(id) FROM ranks), true);
    SELECT pg_catalog.setval('reports_id_seq', (SELECT MAX(id) FROM reports), true);
    SELECT pg_catalog.setval('search_words_id_seq', (SELECT MAX(id) FROM search_words), true);
    SELECT pg_catalog.setval('topics_id_seq', (SELECT MAX(id) FROM topics), true);
    SELECT pg_catalog.setval('users_id_seq', (SELECT MAX(id) FROM users), true);
    

12. Install necessary PunBB extensions.