JIRA OnDemand : Can we get a PostgreSQL dump?

For your on-demand service, is there anyway we could get a periodic PostgreSQL backup dump of the data set? We'd like to run SQL based reports using your schema, and, it seems re-creating your schema via the XML data file might be tedious. We're thinking having a pg_dump might be much simpler.

1 answer

1 vote

No. Access to the database isn't granted as part of OnDemand/Cloud, and providing dumps isn't standard procedure. (Atlassian might well do it on a support request, but you'll need to make a new request each time you want it, they won't do it often, and it could take a few days as a low-priority task)

If you want a download of all your data, use the "backup" feature which will dump everything to a standard Jira xml backup which you can load into a local Jira any time you want. You can do this daily.

Then, I guess, my request is that they might include the PostgreSQL dump as an option of their backup feature.

Lacking that, how would I convert a standard backup into a PostgreSQL database? Is there any sort of software you can download to do that?

I very much doubt it will happen, you've got access to XML which you can import into any database you want, why bother with a dedicated dump.

To import it, set up a dev/test Jira, point it at an empty database of your choice, and run the installation procedure, choosing "I have an xml backup" and pointing it at the file you downloaded.

No fuss, no manipulations, no hassle of database fiddling, you start clean and import it. Once you've imported once, you can leave the dev/test transfer system in place and next time you need it, just import the new file from "Import" in the admin GUI

Hey Clark.

You can use that backup to create a local JIRA instance with it, as said by Nic, using this guide.

Is there an automated mechanism for doing this? We'd like the "reporting clone" to be setup nightly, and, a manual process would be a show stopper. Is there a script that'll take the XML file and rebuild the PostgreSQL database?

You'll need to write something appropriate for your setup.

Assuming you have set up a dev/test transfer system, then your script will need to trigger the creation of the nightly backup, pull the file from the provided url, move it on to your dev/test system, then run a local import of it. Shouldn't be too hard with a bit of curl.

There is a script posted here for pulling down a nightly XML snapshot. However, I don't see any scripts that do the automated import: the instructions are GUI based. Is there a command line import mechanism? Regardless, it's an awful lot of work to just get a pg_dump of the database.... and likely brittle/expensive to maintain.

Also, backups in OnDemand are limited to every 48 hours so nightly will not be possible.

Oops, my mistake, I thought it was 24 hours.

Anyway, again, the reason for providing the xml option is that it is portable for ALL clients. A pg_dump is a waste of time for a lot of clients, and you're not expected to be downloading this stuff regularly either - that's simply not what it's built or intended for.

I'm expecting to download the snapshot regularly to build internal reports. What I'd like to know, is, once I've downloaded the XML, if there is a way to build the database via script?

So, as I understand, the answer is: no, there's no automated way to get a PostgreSQL database copy of an On-Demand JIRA Instance.

Correct, exactly as you were told originally, there is no database dump available, onlt the xml. Which you can load into any database you want via a Jira import.

I think a more constructive train of thought might be

1) Is there any value in moving to self-hosted so you can dump the data whenever you want, or even replicate it and report off the replicated copy in near-real time (I can't recommend reporting directly off an active Jira database, I've seen it go wrong far too often)

2) What "internal reports" are you trying to do? Why can't you do them in Jira directly?

Suggest an answer

Log in or Join to answer
Community showcase
Alexey Matveev
Published Saturday in Jira

How to run Jira in a docker container

Everything below is tested on Ubuntu 17.10. I prefer to use Jira in a docker container because: 1. I can install Jira with a couple of commands. 2. I can start and stop Jira just by starting and s...

230 views 6 8
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot