Database Replication and JIRA

Question

Hello, I'm an intern currently researching DB replication options for JIRA for my mentor. I've been looking at sources all over the place and I was hoping to get some help.

We are using Postgresql and are looking for a good DB replication option in case there is a hardware failure. So we need a decent failover option. I'm very inexperienced in DBA stuff so please if there is a common way to relate this or a demo someone could provide I would be so very appreciative. Our system runs best on a local host. I have been looking into Pgpool-II as a solution can anyone comments on this?

Dave Sisk · Answer

What I described above of course leaves PGPool as a possible single point-of-failure...however, PGPool also has provisions to handle that.  As well, PGPool can intelligently manage read-only query load balancing, providing some level of horizontal scalability, particularly for apps that have a heavy read slant to their workload. Get the simplest scenario working first, the proceed from that.

Dave Sisk · Answer

PGPool-II is definitely a viable solution to provide a high-availability configuration of PostgreSQL.  You don't need to look for a replication solution for PostgreSQL, it's already built into the Postgres server...look up "PostgreSQL streaming replication".  There are other replication solutions, but what's built into Postgres 9.x is by far the best solution...it's 99.9% transparent, adds very little load to the primary, and it works reliably without much fuss and bother.

However, replicating data to a standby is only part of the HA picture...there are 4 important steps, assuming replication to a standby is active and up-to-date:

1) Detect that the primary failed.  This has to happen first.  Configured correctly, PGPool does this part (quite well and quite quickly).

2) STONITH (Shoot the other node in the head)...in other words, disconnect all app connections from the failed primary db (some may still be connected, even though some have failed).  Configured correctly, PGPool does this part.

3) Promote the Postgres standby to become the new primary.  Configured correctly, PGPool does this part.

4) Reconnect the application connections to the new DB primary.  Configured correctly, PGPool does this part.

Hope this helps,

Dave

Arthur Gonçalves · Answer

Hey Kyle,

I recommend you to take a look on this article from the official Postgres documentation, which can give you some ideas on how to achieve this.

-- Arthur Gonçalves