JIRA OnDemand : Can we get a PostgreSQL dump?

[Clark Evans]

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.

[Nic Brough -Adaptavist-]

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.

[Clark Evans]

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?

[Nic Brough -Adaptavist-]

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

[Diego Zarpelon]

Hey Clark.

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

[Clark Evans]

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?

[Nic Brough -Adaptavist-]

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.

[Clark Evans]

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.

[Dave]

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

[Nic Brough -Adaptavist-]

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.

[Clark Evans]

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?

[Clark Evans]

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.

[Nic Brough -Adaptavist-]

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?