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

This widget could not be displayed.

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
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

220 views 3 0
Join discussion

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