Can I migrate Jira from Postgres to Oracle using the XML backup?

[Sean Johnson]

I have been tasked with upgrading Jira 5.0 to current AND because we are an Oracle shop they would like me to move our jira db off of Postgres to Oracle. (I have been the Jira admin, but I'm not a dba or dev guy).

1)  Can this db migration be accomplished thru the XML backup?

2) Are there predefined versions I must touch in the upgrade path?  Surely I don't need to stop at each version, right?  

Information on these subjects has been difficult to sort out for me.

Thank you,

Sean

[Nic Brough -Adaptavist-]

1) Yes

2) No

Do the database migration and the upgrade as different projects (even if the go-live plan is to do them together).  Use Postgres until you get to a version of Jira that is best for the migration to Oracle and do the migration on the same versions rather than try to include it as part of an upgrade.

Starting at Jira 5.0, the recommended upgrade path is 6.0 -> 6.4 -> 7.0 -> 7.target.  You might be ok to jump from 5.0 to 6.4, but do not try to go straight to 7.0, and do not try to skip the 7.0 step.

[Sean Johnson]

Thank you Nic.

[Sean Johnson]

Dumb follow up question.   I'm also moving to a new server.  This might violate one of the principles you shared, but I'm wondering if I can I take this approach:

-Create XML backup

-Build new server and install clean Jira 5.0 or 6.0  w/Oracle connection and use XML to restore jira projects  (ie. bypass postgres entirely)

- then proceed to follow the upgrade path.

[Sean Johnson]

You said,  "Use Postgres until you get to a version of Jira that is best for the migration to Oracle "   

Are you suggesting this because of the Oracle support offered at each Jira version?   

eg. Java 1.8 isnt supported until jira 6.4...  And I'm assuming I need the project structures in the db to upgrade with the software.

So in that case, can I create and migrate Jira to currect using the H4 eval db, and then connect to Oracle when I'm at an appropriate version.  (we are limited on postgres dba's here so trying to avoid using that if possible.

thanks again.

[Nic Brough -Adaptavist-]

Not dumb at all.

Your proposal will work fine too, BUT, only for the same version of Jira.  If you export from 5.0, then build the new server at 5.0 and import the xml into that.

In theory, an xml import into 6.0 will work, but it's certainly no longer the recommended (or supported) upgrade path and it's almost certain to cause issues unless you have an unusually simple and well-housekept Jira.  Move and migrate to Oracle at the same version is safe and supported.

[Nic Brough -Adaptavist-]

Ah, our comments crossed over!  Yes, I was suggesting swapping to oracle at a version that is supported.  I'd draw up the version matrix so you know which Jira versions work with which oracle versions.  But from memory, I think you could well find it easiest to move to oracle on Jira 5.

[Sean Johnson]

Thank you Nic.  I'm digesting all this and preparing the server.  

Also, I'm going to be doing the same thing to our Confluence software.

Currently on 4.1.6  planning on moving to 6.10 (new server, new db, etc).   Do you happen to know the required versions I need to hit on the way up?

Sean

[Nic Brough -Adaptavist-]

Yup, anything from 4 to 4.3 - jump it to 5.10.highest, then 6.10.highest (I suspect that will hold true for all versions of 6 - they will continue to be happy to upgrade from 5.10)

[Ajinkya Paradkar]

Hi Nic ,

Just saw this post , we are also performing Oracle 11g to Postgres 9.3 migration on new infrastructure.

• We have installed Jira 6.4.13 on the new infra host and connected to the existing Oracle 11g DB.
• Connected JIRA to the Postgres 9.3 DB & Schema.
• As a third step , we are planning to cleanup the Postgres Schema and request our DB team to migrate Oracle Schema to Postgres ( It is a Schema backup restore ).

Is the third step recommendable , we do not want to go through the Xml backup and restore option as we are planning multiple upgrades > Postgres 9.3 , Postgres 9.6 & Postgres 11.2 .

Please advise.

Thanks,

Ajinkya

[Nic Brough -Adaptavist-]

Do not do that.  It is unlikely that your "clean up" and "migrate schema" will leave you with a working Postgres database.

Once you've got a working (but very empty) Postgres database, you should empty out all the tables and then import the data from the Oracle data - just the data.

[Ajinkya Paradkar]

Thanks Nic for the quick reply.

Just to confirm here , we have a Postgres 9.3 installed with a newly created empty schema as of now , which when connected to Jira created some tables on the startup.

You are advising just to migrate the data from Oracle now to the new and empty schema ( without dropping any DB objects , if there are any )

Looking forward for your reply and the will implement this.

Many Thanks ,

Ajinkya

[Nic Brough -Adaptavist-]

Yes, it's just the converted data.  Converting the structure often doesn't work properly, it's far better to let Jira get the structure right and then import.

[Ajinkya Paradkar]

Ok , but the problem here is we are not doing the DB switch with Xml backup , we are doing the schema backup.

DB team says that on connection to Postgres DB , JIRA created 210 tables whereas the Oracle Schema has 350 tables , also they are not sure if the table definitions are same for both Oracle and Postgres.

So , they are suggesting dropping the 210 tables and restoring the entire schema from oracle , whereas you are suggesting to let the 210 tables there and migrate the oracle schema and the migration tool will skip the 210 and create the remaining tables and add data to it.

Please correct me if I am wrong here.

[Nic Brough -Adaptavist-]

This is one of the reasons you should be looking at the XML export/import.

The extra tables in your Oracle schema could be because Oracle databases for Jira are structured differently to Jira ones, and you'll have to work out how to change the data you are importing, or they've been created by an app, in which case you should install that app on the target system to have it create the tables you need before you import the data.

Oracle and Postgress databases are NOT compatible.  You can not just take a schema from an Oracle database and import it into Postgres.  They're not the same thing.

What schema conversion tools were you thinking of using?

[Ajinkya Paradkar]

Hi , 

We are using ora2pg tool for the schema conversion.

Ok , as per your suggestion , let me try to place the installed-plugins directory on the new jira host and then try to migrate the schema.

Will keep you posted if it works.

Thanks,

Ajinkya

[Nic Brough -Adaptavist-]

It's worth a quick look at the tables that are "extra" - they should all start with AO_xxxxxx if they are from apps and plugins!  You can use the xxxxxx in there to look them up if you need to, but it's probably easier to look through the plugins you've got installed on the source system!

[Ajinkya Paradkar]

Hi Nic ,

We tried the XML Export/Import option as you suggested for the Database switch on the new host.

It worked quite well except the attchments ( which we copied manually ) , they fail to load post the DB switch with the error :

com.atlassian.jira.issue.attachment.NoAttachmentDataException: Attachment does not exist in filesystem

Any inputs on these , I found this https://confluence.atlassian.com/jirakb/existing-attachments-will-not-open-after-migrating-jira-server-785453577.html , but not sure how to fix these errors.

Thanks,

Ajinkya