In Jira, how do I do a restore from an xml backup using Postgres with table dependencies?

Question

We currently have a Jira server instance using Oracle as the database. We are planning a move to AWS using Postgres RDS. In Oracle, we've developed a number of custom views and functions to provide our Enterprise Reporting team with information for various reports based on Jira issue data.

As part of our migration planning we've created an AWS server instance with Postgres RDS. We used an xml backup to populate the new AWS instance and everything worked fine. We then added our customized reporting views and functions which access both Jira core and add-on tables. We are now unable to perform a restore from backup xml because the restore functionality in Jira tries to drop tables (e.g. AO_60DB71_SPRINT) but it now has a dependency from one of our views and errors out.

I have been unable to find a way to disable the dependency checks in Postgres. Will we have to drop and recreate our custom views and functions any time we want to do a restore from xml? Why is the Jira restore dropping tables instead of just truncating the data?

Any help on this would be appreciated. We have no one in-house with Postgres experience. I don't like the thought of having to couple a database procedure/process with what should be just Jira front-end functionality.

Matt Doar_tzgiej · Answer

"Upgrades do not appear to make any database changes." 6.x to 7.x did

Ah yes, refreshing staging. That's true. I guess all you can do is automate the addition of your changes and removing them before a refresh. That's annoying

John Beveridge_w5lpix · Answer

To be clear, our customizations have not altered any Jira core or add-on database objects; our customizations are net new tables, functions and views. This customization works as expected in Oracle. This new issue is caused by Postgres' built-in dependency checking. We've experienced no issues with restores from xml using Oracle.

Upgrades do not appear to make any database changes. We've upgraded multiple times with the customizations in place with Oracle (6.0.5->....->7.5.0) and have seen no changes.

We use the restore process frequently to update data in our QA environment. This won't be a one off operation.

AWS Postgres RDS is a managed instance. This allows us the freedom to do development without the worries of needing a full-time DBA. We'll have to see how that plays out.

Matt Doar_tzgiej · Answer

It;'s not surprising tto me hat custom DB changes could break the XML import. It's a one-off though, after you've migrated you won't have to do that again. Or perhaps at the next major upgrade.

"We have no one in-house with Postgres experience" is more worrying for your long-term robustness of Jira. Not sure that Postgres was a great choice in that case.