Thread: HA options

Hey Guys.

It's been a while since I looked into this and it seems like new
options have cropped up for postgres HA and scalability.   Is there a
consensus on the "best" way to achieve HA. My primary concern is HA
but of course any scalability gains would be more than welcome.  All
the servers will be virtual on either rackspace or amazon so that's
possibly a complication.

The options I am looking at are.

http://postgres-xc.sourceforge.net/  Is this production ready?
http://www.repmgr.org/
http://www.pgpool.net/mediawiki/index.php/Main_Page
http://www.continuent.com/downloads/documentation
http://www.drbd.org/ ??
Built in hot standby and hand rolled scripts.

are there others I should look at?

Thanks

On Tue, Jan 17, 2012 at 10:47 AM, David Morton <davidmorton@xtra.co.nz> wrote:
> Is shared storage an option for you ? We've had a fairly pleasant experience
> with shared storage partnered up with SLES and its HAE (high availability
> extension) suite using a Pacemaker cluster for resource control. On top of
> this we replicate to a hot standby server offsite, however used for
> reporting and not failover for us.
>

No unfortunately shared storage is not an option.  It would be awesome
if Amazon allowed you to attach the same EBS to multiple instances but
alas they don't. Rackspace offers a SAN but that's too costly for us.

On 01/16/12 1:34 PM, Tim Uckun wrote:
> the servers will be virtual on either rackspace or amazon so that's
> possibly a complication.


virtual servers tend to have lousy storage performance, for what thats
worth.  the actual physical resources are being shared by who knows what
other workloads, and they tend to be higher latency than direct-attach
storage, or proper SAN.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

>
> virtual servers tend to have lousy storage performance, for what thats
> worth.  the actual physical resources are being shared by who knows what
> other workloads, and they tend to be higher latency than direct-attach
> storage, or proper SAN.

I realize that. Eventually we might have to go to physical machines
but for now we are using virtual servers and I have to make it work
within that structure.

On 01/16/12 2:04 PM, Tim Uckun wrote:
> I realize that. Eventually we might have to go to physical machines
> but for now we are using virtual servers and I have to make it work
> within that structure.

quite the catch-22.   a single well built dedicated server likely would
be MORE reliable than a cluster of two virtual servers, and a lot less
complicated.   C'est la vie.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On 1/16/2012 4:09 PM, John R Pierce wrote:
> On 01/16/12 2:04 PM, Tim Uckun wrote:
>> I realize that. Eventually we might have to go to physical machines
>> but for now we are using virtual servers and I have to make it work
>> within that structure.
>
> quite the catch-22. a single well built dedicated server likely would be
> MORE reliable than a cluster of two virtual servers, and a lot less
> complicated. C'est la vie.
>
>
>

I wonder.  If its a write heavy database, I totally agree with you.  But
if its mostly read-only, and mostly fits in ram, then a pgpool of
servers should be faster.

Be nice to know the usage patterns of this database. (and size).

-Andy

On 1/16/2012 4:13 PM, Andy Colson wrote:
> On 1/16/2012 4:09 PM, John R Pierce wrote:
>> On 01/16/12 2:04 PM, Tim Uckun wrote:
>>> I realize that. Eventually we might have to go to physical machines
>>> but for now we are using virtual servers and I have to make it work
>>> within that structure.
>>
>> quite the catch-22. a single well built dedicated server likely would be
>> MORE reliable than a cluster of two virtual servers, and a lot less
>> complicated. C'est la vie.
>>
>>
>>
>
> I wonder. If its a write heavy database, I totally agree with you. But
> if its mostly read-only, and mostly fits in ram, then a pgpool of
> servers should be faster.
>
> Be nice to know the usage patterns of this database. (and size).
>
> -Andy
>

crap.  ignore me.  You used "reliable" and "complicated", and I used
"faster".  I really should read things more closely.

-Andy

>
> I wonder.  If its a write heavy database, I totally agree with you.  But if
> its mostly read-only, and mostly fits in ram, then a pgpool of servers
> should be faster.
>
> Be nice to know the usage patterns of this database. (and size).
>

In this case the databases are small to medium and the usage is
relatively light.  The main concern is availability and not
scalability although that will probably come into play later on. For
our performance profile we are OK with using virtual servers *at this
time*.

I will need HA no matter what underlying hardware I use but as you
guys have pointed out using virtual servers means a shared nothing
approach for now so I was asking about the pros and cons of the
various projects I listed and people's experiences of them.

I have read the basic introductions to the various projects and have a
rough idea of what they are trying to do but when it comes to
implement something like this the devil is in the details. I am
curious if anybody has used them in production environments and if
given a chance they would go the same route or try something
different.

My use case is pretty simple and I would think extremely common which
is that "the database must be up no matter what".  In addition to
having some sort of a failover I want to implement log shipping to a
remote location and keeping a hot/warm standby there.  Anything over
and above that would be icing on the cake. Things like adding nodes on
the fly, being able to upgrade without downtime, failing back without
having to do a full backup, multi master, etc are very desirable
things to have but *at this time* I can do without them.

Cheers.

On Tuesday, January 17, 2012 10:34:54 AM Tim Uckun wrote:
> Hey Guys.
>
> It's been a while since I looked into this and it seems like new
> options have cropped up for postgres HA and scalability.   Is there a
> consensus on the "best" way to achieve HA. My primary concern is HA
> but of course any scalability gains would be more than welcome.  All
> the servers will be virtual on either rackspace or amazon so that's
> possibly a complication.
>
> http://www.drbd.org/ ??
> Built in hot standby and hand rolled scripts.

I have a few clusters running on EC2 using DRBD to replicate between
availability zones. It's not fast, but it works. If your write load is under
30MB/sec it's definitely an option. I run DRBD over SSH tunnels to get around
the random IP address issue. I use heartbeat on top for resource
management (I know it's not ideal with the single communications
path, but what can you do), and DDNS with really short TTLs for the cluster
names.

I've also had success using the native replication to spin read-only slaves off
those HA masters, using pgpool to load balance selects.

On Tue, Jan 17, 2012 at 12:31 PM, David Morton <davidmorton@xtra.co.nz> wrote:
> Have you looked at a 'shared storage' solution based on DRBD ? I configured
> a test environment using SLES HAE and DRBD with relative ease and it behaved
> very well (can probably supply a build script if you like), there are lots
> of people running production systems on similar setups although I think it
> deserves a solid test and soak period. I was looking at this to form a high
> performance storage tier based on fusion-io hardware.

Do you know if DRBD can be used with EC2/EBS or Rackspace virtual hosts?

On Tuesday, January 17, 2012 10:34:54 AM Tim Uckun wrote:
> http://www.drbd.org/ ??
> Built in hot standby and hand rolled scripts.
>

I have a few clusters running on EC2 using DRBD to replicate between
availability zones. It's not fast, but it works. If your write load is under
30MB/sec it's definitely an option. I run DRBD over SSH tunnels to get around
the random IP address issue. I use heartbeat on top for resource
management/failover (I know it's not ideal with the single communications
path, but what can you do), and DDNS with really short TTLs.

I've also had success using the native replication to spin read-only slaves off
those HA masters, using pgpool to load balance selects.

>
> I have a few clusters running on EC2 using DRBD to replicate between
> availability zones. It's not fast, but it works. If your write load is under
> 30MB/sec it's definitely an option. I run DRBD over SSH tunnels to get around
> the random IP address issue. I use heartbeat on top for resource
> management/failover (I know it's not ideal with the single communications
> path, but what can you do), and DDNS with really short TTLs.

If you have a howto or some documentation on how to get DRBD to work
with EC2 (or rackspace) I'd appreciate a link to it.

> I've also had success using the native replication to spin read-only slaves off
> those HA masters, using pgpool to load balance selects.

I think I am probably going to explore this option first.   I don't
know why automatic failover, failback, etc are not built in already. I
guess even connection pooling ought to be built in. Seems like
everybody would need that no?

On 01/17/12 12:33 PM, Tim Uckun wrote:
> I think I am probably going to explore this option first.   I don't
> know why automatic failover, failback, etc are not built in already. I
> guess even connection pooling ought to be built in. Seems like
> everybody would need that no?

because you quickly get trapped into OS specific quicksand with these
features.

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

>
> because you quickly get trapped into OS specific quicksand with these
> features.
>

Isn't that an issue with just about every feature? Besides the issues
have already been solved mostly. Pgpool already exists. Tatsuo Ishii
says porting a windows is just a resource issue as he doesn't have the
time and is not an expert in  windows.