phpBB MySQL -> PostgreSQL

Conversion of phpBB 2.0.17 forum database from MySQL to PostgreSQL (phpBB 2.0.22) is not straightforward and needs some tweaking. I'll describe how I did the conversion and what modifications were needed. I also converted DB from win1250 to UTF-8 and added czech language.

Howto switch phpBB database from MySQL to PostgreSQL

As on primary site with MySQL there was not available phpMyAdmin nor the export to PostgreSQL compatible I go just ordinary mysqldump.

First step was to convert database:

perl mysql2pgsql.perl --enc_in cp1250 dump.txt pg.sql

This I did with CVS version of mysql2pgsql perl script - this is the version I used. Otherwise you can get something in python e.g. on mysql2pgsql...

Then the most important part comes, because the conversion was not 100% OK.

• Edit pg.sql>
  1. find phpbb_config and edit it for your needs (If you do not have admin account on imported forum this is the only way how to change the baseurl, mail etc.)
  2. convert sequence names - e.g. phpbb_posts_post_id_seq -> phpbb_posts_id_seq and similar - the seq structure is replacement for mysql autoincrement
  3. add missing phpbb_users_id_seq - definition in mysql dump is without sequence (auto_increment) - you can also change mysql dump before conversion to save this step
  4. if you experience any serious errors during following import of DB then check the lines with those errors and fix them
  5. for security reasons replace phpbb prefix to something else
  6. phpbb_search_wordlist table converted to bytea instead varchar for "word_text", change it to varchar(50)
• install phpBB2 (unpack tar.gz somewhere)
• unpack czech translation and convert it from cp1250 to UTF-8 (iconv -f cp1250 to UTF-8), copy lang_czech.utf8 to languages under forum, copy images, copy subsilver_czech
• Import database:

  psql -f  pg.sql   > pgsqlimport.log
  

  (NOTICE is fine during import, no error should appear)
• Change sequence numbers to start above highest existing index number (e.g. when you have 2345 posts then you have to set phpbb_posts_id_seq to at least 2346) <- it may be that only pruning DB from admin menu will do that (I did not tested).

  psql
  select setval('phpbb_search_smilies_id_seq', 247);
  

• Navigate to install/update_to_latest.php

You are done.

---

Email comment