HA options

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 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.

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?

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.