Обсуждение: HA solution

Hi,

I am looking for active-active clustering solution.

I have one SAN box and two separate NODES, where I need to create active-active cluster. My data directory would be one and mounted to the SAN box for both the nodes. (There will be one sharable data directory for both the nodes) So the query which will come to the load balancer (pgpool) it will route to the node which has a less load. However, it will use the same data directory. It is nothing but the RAC kind of structure.  Now, my question is.

1)      Is it possible above implementation in PostgreSQL?
2)      Has someone implemented cluster in their production environment?

Please experts share your thought/comments/experience how I shall achieve that.


--
Thanks & Regards
Dhaval Jaiswal

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon,this information by persons or entities other than the intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the material from your computer. 
Microland takes all reasonable steps to ensure that its electronic communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software. 

On January 14, 2011, "Jaiswal Dhaval Sudhirkumar" <JaiswalDS@microland.com> wrote:

> Hi,

>

> I am looking for active-active clustering solution.

>

> I have one SAN box and two separate NODES, where I need to create

> active-active cluster. My data directory would be one and mounted to the

> SAN box for both the nodes. (There will be one sharable data directory

> for both the nodes) So the query which will come to the load balancer

> (pgpool) it will route to the node which has a less load. However, it

> will use the same data directory. It is nothing but the RAC kind of

> structure. Now, my question is.

>

> 1) Is it possible above implementation in PostgreSQL?

> 2) Has someone implemented cluster in their production environment?

>

> Please experts share your thought/comments/experience how I shall achieve

> that.

>

You cannot run multiple PostgreSQL instances against the same data directory.

--

A hybrid Escalade is missing the point much in the same way that having a diet soda with your extra large pepperoni pizza is missing the point.

On 01/14/11 9:47 PM, Jaiswal Dhaval Sudhirkumar wrote:
>
> Hi,
>
> I am looking for active-active clustering solution.
>

best of luck.    active-active is fraught with complex hard-to-solve
problems.



> I have one SAN box and two separate NODES, where I need to create
> active-active cluster. My data directory would be one and mounted to
> the SAN box for both the nodes. (There will be one sharable data
> directory for both the nodes) So the query which will come to the load
> balancer (pgpool) it will route to the node which has a less load.
> However, it will use the same data directory. It is nothing but the
> RAC kind of structure.  Now, my question is.
>
> 1)      Is it possible above implementation in PostgreSQL?
>

No

> 2)      Has someone implemented cluster in their production environment?
>

Oracle RAC supports configurations like that, however in our limited
testing, we came to the conclusion that you needed at least 4 nodes
before it was faster than 1 simple database server.   And of course,
these 4 nodes required 4 times the Oracle licensing, which can get
really expensive really fast.



Postgres can scale very nicely vertically with the right hardware (lots
of CPU cores, lots of memory, lots of IO bandwidth).    The standard
database server configuration for the hardware you described is an
active/standby cluster, where all requests are processed by the active
server, and the standby server only takes over if the active server has
failed.

Thanks for your support.

We have power full HP servers with lots of CPU cores, I/O bandwidth and memory too.

Actually I will give you the environment details, which will help you to understand. 

It is a huge set-up where we have a DC & DR. There will be lots of daily edit and read hits. Also there would be lots of read hits from reporting perspective too. Therefore, I thought of to keep one node for edit and other one for read (OLTP vs REPORTING) kind of structure for DC & if it goes down DR will take care. However in your suggested structure there will be only one active node at a time in DC. Even standby database would be in recovery mode.

Initially I thought of the set-up slony replication for (OLTP vs REPORTING) & pgpool replication for (OLTP vs OLTP) but there will be very huge GIS database size & they agreed only on cluster set-up.

I really need thoughts/comments/help from experts.


--
Thanks & Regards
Dhaval Jaiswal




-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of John R Pierce
Sent: Sat 1/15/2011 12:23 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] HA solution

On 01/14/11 9:47 PM, Jaiswal Dhaval Sudhirkumar wrote:
>
> Hi,
>
> I am looking for active-active clustering solution.
>

best of luck.    active-active is fraught with complex hard-to-solve
problems.



> I have one SAN box and two separate NODES, where I need to create
> active-active cluster. My data directory would be one and mounted to
> the SAN box for both the nodes. (There will be one sharable data
> directory for both the nodes) So the query which will come to the load
> balancer (pgpool) it will route to the node which has a less load.
> However, it will use the same data directory. It is nothing but the
> RAC kind of structure.  Now, my question is.
>
> 1)      Is it possible above implementation in PostgreSQL?
>

No

> 2)      Has someone implemented cluster in their production environment?
>

Oracle RAC supports configurations like that, however in our limited
testing, we came to the conclusion that you needed at least 4 nodes
before it was faster than 1 simple database server.   And of course,
these 4 nodes required 4 times the Oracle licensing, which can get
really expensive really fast.



Postgres can scale very nicely vertically with the right hardware (lots
of CPU cores, lots of memory, lots of IO bandwidth).    The standard
database server configuration for the hardware you described is an
active/standby cluster, where all requests are processed by the active
server, and the standby server only takes over if the active server has
failed.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon,this information by persons or entities other than the intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the material from your computer. 
Microland takes all reasonable steps to ensure that its electronic communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software. 

On Saturday 15 January 2011 10:07:14 am Jaiswal Dhaval Sudhirkumar wrote:
> Thanks for your support.
>
> We have power full HP servers with lots of CPU cores, I/O bandwidth and
> memory too.
>
> Actually I will give you the environment details, which will help you to
> understand.
>
> It is a huge set-up where we have a DC & DR. There will be lots of daily
> edit and read hits. Also there would be lots of read hits from reporting
> perspective too. Therefore, I thought of to keep one node for edit and
> other one for read (OLTP vs REPORTING) kind of structure for DC & if it
> goes down DR will take care. However in your suggested structure there will
> be only one active node at a time in DC. Even standby database would be in
> recovery mode.
>
> Initially I thought of the set-up slony replication for (OLTP vs REPORTING)
> & pgpool replication for (OLTP vs OLTP) but there will be very huge GIS
> database size & they agreed only on cluster set-up.
>
> I really need thoughts/comments/help from experts.

First a data directory cannot be shared between database clusters. To put it
another away each database cluster you initdb will need to have its own data
directory.

Second with Postgres 9.0+ there is the concept of hot standby whereby the
standby cluster is available for read only operations while it is in recovery
mode. For an idea of what is possible you might to take a look at this blog:

http://blog.2ndquadrant.com/en/2011/01/easier-postgresql-90-clusters.html

Conclusion. Pulling together what has already been posted. If you want to run
two Postgres instances you will need to have two copies of the data. If you
want one copy of the data you will have to work with one instance of Postgres.

>
>
> --
> Thanks & Regards
> Dhaval Jaiswal
>





--
Adrian Klaver
adrian.klaver@gmail.com

Thanks for your great comments.

I have gone through suggested link & the streaming replication with 9.0+. (We are using PostgreSQL 8.4. Not to worry I will migrate it to 9.0+)

It seems to me that if i will go with this Active/Hot stand by(stream) architecture, I can generate the report from my standby database; which is in readable mode. Isn't it?

Can i take the dump from my standby database through pg_dump?

Is this standby comfortable with Big datatypes like (BLOB, CLOB, GIS, etc.) & partitioning table?


--
Thanks & Regards
Dhaval Jaiswal



-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Sun 1/16/2011 1:28 AM
To: pgsql-general@postgresql.org
Cc: Jaiswal Dhaval Sudhirkumar; John R Pierce
Subject: Re: [GENERAL] HA solution

On Saturday 15 January 2011 10:07:14 am Jaiswal Dhaval Sudhirkumar wrote:
> Thanks for your support.
>
> We have power full HP servers with lots of CPU cores, I/O bandwidth and
> memory too.
>
> Actually I will give you the environment details, which will help you to
> understand.
>
> It is a huge set-up where we have a DC & DR. There will be lots of daily
> edit and read hits. Also there would be lots of read hits from reporting
> perspective too. Therefore, I thought of to keep one node for edit and
> other one for read (OLTP vs REPORTING) kind of structure for DC & if it
> goes down DR will take care. However in your suggested structure there will
> be only one active node at a time in DC. Even standby database would be in
> recovery mode.
>
> Initially I thought of the set-up slony replication for (OLTP vs REPORTING)
> & pgpool replication for (OLTP vs OLTP) but there will be very huge GIS
> database size & they agreed only on cluster set-up.
>
> I really need thoughts/comments/help from experts.

First a data directory cannot be shared between database clusters. To put it
another away each database cluster you initdb will need to have its own data
directory.

Second with Postgres 9.0+ there is the concept of hot standby whereby the
standby cluster is available for read only operations while it is in recovery
mode. For an idea of what is possible you might to take a look at this blog:

http://blog.2ndquadrant.com/en/2011/01/easier-postgresql-90-clusters.html

Conclusion. Pulling together what has already been posted. If you want to run
two Postgres instances you will need to have two copies of the data. If you
want one copy of the data you will have to work with one instance of Postgres.

>
>
> --
> Thanks & Regards
> Dhaval Jaiswal
>





--
Adrian Klaver
adrian.klaver@gmail.com

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon,this information by persons or entities other than the intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the material from your computer. 
Microland takes all reasonable steps to ensure that its electronic communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software. 

On Sunday 16 January 2011 7:19:49 am Jaiswal Dhaval Sudhirkumar wrote:
> Thanks for your great comments.
>
> I have gone through suggested link & the streaming replication with 9.0+.
> (We are using PostgreSQL 8.4. Not to worry I will migrate it to 9.0+)
>
> It seems to me that if i will go with this Active/Hot stand by(stream)
> architecture, I can generate the report from my standby database; which is
> in readable mode. Isn't it?

Yes.

>
> Can i take the dump from my standby database through pg_dump?

See here for answer and a suggestion:
http://archives.postgresql.org/pgsql-general/2010-07/msg00767.php

>
> Is this standby comfortable with Big datatypes like (BLOB, CLOB, GIS, etc.)
> & partitioning table?

The built-in replication works at the binary level and ships whatever happens on
A to B. For a more in depth look see this Wiki page:

http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#What_Can_You_Do_With_Binary_Replication.3F
>
>
> --
> Thanks & Regards
> Dhaval Jaiswal
>



--
Adrian Klaver
adrian.klaver@gmail.com

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon,this information by persons or entities other than the intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the material from your computer. 
Microland takes all reasonable steps to ensure that its electronic communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software. 

On Monday 17 January 2011 5:43:19 am Jaiswal Dhaval Sudhirkumar wrote:
> http://wiki.postgresql.org/wiki/Streaming_Replication
>
> I have gone through the above link. Current streaming replication provides
> Asynchronous based solution & synchronous solution will come with version
> 9.1
>
> Can some one please give me an approximate idea when version 9.1 will be
> release?
>

See here to keep up on development schedule:
http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Development_Plan

FYI 9.0 was released 09/20/2010 which would give a rough date of
September/October of this year per above. Be aware that not all items in
development make it to the next release.

>
> --
>
> Thanks & Regards
> Dhaval Jaiswal
>




--
Adrian Klaver
adrian.klaver@gmail.com