Is anyone using PGPOOL for JIRA, Confluence and Bitbucket data center editions

[Anil Kumar Patcha]

We are getting db exceptions as below during issue creations. This is intermittent error and not occurring always. 

"2019-01-10 01:38:38,714 http-nio-8080-exec-9 ERROR XXXXX 98x109961x1 c5x1a 10.23.147.254 /secure/QuickCreateIssue.jspa [c.a.j.bc.issue.DefaultIssueService] Error creating issue:
com.atlassian.jira.exception.CreateException: Could not create workflow instance: root cause: while inserting: [GenericEntity:OSWorkflowEntry][name,Software Simplified Workflow for Project XXXXXX][id,51276][state,0] (SQL Exception while executing the following:INSERT INTO public.OS_WFENTRY (ID, NAME, INITIALIZED, STATE) VALUES (?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint ""pk_os_wfentry""
  Detail: Key (id)=(51276) already exists.))

Also we are seeing below DB exception in the logs. 

2019-01-11 09:46:37,416 http-nio-8080-exec-12 ERROR XXXXXX 586x525577x1 do0rdz 10.23.147.254 /secure/QuickCreateIssue.jspa [c.a.j.quickedit.action.QuickCreateIssue] com.atlassian.plugins.rest.common.json.JsonMarshallingException: org.codehaus.jackson.map.JsonMappingException: Caught PSQLException for select ACTION.id, ACTION.issueid, ACTION.author, ACTION.actiontype, ACTION.actionlevel, ACTION.rolelevel, ACTION.actionbody, ACTION.created, ACTION.updateauthor, ACTION.updated, ACTION.actionnum
    from public.jiraaction ACTION
    where ACTION.issueid = ? and ACTION.actiontype = ?
    order by ACTION.created asc, ACTION.id asc
    limit ? (through reference chain: com.atlassian.jira.quickedit.rest.api.field.QuickEditFields["createdIssueDetails"]->com.atlassian.jira.rest.v2.issue.IssueBean["fields"]->java.util.HashMap["comment"])
com.atlassian.plugins.rest.common.json.JsonMarshallingException: org.codehaus.jackson.map.JsonMappingException: Caught PSQLException for select ACTION.id, ACTION.issueid, ACTION.author, ACTION.actiontype, ACTION.actionlevel, ACTION.rolelevel, ACTION.actionbody, ACTION.created, ACTION.updateauthor, ACTION.updated, ACTION.actionnum
from public.jiraaction ACTION
where ACTION.issueid = ? and ACTION.actiontype = ?
order by ACTION.created asc, ACTION.id asc
limit ? (through reference chain: com.atlassian.jira.quickedit.rest.api.field.QuickEditFields["createdIssueDetails"]->com.atlassian.jira.rest.v2.issue.IssueBean["fields"]->java.util.HashMap["comment"])
	at com.atlassian.plugins.rest.common.json.DefaultJaxbJsonMarshaller.marshal(DefaultJaxbJsonMarshaller.java:57)
...
Caused by: org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" already exists

Have you seen these type of errors? If yes, what was the resolution to fix them? Also, as we are using PGPOOL (Officially not supported by Atlassian) we need to your help to identify the best practices to use PGPOOL with Atlassian data center editions. 

[Dave Theodore [Coyote Creek Consulting]]

Are you running an Active/Active Postgres cluster? If not, my recommendation would be to not use PGPOOL.  It really doesn't add any value unless you're Active/Active.  

We most commonly set up Postgres clusters as Active/Passive and use Repmgr to handle the replication.  It can be tuned to failover quickly.  We've seen slave promotion to master typically in under 20 seconds. If you have a VIP database IP and use keepalived to move the IP to the active node, you should expect a complete failover in under 30 seconds. It's a pretty simple, elegant solution that doesn't require a Postgres expert to manage, as well.

This also has the benefit of bringing you closer to Atlassian Support's ability to help you.

[Anil Kumar Patcha]

Hi Dave,

We are using Active/Active Postgres cluster. Can you suggest in this case?

[Eddy Fras]

Hi Anil,

Did you manage to get an answer to your question ?

I also need to setup an active/active pgsql cluster and as far as I know, pgpool is the most reliable solution for that but I need to be sure it's compatible with Jira & Confluence Data Center releases.

[Anil Kumar Patcha]

Hi Eddy,

No. I didn't get answer for active/active pgsql cluster. We have removed PGPOOL and pointed to stand alone db node as recommended by Atlassian. And searching for other solution to maintain database high availability.

[Eddy Fras]

Thanks for your quick answer Anil.

I can clearly see that my single pgsql node is saturated with regular 100% cpu consumption spikes due to I/O but I have no idea on how I can fix that, that's why I thought to add a second node to handle more requests.

I understand that I have to find another option ...

If you have any tips for pgsql configuration for Jira/Confluence DC, I'm interested !

[Suman Brar]

Its an old post but looking for any pointers if someone had success with running Jira using pgpool and Postgres HA. @swzh02 @Anil Kumar Patcha  Were you able to run this successfully. 

[swzh02]

Hi Anil, we're having this issue too using PGPool. Have you found a better database high availability option? Thanks.