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.