• lystor
  • Junior Member
  • Offline
  • Registered: 2009-04-28
  • Posts: 4

Topic: HOWTO: 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. Extract 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.

Original HOWTO

http://www.lystor.org.ua

lystor's Website