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

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.

1 answer

0 votes

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.

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.

"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

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Jan 08, 2019 in Jira

How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

917 views 3 9
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you