PostgreSQL Jira: Step-by-Step Guide How to Connect Jira to PostgreSQL Database

An array of advantages for data analysis are provided by combining the strengths of PostgreSQL Jira integration, where PostgreSQL is a potent open-source relational database management system, and Jira is a flexible project management platform. Connecting Jira to PostgreSQL enables seamless data collection and export for your business, analytical, and reporting purposes.

In this article, we tell how to set up a PostgreSQL Jira connection with SQL Connector for Jira as your bridge between these two powerful platforms.

SQL Connector for Jira is a tool to connect Jira to a PostgreSQL database developed by AlphaServe. It also supports other SQL databases, such as Microsoft SQL Server, MySQL, MariaDB, Oracle, and H2. The app enables simple data export from Jira to the databases mentioned above, allowing you to leverage the power of SQL queries and data manipulation capabilities for further business reporting and complex analysis.

It has a user-friendly interface to make data manipulation more accessible to users without coding skills. This direct connection allows for on-request data synchronization, integration, and interaction between the two systems without manual actions. The connector’s broad filtering options allow you to extract specific data from Jira, in a format tailored to your requirements. What is more, you can configure automated data refreshes and add as many data sources as you need for different use cases.

Unlike other Jira data export methods, using SQL Connector for Jira will help you to use the data from Jira add-ons as well. Supported plugins include Advanced Roadmaps, Elements Connect, Insight - Asset Management, R4J, Tempo, Time to SLA, Time in Status, User Profiles for Jira, WorklogPro, Xray Test Management, and Zephyr.

Not to mention, you will see all the data export history and can be sure that your data is properly secured.

Of course, the first step to start benefitting from your operational data processing in PostgreSQL and data export automation is to install the SQL Connector. You can do it in two ways: directly from Jira or by visiting the Atlassian Marketplace product page.

In the first case, you should go to the Jira Apps and select Explore more apps section. Start typing “SQL Connector for Jira Alpha Serve” and press Enter, then hit the box with the app when you see it in the search results.

You will be offered to try the product for free for 30 days. After hitting the corresponding button, you will be redirected to the installation page. If you have Jira Administrator rights, simply click Start free trial and follow the instructions of the installation master. Otherwise, request your Jira Admin to install SQL Connector for Jira for you.

You can also install SQL Connector for Jira directly from Atlassian Marketplace by visiting the application product page. Select either Cloud or Data Center hosting option and click Try it free. Please bear in mind that to install the Jira PostgreSQL connector you should have Jira administrator’s rights.

To create the one for Jira SQL Connector, you need to go to Account settings, select the Security tab, and hit the Create and Manage API tokens link.

You will see the Create API token button. Click it and add the API token name.

When this is done, copy your new API token as you will not have a chance to do it later.

You will have to paste the copied token to the corresponding section in the SQL Connector for Jira. Launch the app and navigate to Tokens > Jira API token, insert the token, and click Validate & Save.

If you wish to fetch the data not only from Jira but also from its add-ons, you need to create responsive tokens for each of the additional plugins. See the detailed instructions here.

You will need to accomplish database configuration to validate the Jira PostgreSQL connection.

To launch a PostgreSQL database using Docker, you start a container with specific settings, such as the container name, data storage location, and superuser password. This container acts as a virtual computer running the database. Once the container is running, you can log into it and configure the database by creating a user with limited privileges, creating a database for data export, and granting access to the user within a specific schema.

When done, it’s time to check whether we have configured everything right and try to connect the database. In the SQL Connector for Jira navigate to the add a database step, and click Validate connection.

If you require detailed instructions on how to configure the PostgreSQL database, read our documentation.

After making the selection, simply hit the Save button.

The next is the Filter Issues section. As already mentioned the app allows doing as narrow data selection as possible. And it is here where the magic happens.

To start, you can choose the All option to add all the issues you have in your Jira project, the option Select by JQL allows using JQL to create your own request and filter data, and, finally, the Basic option will select the standard fields for you.

You can further configure the Basic filter by clicking the Issue filter button.
Here you can add specific projects, and select issue types and statuses.

It is also possible to include the issues that were created or updated at a certain time period. After making your choices, don’t forget to click Apply to save the changes.

Select Fields is the next section you will see on the data source creation screen. The SQL Connector for Jira provides a comprehensive set of tables and fields that can be exported for data analysis: issues, projects, issue types, statuses, components, users, issue custom fields and time in status and with assignee. These are just a few examples of the tables and fields available for export using the SQL Connector for Jira. By leveraging these data points, you can perform in-depth analysis, generate custom reports, and gain insights into your Jira projects for improved decision-making and project management.

Here you can use a number of useful tools to narrow your selection. They include the search field, view options, tabs with a list of Jira fields, and tables you can export, accompanied by the checkboxes for your convenience.

When you are ready with data selection, hit the Save button to save the data source.

It is also possible to preview the data selected for export and the Entity Relationship Diagram by clicking on the corresponding buttons.

SQL Connector lists all the available data sources. So, to start exporting the data, you should click the Export data button in front of the one you need.

When exporting data from this data source for the first time you will need to either set a new connection or use an existing one.

When ready with this step, click Save, then hit the Export data button again if it’s the first time you set up the connection.

To take control of the data export, you will see the corresponding statuses for the button. Possible options include Cancel export (in this case you can cancel export), Not Exported, Exported, Loading, or Error (with the details of what’s wrong).

You can also configure Auto-Export. This feature is available in the right menu of the data source. It allows enabling/disabling the automatic data export to the selected database and setting the time for the daily autorefresh.

Check our User guide to learn more about additional features available.

By following the step-by-step guide provided in our article, you can simplify the process and optimize the compatibility between Jira and PostgreSQL database. Taking the time to prepare your Jira data properly will result in a smoother export process, improved data integrity, and enhanced performance in your PostgreSQL database.

This no-code solution for PostgreSQL Jira integration enables you to access and manipulate using advanced features: filters, data export scheduling, and support of Jira add-ons, to name a few.