Обсуждение: 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
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.
From: Tim Uckun <timuckun@gmail.com>
To: pgsql-general <pgsql-general@postgresql.org>
Sent: Tuesday, 17 January 2012 10:34 AM
Subject: [GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
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.
I guess you need to evaluate the failover and failback mechanisms for each scenario (shared storage on DRBD and streaming replication with trigger file etc) and see if block level replication complexity up front is more or less of a headache than managing the application side replication with somewhat more clunky control mechanisms.
From: Tim Uckun <timuckun@gmail.com>
To: Andy Colson <andy@squeakycode.net>
Cc: John R Pierce <pierce@hogranch.com>; pgsql-general@postgresql.org
Sent: Tuesday, 17 January 2012 12:21 PM
Subject: Re: [GENERAL] HA options
>
> 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.