Connecting JIRA applications to PostgreSQL

1. Create and configure the PostgreSQL database

Accept remote TCP connections (remote PostgreSQL server only)

If you are connecting JIRA to a remote PostgreSQL server (i.e. if your PostgreSQL server is not installed locally on your JIRA server host system), you will need to configure your data/postgresql.conf and data/pg_hba.conf files to accept remote TCP connections from your JIRA server's IP address.

The following PostgreSQL documentation contains information on the appropriate listen_addresses value in the postgresql.conf file as well as the pg_hba.conf file:

• PostgreSQL 9.5 documentation — Connections and Authentication
• PostgreSQL 9.4 documentation — Connections and Authentication
• PostgreSQL 9.3 documentation — Connections and Authentication
• PostgreSQL 9.2 documentation — Connections and Authentication

After you modify the data/postgresql.conf and data/pg_hba.conf files, restart PostgreSQL for the changes to take effect.

Create users and databases for your version of PostgreSQL

You can find information on creating users and databases for your version of PostgreSQL on their website.

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' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

   Or from the command-line:

   $ createdb -E UNICODE -l C -T template0 jiradb

3. Ensure that the user has permissions to connect to the database, and to create and write to tables in the database.

    

   GRANT ALL PRIVILEGES ON DATABASE <Database Name> TO <Role Name>

4. To verify that the privileges were granted successfully, connect to the database and run the \z command.

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

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

• Using the JIRA setup wizard — Use this method if you have just installed JIRA, and you 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 — 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.

Instructions for each configuration method

Database connection fields

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-on-borrow>false</pool-test-on-borrow>
	<pool-test-while-idle>true</pool-test-while-idle>

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

3. Start JIRA

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

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