Is there a way to change database engine from mysql to postgresql?

[mrPsycho]

After using this guide, on jira with postgresql i can't see RapidBoards and several other things.

it says - it didn't created or i don't have permissions... 

and... i can say, that migration fails, cause i can't say that everything works. 

upd. i found problems with: 

boards
structures
tests
Repositories
Email

this is differs from original DB.

[Earl McCutcheon]

Hello Yaroslav,

First just to cet some additional context of the migration, what Jria version did you start on, and Did you migrate and upgrade at the same time, if so what version did you upgrade too or did you migrate to a matching version of Jira?, also what are the database versions both MySQL and PostgreSQL?

Next There are a few things to check right off the bat, first would be to go to the Settings >> Applications menu and make sure that jira software is installed, and no version conflicts are listed.  Then go to the Manage add-ons section and verify that everything is listed as enabled and no errors are listed.

Check the database to verify the board data was imported, this can be don with:

SELECT * FROM "AO_60DB71_RAPIDVIEW";

If the result set is empty then no boards exist and we know that the import failed, if the boards are listed then something else is missing.

Extract the backup.zip file you used for the migration and check the activeobjects.xml file for incorrect primary key increment values, this can occur if there was a collation conflicts on the source MySQL DB and will cause the data import to fail because of attempting to duplicate primary key values, the following grep will locate these items:

grep '<column name="ID" primaryKey="false"' activeobjects.xml 

If that returns results run the following to fix the file and you can rerun the import:

sed -i -e 's/<column name="ID" primaryKey="false" autoIncrement="false"/<column name="ID" primaryKey="true" autoIncrement="true"/g' activeobjects.xml 

Next start looking at the startup logging check for a section called "failed plugin report" in the atlassian-jira.log, if there are any failing plugins or missing dependencies they will show up there, and could be causing Software to only partially start up and we would need to see the list of failed plugins to get an idea of how to move forward.

While in the logs check for any other explicit stack traces during the migration / startup period and post the error output so we can take a closer look.

Regards,
Earl

[mrPsycho]

hello! Thanks for fast responce!

Q: Wwhat Jria version did you start on?
A: Both versions 7.12.1

Q: Did you migrate and upgrade at the same time?
A: no. just migrating db engine. 

Q: what are the database versions both MySQL and PostgreSQL?
A: MySQL 5.6, Postgres 9.6

now interesting.
we found that importing backup.xml doesn't create 

AO_60DB71_RAPIDVIEW

pgloader creates, but:
"ao_60db71_rapidview"

and this is very strange and looks like a bug.

also, pgloader says about 74 million records, and backup.xml - only 63 million. 

that's strange too. 

[mrPsycho]

i guess by hands we will fix this issue... but this is sucks, that such great company doens't have working solution to migrate from engine to engine. 
especially, when company start to work with mysql jira, than it grows and ready to move to jira DC, which doesn't support mysql... and that's it. 

one big fail (

[Earl McCutcheon]

Hi Yaroslav,

Since its the "AO_60DB71_RAPIDVIEW" table specifically thats failing to create on import, It looks like you might be running into the same scenario covered in this previous post on a similar MySQL to PostgreSQl migration:

• https://community.atlassian.com/t5/Jira-Core-questions/JIRA-migration-from-mysql-to-postgres-failed/qaq-p/211154

Check out the Logging for the error noted in the attached KB that was listed as the fix on that post "JIRA Import Fails Due to Character Encoding"

Regards,
Earl 

[mrPsycho]

i'm using xml cleaner, cause without it restore fails at start. 

and it doesn't help

[Earl McCutcheon]

Hi Yaroslav,

After using the xml cleaner, you noted "without it restore fails at start" are you saying that when the XML cleaner is not used you immediately get an error but after using it the import is completing, and only the agile data is failing to complete?

Also what are the errors you are seeing if any?

And do you see any errors in the logging during the import?

Regards,
Earl

[mrPsycho]

if i could, i'd just gave you this dump file and you'd find the problem.

but, due to our restriction - we cannot do it. 

Anyway, i did my job with pgloader.