Hi
Since PostgreSQL is less and less supported as a Backend (which is in the end more then understandable), and the database upgrade script finally does not work at all with PostgreSQL anymore (compare: [OJS3.0.1] 'Upgrade' script fails constantly) I would like to migrate my database from PostgreSQL to MySQL.
Are there any Experiences or Tips with that out there?
Best,
Paf
Hi @paf,
There’s a work-around for that issue, which is an ADODB bug. We don’t plan to drop PostgreSQL support, but as you’ll note most of our community uses MySQL, so it’ll receive more testing attention.
Converting from PostgreSQL to MySQL is probably a better topic for a resource like StackOverflow – I expect there are already many threads there. OJS doesn’t require any special considerations in this regard.
Regards,
Alec Smecher
Public Knowledge Project Team
I’ve had some success with migrating our PostgreSQL to MySQL. We plan to do this as part of our v2 → v3 migration. Starting with the current v2 PostgreSQL, we use a free tool called pg2mysql (Lightbox Technologies Inc - pg2mysql) in command line mode. Instructions include the syntax for a required postgres SQL dump. After running the tool, I had to run some string find & replaces using VIM to take care of a couple of issues.
Replace some bad escaping of ‘’ with ’
Add some character limits for indexes using TEXT fields (ie setting_name, setting_value)
The resulting SQL file was then used to build a MySQL database with CHARACTER SET utf8 COLLATE utf8_general_ci. After importing into MySQL, OJS v2 could be pointed to it.
I ran into a couple of more issues when updating to v3. Mainly it was do to having multiple email entries. MySQL did not like entries that differed only by case. Also ran into a similar issue with keywords. I wrote a script to “fix” these in the database. With those changes, the upgrade to v3 worked. We are still in testing mode right now, but hope to migrate/upgrade in the next month or two.
Hi @andjsmit thank you very much for sharing your experiences! I considered to use this script as well, but since it is from 2011 there had to be a lot of corrections. There was one issue, which was related to this building indexes over text fields - I guess the reason why you added character limits. I’d like to know how you could be sure, that none of the contents is actually longer than the newly set limit?
Anyway would you share your script? Not only for my own migration, but I also started to make an updated version of pg2mysql on GitHub since it’s so outdated and could use your correction script as an inspiration for that!
@asmecher in my opinion it would be better to drop the PostgreSQL support entirely instead of maintaining it in a state which is in fact not usable without some obnoxious patches of which you said you won’t include it in the code. A not to be named software giant shows us constantly, that its better to drop badly supported features than to keep them in bad shape. No normal person would use postgres anyway for a thing like OJS I guess (I think the installer of my instance did just to make himself more essential probably). But that is just my opinion, for sure although being quite experienced now with the ojs/pkp code and project I am not in the position to completely oversee this.
A good migration path would be needed anyway, but that I will hopefully produce now with the help of @andjsmit and document here or the like.
Best,
Paf
Hi @paf,
Thanks for the feedback – overall I agree, however we do have users working with PostgreSQL that I don’t want to orphan, and our long-term plans may include dropping/replacing ADODB, which would mean doing away with the schema management tools that necessitate that SERIAL patch.
Regards,
Alec Smecher
Public Knowledge Project Team
What is the output if you run:
echo “select * from xi_sessions” | mysql -uroot -pnagiosxi -D nagiosxi
?
Attached is a sql script to create any tables that may have been missed. Run it with:
mysql -uroot -pnagiosxi < agiosxi_db.sql
Try running this and then run the import command again or try to read common instruction Error trying to migrate bd from Laravel to PostgreSQL - It_qna