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.
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.
how is the ip converting handle by this process?
Mysqldump is told to do it as hex. The utility parses the hex & translates it.
Only tested with ipv4 thus far.
would be nice if some 'convert.php' would be available
/me hides
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!
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.
Quote from: shawnb61 on August 28, 2023, 07:03:06 AMSince 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!