Setting up High Availability Group of PostgreSQL instances using RedHat Cluster Suite

[Terry Khatri]

Hello

I need to do the subject setup using PostgreSQL, where when one node goes
down another takes over without manual intervention, the way it is done in
Oracle using grid infrastructure and in SQL Server 2012 using their
Alway-On feature

My question is, has anyone done that ? can anyone please help me implement
it, I have googled on the subject, some people have done that and
EnterpriseDB offer their services to implement it but there are no details
how actually to do it.

Your step by step guide will be much appreciated.

Also if there are any other ways to do it please let me know.

Thanks
T

[Albe Laurenz]

Post by Terry Khatri
I need to do the subject setup using PostgreSQL, where when one node

goes down another takes over

Post by Terry Khatri
without manual intervention, the way it is done in Oracle using grid

infrastructure and in SQL Server

Post by Terry Khatri
2012 using their Alway-On feature
My question is, has anyone done that ? can anyone please help me

implement it, I have googled on the

Post by Terry Khatri
subject, some people have done that and EnterpriseDB offer their

services to implement it but there

Post by Terry Khatri
are no details how actually to do it.
Your step by step guide will be much appreciated.

No step-by-step guide, but we have had such a configuration
running for years.

One gotcha I remember was that we had to modify
/usr/share/cluster/postgres.sh to wait for a while
if the regular checks got
FATAL: the database system is starting up
because otherwise you could end up with a disabled
service after a failover (+ crash recovery).

We had a 2 node cluster and had problems with it
because of the quorum disk. For unknown reasons storage
had occasional "hiccups" that caused the quorum disks to
be unavailable for short spells, which in turn caused
unnecessary failovers.

I'd recommend a three node cluster.

Yours,
Laurenz Albe

[Devrim GÜNDÜZ]

Post by Terry Khatri
I need to do the subject setup using PostgreSQL, where when one node
goes down another takes over without manual intervention, the way it
is done in Oracle using grid infrastructure and in SQL Server 2012
using their Alway-On feature

This is done using Red Hat Cluster Suite (RHCS). It is the "High
Availability Addon" in Red Hat Enterprise Linux, and I recommend you
(though not strictly required) to use GFS2 along with RHCS

Post by Terry Khatri
My question is, has anyone done that?

Yes ;)

Post by Terry Khatri
can anyone please help me implement it

http://www.gunduz.org/download.php?dlid=190

has the overview of the infrastructure. Step-by-step installation might
be too long for a mailing list, but the idea is the same as installing
anything under RHCS -- so any tutorial will help you that describes how
to configure a clustered service in RHCS. Please note that this is not
an active-active system as you wrote above, so those clusters won't
apply this. Another tip would be keeping lock file and pid in the shared
storage, not on the local disks. You will need to adjust init scripts
for that, too. There is not too much manual work btw.

Oh, that reminds me -- RHCS is currently using cman+corosync+rgmanager,
so any solutions with pacemaker will work, but won't be supported by Red
Hat (though AFAIK RHEL 7 will use pacemaker by default)

Feel free to use luci and ricci for configuring the services, they will
help you a lot. Luci is the web interface for configuring cluster, ricci
is the agent on each node.

Regards,

[Terry Khatri]

Hellow Devrim

Thank you very much for your response I will first do a test setup and the
information you have provided will help me alot so thanks again.

May I contact you my email if I get stuck somewhere ?

Regards
Terry

Post by Devrim GÜNDÜZ

Post by Terry Khatri
I need to do the subject setup using PostgreSQL, where when one node
goes down another takes over without manual intervention, the way it
is done in Oracle using grid infrastructure and in SQL Server 2012
using their Alway-On feature

This is done using Red Hat Cluster Suite (RHCS). It is the "High
Availability Addon" in Red Hat Enterprise Linux, and I recommend you
(though not strictly required) to use GFS2 along with RHCS

Post by Terry Khatri
My question is, has anyone done that?

Yes ;)

Post by Terry Khatri
can anyone please help me implement it

http://www.gunduz.org/download.php?dlid=190
has the overview of the infrastructure. Step-by-step installation might
be too long for a mailing list, but the idea is the same as installing
anything under RHCS -- so any tutorial will help you that describes how
to configure a clustered service in RHCS. Please note that this is not
an active-active system as you wrote above, so those clusters won't
apply this. Another tip would be keeping lock file and pid in the shared
storage, not on the local disks. You will need to adjust init scripts
for that, too. There is not too much manual work btw.
Oh, that reminds me -- RHCS is currently using cman+corosync+rgmanager,
so any solutions with pacemaker will work, but won't be supported by Red
Hat (though AFAIK RHEL 7 will use pacemaker by default)
Feel free to use luci and ricci for configuring the services, they will
help you a lot. Luci is the web interface for configuring cluster, ricci
is the agent on each node.
Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

[Devrim GÜNDÜZ]

Hi,

I am having an issue with postgres service on redhat cluster suite
(Rehl6), the service gets started on 2 nodes out of 3, is that the
right behaviour ?

Depends.

I would like it to be restricted to 1 node at a time becuase I am
also doing replication and when the service gets started on 2 nodes
the slaves get stuck as the masters on both nodes generate their own
wall segments.in the same folder on shared san disk on GFS2

This is a bit strange. If they are using the same $PGDATA, how can they
start at the same time?

I'd start with setting ordered=1 and restricted=0 in cluster.conf. Also
make sure that you have disabled both postgresql init scripts to start
on boot.

Let me know if these don't help.

Regards,

[Terry Khatri]

Hey Devrim

Thanks for your reply, I did some googling since my last post and finally
configured the pg cluster with RedHat the way I wanted it works so well and
we amazed, the link that you sent to your guide helped a lot too.

Again thank you very much for your assistance.
Regards
Terry

Post by Devrim GÜNDÜZ

I am having an issue with postgres service on redhat cluster suite
(Rehl6), the service gets started on 2 nodes out of 3, is that the
right behaviour ?

Depends.

I would like it to be restricted to 1 node at a time becuase I am
also doing replication and when the service gets started on 2 nodes
the slaves get stuck as the masters on both nodes generate their own
wall segments.in the same folder on shared san disk on GFS2

This is a bit strange. If they are using the same $PGDATA, how can they
start at the same time?
I'd start with setting ordered=1 and restricted=0 in cluster.conf. Also
make sure that you have disabled both postgresql init scripts to start
on boot.
Let me know if these don't help.
Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

[Terry Khatri]

Hey Devrim

Thanks for your reply, I did some googling since my last post and finally
configured the pg cluster with RedHat the way I wanted it works so well and
we amazed, the link that you sent to your guide helped a lot too.

Again thank you very much for your assistance.
Regards
Terry

Post by Devrim GÜNDÜZ

I am having an issue with postgres service on redhat cluster suite
(Rehl6), the service gets started on 2 nodes out of 3, is that the
right behaviour ?

Depends.

I would like it to be restricted to 1 node at a time becuase I am
also doing replication and when the service gets started on 2 nodes
the slaves get stuck as the masters on both nodes generate their own
wall segments.in the same folder on shared san disk on GFS2

This is a bit strange. If they are using the same $PGDATA, how can they
start at the same time?
I'd start with setting ordered=1 and restricted=0 in cluster.conf. Also
make sure that you have disabled both postgresql init scripts to start
on boot.
Let me know if these don't help.
Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz