Connecting JIRA to PostgreSQL

These instructions will help you connect JIRA to a PostgreSQL database. A version of these instructions specific to Linux and JIRA is available.

Before you begin

On this page:

1. Create and configure the PostgreSQL database

  1. Create a database user (login role) which JIRA will connect as (e.g. jiradbuser).
    Remember this database user name, as it will be used to configure JIRA's connection to this database in subsequent steps.
  2. Create a database for JIRA to store issues in (e.g. jiradb) with Unicode collation.
    Remember this database name, as it will be used to configure JIRA's connection to this database in subsequent steps.

    CREATE DATABASE jiradb WITH ENCODING 'UNICODE';

    Or from the command-line:

    $ createdb -E UNICODE jiradb
  3. Ensure that the user has permissions to connect to the database, and to create and write to tables in the database.

2. Copy the PostgreSQL JDBC driver to your application server (JIRA WAR only)

(warning) Skip this step if you installed a 'Recommended' distribution of JIRA, which includes the PostgreSQL JDBC driver. The JIRA WAR distribution does not include this driver.

  1. Download the PostgreSQL JDBC driver from http://jdbc.postgresql.org/download.html. (Obtain version 9.0 of the JDBC 4 driver.)
  2. Add the PostgreSQL JDBC driver jar to the lib/ directory of your application server.

3. Configure your JIRA server to connect to your PostgreSQL database

There are three ways to configure your JIRA server to connect to your PostgreSQL database:

  • Using the JIRA setup wizard (not applicable to JIRA WAR) — Use this method, if you have just installed JIRA and are setting it up for the first time. Your settings will be saved to the dbconfig.xml file in your JIRA Home Directory.
  • Using the JIRA configuration tool (not applicable to JIRA WAR) — Use this method, if you have an existing JIRA instance. Your settings will be saved to the dbconfig.xml file in your JIRA Home Directory.
  • Manually — Only use this method if you have a JIRA WAR instance or you have a console-only connection to your JIRA server. You will be required to manually update the dbconfig.xml file in your JIRA Home Directory.

Instructions for each configuration method

JIRA setup wizard

The JIRA setup wizard will display when you access JIRA for the first time in your browser.

  1. In the first screen, 'Configure Language and Database', set Database Connection to My own database.
  2. Set Database Type to PostgreSQL.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.
 

JIRA configuration tool

  1. Run the JIRA configuration tool as follows:
  2. Navigate to the Database tab and set Database type to PostgreSQL.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.
  5. Restart JIRA. 
 

Manually

  1. Locate the dbconfig.xml file at the root of your JIRA Home Directory.
    • If this file does not exist, create the file, copy and paste the example XML code (shown below) into this file.
  2. Update the file, as described in the Database connection fields section below. Escape any '&' characters by adding 'amp;' to the end of each one.
    • Note, the <database-type/> element must specify your type of database, e.g. postgres72. If you forget to do this and you start JIRA, your database tables may be created incorrectly. See Incorrect database type specified if this happens to you.
  3. Save the file and restart JIRA.

Database connection fields

Setup Wizard / Configuration Tooldbconfig.xmlDescription
Hostname

Located in the <url> tag (bold text in example below):
<url>jdbc:postgresql://dbserver:5432/jiradb</url>

The name or IP address of the machine that the PostgreSQL server is installed on.
PortLocated in the <url> tag (bold text in example below):
<url>jdbc:postgresql://dbserver:5432/jiradb</url>
The TCP/IP port that the PostgreSQL server is listening on. You can leave this blank to use the default port.
DatabaseLocated in the <url> tag (bold text in example below):
<url>jdbc:postgresql://dbserver:5432/jiradb</url>
The name of your PostgreSQL database (into which JIRA will save its data). You should have created this in Step 1 above.
Username

Located in the <username> tag (see bold text in example below):
<username>jiradbuser</username>

The user that JIRA uses to connect to the PostgreSQL server. You should have created this in Step 1 above.
PasswordLocated in the <password> tag (see bold text in example below):
<password>jiradbuser</password>
The user's password — used to authenticate with the PostgreSQL server.
SchemaLocated in the <schema-name> tag (see bold text in example below):
<schema-name>public</schema-name>

The name of the schema that your PostgreSQL database uses.

PostgreSQL 7.2 and later require a schema to be specified in the <schema-name/> element. If your PostgreSQL database uses the default 'public' schema, this should be specified in the <schema-name/> element as shown below. Ensure that your database schema name is lower-case, as JIRA cannot work with PostgreSQL databases whose schema names contain upper-case characters.

Sample dbconfig.xml file

For more information about the child elements of <jdbc-datasource/> beginning with pool in the dbconfig.xml file above, see Tuning Database Connections.

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>postgres72</database-type>
  <schema-name>public</schema-name>
  <jdbc-datasource>
    <url>jdbc:postgresql://dbserver:5432/jiradb</url>
    <driver-class>org.postgresql.Driver</driver-class>
    <username>jiradbuser</username>
    <password>password</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
 
    <validation-query>select version();</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
  
    <pool-test-while-idle>true</pool-test-while-idle>

  </jdbc-datasource>
</jira-database-config>

 

4. Start JIRA

You should now have JIRA configured to connect to your PostgreSQL database. The next step is to start it up!

  • If you are using a 'recommended' distribution of JIRA, start it up and watch the logs for any errors.
  • If you are using the JIRA WAR distribution, rebuild and redeploy the webapp in your application server.

(tick) Congratulations, you now have JIRA connected to your PostgreSQL database.

Installation notes

Please see JIRA and PostgreSQL for topics related to JIRA and PostgreSQL.

Last modified on Jan 21, 2014

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.