It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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. 

 

1 answer

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.

Hi Dave,

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

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.

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.

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 !

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

We want to hear from you! How do you use Slack and Atlassian together?

Hi Everyone! My name is Mina and I am on Atlassian’s Ecosystems Marketing team. Our team is focused on our technology partnerships and marketplace apps. One of Atlassian’s partners is Slack, who ...

97 views 1 6
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you