About Pgpool-II

Pgpool-II is a high-availability (HA) database solution based on Postgres. Here’s why we recommend moving to high-availability databases like Pgpool-II:

1. No single point of failure (SPoF). Pgpool-II addresses the challenges typical of PostgreSQL databases that expose a Single-Point-of-Failure resulting in business impact due to service downtimes.

2. Connection pooling. Pgpool-II offers connection pooling which allows multiple client applications to share a pool of database connections. This significantly reduces the overhead of establishing new connections for each client request, resulting in improved performance and reduced resource consumption.

3. Load balancing. Pgpool-II includes a built-in load balancer that distributes client requests across multiple PostgreSQL servers. This helps distribute the workload evenly and ensures optimal resource utilization across the available database servers.

4. High availability. Pgpool-II supports high availability configurations by implementing features such as automatic failover and online recovery. It can detect when a primary PostgreSQL server fails and automatically promotes a standby server to take its place, minimizing downtime and ensuring continuous availability of the database.

Learn more about what Pgpool-II is from its official documentation

Before you begin

• Check whether your version of PostgreSQL is supported. For more details, refer to Supported platforms.
• If you're migrating Jira to another server, export your data to create a backup. You'll be able to transfer the data from the old database to the new database. Learn more about migrating data between databases in Switching databases.
• Shut down Jira before you begin, unless you're running the setup wizard.

1. Run and configure the Pgpool-II environment

For illustration in this document, we're going to use Docker images from Bitnami by VMware. According to the official Pgpool documentation, this approach has several benefits:

• Bitnami closely tracks upstream source changes and promptly publishes new versions of this image using our automated systems.
• With Bitnami images, the latest bug fixes and features are available as soon as possible.

Setup

First, you need to set up Postgres nodes. They must be accessible to one another. They can be a part of the same private subnet or be exposed to the Internet, though exposure to the Internet isn’t recommended.

1. Create a primary PostgreSQL node on a separate machine. Run the following command:

   docker network create my-network --driver bridge

   The launch of the node will look as follows:

   docker run --detach --rm --name pg-0 \
     -p 5432:5432 \
     --network my-network \
     --env REPMGR_PARTNER_NODES={PG-0-IP},{PG-1-IP} \
     --env REPMGR_NODE_NAME=pg-0 \
     --env REPMGR_NODE_NETWORK_NAME={PG-0-IP} \
     --env REPMGR_PRIMARY_HOST={PG-0-IP} \
     --env REPMGR_PASSWORD=repmgrpass \
     --env POSTGRESQL_POSTGRES_PASSWORD=adminpassword \
     --env POSTGRESQL_USERNAME=customuser \
     --env POSTGRESQL_PASSWORD=custompassword \
     --env POSTGRESQL_DATABASE=customdatabase \
     --env BITNAMI_DEBUG=true \
     bitnami/postgresql-repmgr:latest

   
   The message [NOTICE] starting monitoring of node "pg-0" (ID: 1000) confirms the successful creation of the primary node.

2. Create a standby node on a separate machine. Run the following command:

   docker network create my-network --driver bridge

   The launch of the node will look as follows:

   docker run --detach --rm --name pg-1 \
     -p 5432:5432 \
     --network my-network \
     --env REPMGR_PARTNER_NODES={PG-0-IP},{PG-1-IP} \
     --env REPMGR_NODE_NAME=pg-1 \
     --env REPMGR_NODE_NETWORK_NAME={PG-1-IP} \
     --env REPMGR_PRIMARY_HOST={PG-0-IP} \
     --env REPMGR_PASSWORD=repmgrpass \
     --env POSTGRESQL_POSTGRES_PASSWORD=adminpassword \
     --env POSTGRESQL_USERNAME=customuser \
     --env POSTGRESQL_PASSWORD=custompassword \
     --env POSTGRESQL_DATABASE=customdatabase \
     --env BITNAMI_DEBUG=true \
     bitnami/postgresql-repmgr:latest

   1. Replace {PG-0-IP},{PG-1-IP} in the code sample with comma-separated IP addresses that can be used to access pg-0 and pg-1 nodes. For example, 15.237.94.251,35.181.56.169.
   2. To establish a mutual connection, the standby node tries to access the primary node right after starting.

3. Create a Pgpool balancer middleware node with the reference to the other nodes. Run the following command:

   docker network create my-network --driver bridge

   The launch of the node will look as follows:

   docker run --detach --name pgpool --network my-network \
     -p 5432:5432 \
     --env PGPOOL_BACKEND_NODES=0:{PG-0-HOST},1:{PG-1-HOST} \
     --env PGPOOL_SR_CHECK_USER=postgres \
     --env PGPOOL_SR_CHECK_PASSWORD=adminpassword \
     --env PGPOOL_ENABLE_LDAP=no \
     --env PGPOOL_USERNAME=customuser \
     --env PGPOOL_PASSWORD=custompassword \
     --env PGPOOL_POSTGRES_USERNAME=postgres \
     --env PGPOOL_POSTGRES_PASSWORD=adminpassword \
     --env PGPOOL_ADMIN_USERNAME=admin \
     --env PGPOOL_ADMIN_PASSWORD=adminpassword \
     --env PGPOOL_AUTO_FAILBACK=yes \
     --env PGPOOL_BACKEND_APPLICATION_NAMES=pg-0,pg-1 \
     bitnami/pgpool:latest

   1. Replace {PG-0-HOST},{PG-1-HOST} in the code sample with the host addresses of the pg-0 and pg-1 nodes, including ports. For example, 15.237.94.251:5432. 
      
      
      Learn more about the configuration of the Bitnami containers
      
      

4. Now, you can use the pgpool container as an entry point to the database cluster. To connect to the pgpool container, use the following command: 
   

   psql -h {PGPOOL-HOST} -p 5432 -U postgres -d repmgr

   Replace {PGPOOL-HOST} in the code sample with the pgpool node address. For example, 34.227.66.69.
   
   To confirm the successful deployment, access the table repmgr.nodes by using the following SQL query:
   

   SELECT * FROM repmgr.nodes;

   The output must show all the information about each node’s state:
   
   

Create users and databases for your version of PostgreSQL

You can find information on creating users and databases for your version of PostgreSQL in the official documentation.

1. Create a database user (login role) Jira will connect to (for example, jiradbuser).
   Remember this database user name as it'll be used to configure Jira's connection to this database in the following steps.

2. Create a database for Jira to store issues with Unicode collation (for example, jiradb).
   Remember this database name as it'll be used to configure Jira's connection to this database in the following steps.

   CREATE DATABASE jiradb WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

   Or do this from the command line:

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

3. Ensure that the user has permission to connect to the database as well as 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 Jira to connect to the database

There are two ways to configure the Jira server to connect to the PostgreSQL database:

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

  How to launch the 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.
  4. Test your connection and save.

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

  How to launch the Jira configuration tool?

  1. Run the Jira configuration tool as follows:
     1. Windows: Open a command prompt and run config.bat in the bin sub-directory of the Jira installation directory.
     2. Linux/Unix: Open a console and execute config.sh in the bin sub-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.
        
  2. Select the Database tab and set Database type to PostgreSQL.
  3. Fill out the fields as described in the Database connection fields section.
  4. Test your connection and save.
  5. Restart Jira. 

Database connection fields

The table shows the fields you'll need to fill out when connecting Jira to your database. You can also refer to them and to the sample dbconfig.xml file under the table if you'd like to create or edit the dbconfig.xml file manually.

Sample dbconfig.xml file

For more information about the child elements of <jdbc-datasource/> beginning with pool in the dbconfig.xml file, 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 Jira up!