Does Jira 7.3 support pgpool-ii?

[Matthew Thompson]

Pgpool-ii is middleware that provides connection pooling, replication, load balancing for postgresql.

http://www.pgpool.net/mediawiki/index.php/Main_Page

If Jira doesn't support pgpool-ii does Atlassian have any other recommendations for Database HA solutions for postgresql with Jira.

[Gonchik Tsymzhitov]

@Matthew Thompson  Finally what did you use? I use pgpool-II on 8.13.3

[Nic Brough -Adaptavist-]

You'll need to explain what pgpool-ii is.

[Dave Sisk]

PGPool is intelligent database proxy for PostgreSQL.  Applications connect to PGPool *thinking* they are connecting directly to Postgres, and PGPool uses pooled connections to the actual database behind the scenes. 

Why go to all this trouble?  A number of reasons. Postgres natively supports streaming replication to one or more hot standby's which, in the event of a failure of the primary read/write Postgres instance, can simply be promoted to function as a new primary. However, promoting a standby is only one piece of the high availability picture...other steps that have to happen include 1) kill all connections to the original primary DB, 2) promote the standby so that it's writeable in addition to readable, 3) reconnect the application to the new primary.  PGPool does lots of things, but this is an important (and reliable) one designed to provide high availability.  Jira and Confluence both handle this just fine via their own app-level connection pooling...the apps will see an error on the first transaction attempt, but will then reconnect and continue processing user requests with no interruption.  This works quite well, even in all important production environments.  PGPool is a big complicated to configure properly, but it can be done.  Reach out to me if you have specific questions.

o

[Dave Sisk]

One of the other useful things that PGPool can do is read-only query load-balancing.  As I mentioned above, it's an intelligent database proxy, so it knows in general how to route truly read-only queries to a standby vs how/when to route write queries and read queries that are part of a write transaction to the db primary.   However, there are a few glitches in this with Jira and Confluence...there are issues on startup.  Somewhere in the startup set of queries, both Jira and Confluence somehow attempt operations against a standby that can only be performed against a primary.  Sometimes Jira/Conf will successfully start up, sometimes it won't.  AFTER it's started, the query load-balancing with PGPool seems to work quite well...however, getting the apps to start successfully is the issue.  This needs some troubleshooting, but I'll provide more details at some future time. 

So, I'd suggest that you go read the PGPool docs and familiarize yourself with it.  PostgreSQL is one of your premier platforms for Atlassian apps...you surely should know at least what PGPool is. ;-)

Regards,

Dave