Converting from mysql to postgresql

[shawnb61]

I have successfully created a copy of a forum from mysql into pg.  This required a couple of utilities, linked below.

These utilities work for SMF2.1 only.

Prepwork:
  - Install a vanilla version of PG & get it fully working.  Truncate all tables.
  - Your source mysql SMF DB must be UTF8.
  - Your source mysql SMF DB must include ONLY vanilla tables & columns. Source & target tables & columns must match perfectly.
  - If needed, create a copy & delete all extraneous rows & columns and do the mysqldump from there.

Overall Process - for use on Windows:
 (1) Use the following command to do the export:
    mysqldump -u{user} -p{pass} --no-create-db --no-create-info --hex-blob --skip-add-locks --skip-comments --skip-set-charset --compact --skip-quote-names --complete-insert {dbname} > {myfile.sql}
 (2) Run the pg_converter.php utility, reading the mysqldump just created & creating a new .sql file
 (3) Specify the $infile, $outfile & $path when prompted
 (4) Open a command window
 (5) Issue the following in the Windows command window (otherwise it assumes import is in Windows 1252, not utf8...):
    SET PGCLIENTENCODING=utf-8
 (6) Log on to psql, connect to your new empty vanilla pg DB
 (7) Load file with: \i mynewfile.sql
 (8 ) After load, you must fix all the SEQUENCE #s...
 (9) Run repair_settings.php to correct paths
 (10) Copy over your attachments, avatars, custom avatars & smileys
 (11) Clear your cache

Utilities used:
pg_converter.php: https://github.com/sbulen/sjrbTools/blob/master/pg_converter.php
pg_convert_seqs.php - fixes all the sequence #s: https://github.com/sbulen/sjrbTools/blob/master/pg_convert_seqs.php

Limitations:
Some JSON fields will get busted.  You will need to fix these by hand.  This utility does some simple translations of quotes to the pg format & since it is operating on the whole mysql export file, it treats text & code the same.  This does break some JSON fields. 

I hope this helps,

NOTE:  This has only been tested with ipv4 data.  It has also only been tested with mysql v5.7 & pg v10.  Also, only 2.1 RC3. 

[shawnb61]

I recently used these utilities to convert a mid-sized MySQL 8.0 DB (200K posts) to pg v14.1.  SMF 2.1 RC4.

It did break some JSON, which I repaired by hand.  A note about the JSON limitation was added above.

Note that due to the different quote requirements between SMF2.0 & SMF2.1, this approach only works for SMF2.1.

[albertlast]

how is the ip converting handle by this process?

[shawnb61]

Mysqldump is told to do it as hex. The utility parses the hex & translates it.

Only tested with ipv4 thus far.

[AllMassive]

would be nice if some 'convert.php' would be available

* AllMassive hides

[Gryzor]

Thanks for this tutorial. Found this thread because we're considering upgrading to a new server with postgresql. 

However, what is meant by "ONLY vanilla tables & columns"?

Thanks!

[shawnb61]

Since you're inserting rows into existing tables, the contents must match your newly installed blank db exactly.  So, all "vanilla" - no custom columns or tables, e.g., from mods.

[Gryzor]

Since you're inserting rows into existing tables, the contents must match your newly installed blank db exactly.  So, all "vanilla" - no custom columns or tables, e.g., from mods.

Ah damn, I was afraid so... this sucks, what a pity! But thanks nonetheless!