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 11 documentation — Connections and Authentication
- PostgreSQL 10 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.
- 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. 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 jiradbEnsure 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>To verify that the privileges were granted successfully, connect to the database and run the \z command.
To achieve and maintain optimal PostgreSQL performance, schedule daily maintenance tasks to update database statistics.
For information on how to set up regular maintenance tasks, see Optimize and Improve PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX
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.xmlfile 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.xmlfile 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.
- In the first screen, 'Configure Language and Database', set Database Connection to My own database.
- Set Database Type to PostgreSQL.
- Fill out the fields, as described in the Database connection fields section below.
- Test your connection and save.
Jira configuration tool
- Run the Jira configuration tool as follows:
- Windows: Open a command prompt and run
config.batin thebinsub-directory of the Jira installation directory. Linux/Unix: Open a console and execute
config.shin thebinsub-directory of the Jira installation directory.This command might fail with the error as described in Unable to Start Jira applications Config Tool due to No X11 DISPLAY variable was set error. If it happens, refer to this article for the workaround.
- Windows: Open a command prompt and run
- Navigate to the Database tab, and set Database type to PostgreSQL.
- Fill out the fields, as described in the Database connection fields section below.
- Test your connection and save.
- Restart Jira.
Database connection fields
| Setup Wizard / Configuration Tool | dbconfig.xml | Description |
|---|---|---|
| Hostname | Located in the | The name or IP address of the machine that the PostgreSQL server is installed on. |
| Port | Located 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. |
| Database | Located 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 | The user that JIRA uses to connect to the PostgreSQL server. You should have created this in Step 1 above. |
| Password | Located in the <password> tag (see bold text in example below):<password>jiradbuser</password> | The user's password — used to authenticate with the PostgreSQL server. |
| Schema | Located 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 We recommend using public schema as a custom one might cause issues. See JRASERVER-64886 |
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>
Both the Jira setup wizard and database configuration tool also add the element <validation-query>select 1</validation-query> to the dbconfig.xml file, which is usually required when running Jira with default MySQL installations. See Surviving connection closures for details.
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.