Connecting JIRA to PostgreSQL

2. Create and Configure the PostgreSQL Database

1. Create a database user 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.

   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.
   Remember this database name, as it will be used to configure JIRA's connection to this database in subsequent steps.

3. Copy the PostgreSQL JDBC Driver to Your Application Server (JIRA WAR Only)

Skip this step if you installed a 'Recommended' distribution of JIRA, which (unlike JIRA WAR) includes the PostgreSQL JDBC driver.

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

4. 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 Configuration Tool (except JIRA WAR distributions) or
• Manually

Please Note:

• The JIRA Configuration Tool requires a Java platform to be installed and configured on your operating system. We recommend using a Java platform supported by JIRA — refer to JIRA Requirements for details.
• If you use the JIRA WAR distribution or have a console-only connection to your JIRA server, you will need to configure JIRA with PostgreSQL manually.

4.1 Connecting JIRA to PostgreSQL Using the JIRA Configuration Tool

The JIRA Configuration Tool is not available with JIRA WAR distributions.

• On Windows:
  Open a command prompt and run config.bat in the bin subdirectory of the JIRA Installation Directory.
• On Linux/Unix
  Open a console and execute config.sh in the bin subdirectory of the JIRA Installation Directory.

To connect JIRA to PostgreSQL using the JIRA Configuration Tool:

1. Click the Database tab.
2. From the Database type drop-down choose PostgreSQL.
   The JIRA Configuration Tool will display your current database configuration settings if any are already set.
   jira-configuration-tool-postgresql-config.png
3. Fill in the connection details for your PostgreSQL database.
   
   • Hostname — The name or IP address of the machine that the PostgreSQL server is installed on.
   • Port — The TCP/IP port that the PostgreSQL server is listening on. You can leave this blank to use the default port.
   • Database — The name of your PostgreSQL database (into which JIRA will save its data).
   • Username — The user that JIRA uses to connect to the PostgreSQL server.
   • Password — The user's password to authenticate with the PostgreSQL server.
   • Schema — The name of the schema that your PostgreSQL database uses.
     Please ensure your database's schema name is lower-case as JIRA cannot work with PostgreSQL databases whose schema names contain upper-case characters.
4. After typing in your settings, click the Test Connection button to test the connection settings. The tool will attempt to connect to the database, and give a message with the results.
5. Click Save to save your settings when you are done.
   Please Note:
   
   • The JIRA Configuration Tool will save your database configuration to a dbconfig.xml file in your JIRA Home Directory.
   • JIRA must be restarted for your new settings to take effect.

Congratulations — you have finished! Proceed to 'Next Steps' below.

4.2 Connecting JIRA to PostgreSQL Manually

1. Edit 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 below into this file and edit the pasted XML as required.

   Please Note:

   Ensure that the <database-type/> element's content specifies your type of database, as shown below. If you forget to do this and you start JIRA, your database tables may be created incorrectly. Refer to our Incorrect database type specified documentation if this happens to you.
   PostgreSQL 7.2 and later requires 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. Please ensure your database's schema name is lower-case as JIRA cannot work with PostgreSQL databases whose schema names contain upper-case characters.

   When editing your dbconfig.xml file, escape any '&' characters by adding 'amp;' to the end of each one.

   <?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>
     </jdbc-datasource>
   </jira-database-config>
   

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

2. Save your edited dbconfig.xml file (at the root of your JIRA Home Directory).
   JIRA must be restarted for your new settings to take effect.

5. Next steps

You should now have JIRA configured to connect to your PostgreSQL database.

• 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.

Installation notes

Please see JIRA and PostgreSQL.