Thread: Setting up HA postgresql

On 07/21/2015 08:34 AM, William Dunn wrote:
> Hello Aviel,
>
> On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila <aviel33@gmail.com
> <mailto:aviel33@gmail.com>> wrote:
>
>     How can I set a highly available postgresql in a share-nothing
>     architecture?
>
> I suggest you review the official documentation on high-availability
> configurations linked below:
> http://www.postgresql.org/docs/current/static/high-availability.html
>
> The most common configuration is to use PostgreSQL's built in
> master/standby streaming replication. However you will need to develop
> any fail-over logic you need yourself or use a third party tool such as
> EnterpriseDB's Failover Manager.

Or use already available open source tools such as Pgpool-II or Linux-HA.

Sincerely,
JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

On 07/21/2015 11:04 AM, William Dunn wrote:
> If you dig deeper into pgpool-II you will find that it does not have
> failover logic. Its intention is to pool connections and distribute
> query load among replicas, but it cannot differentiate node failure from
> network partition and cannot promote a standby to master in the case of
> failure.

If you dig deeper, I think you would find your statement is no longer
accurate:

http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

On 07/21/2015 01:21 PM, William Dunn wrote:
> That's pretty cool! But the intended use of watchdog is so you can have
> multiple pgpool-II instances and failover among them
> (http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog) rather than
> failure of Postgres. In the configuration described in that wiki what
> happens when the DBMS goes down but pgpool-II is fine? The watchdog
> appears to be monitoring the pgpool-II process, not the
> postgres/postmaster process.

 From what I see, it has the ability to execute a command/script based
on a failed condition which could include but not be limited to a
command to fail over a standby.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

On 07/21/2015 01:37 PM, William Dunn wrote:
> But it appears that the fail condition for "watchdog" is the failure of
> a pgpool-II instance. In the configuration described in the wiki you
> would put a pgpool-II instance on each Postgres node, and if one of the
> pgpool-II instances fails it executes a script (which can create the
> trigger file to promote the standby to master). However, if the fail
> condition for watchdog is a failure of the pgpool-II instance what
> happens if the DBMS has availability issues but the pgpool-II process on
> that server is ok? The fail condition would never be triggered, right?

What I am going off of is:

"When backend node status changes by failover etc., watchdog notifies
the information to other pgpool-IIs and synchronizes them. When online
recovery occurs, watchdog restricts client connections to other
pgpool-IIs for avoiding inconsistency between backends."

A backend node status would change if say the master went down, at that
point pgpool can be instructed either automatically or manually to
failover (depending on configuration) and allow the slave to failover.

Sincerely,

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

On 07/21/2015 02:48 PM, William Dunn wrote:
> Maybe Linux-HA which you recommended is the more promising option for
> open source tool.
> http://www.linux-ha.org/wiki/Main_Page
>
> The Postgres resource agent appears to monitor the instance by executing
> 'SELECT now();' which is typically the recommended check. Though, I do
> not know Linux-HA well enough to know of any limitations or whether it
> should be recommend
> http://linux-ha.org/doc/man-pages/re-ra-pgsql.html

CMD has deployed many HA clusters using Linux-HA.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

> But it appears that the fail condition for "watchdog" is the failure of a
> pgpool-II instance. In the configuration described in the wiki you would
> put a pgpool-II instance on each Postgres node, and if one of the pgpool-II
> instances fails it executes a script (which can create the trigger file to
> promote the standby to master). However, if the fail condition for watchdog
> is a failure of the pgpool-II instance what happens if the DBMS has
> availability issues but the pgpool-II process on that server is ok? The
> fail condition would never be triggered, right?

No. The condition (PostgreSQL failure) will be detected by one of
pgpool-II (not watchdog process) and the pgpool-II will disconnect the
failed PostgreSQL, then execute failover script (if defined) which
will usually promote standby PostgreSQL (if failed PostgreSQL was
primary) or just ignore the broken PostgreSQL if it was standby.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> *Will J. Dunn*
> *willjdunn.com <http://willjdunn.com>*
>
> On Tue, Jul 21, 2015 at 4:27 PM, Joshua D. Drake <jd@commandprompt.com>
> wrote:
>
>>
>> On 07/21/2015 01:21 PM, William Dunn wrote:
>>
>>> That's pretty cool! But the intended use of watchdog is so you can have
>>> multiple pgpool-II instances and failover among them
>>> (http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog) rather than
>>> failure of Postgres. In the configuration described in that wiki what
>>> happens when the DBMS goes down but pgpool-II is fine? The watchdog
>>> appears to be monitoring the pgpool-II process, not the
>>> postgres/postmaster process.
>>>
>>
>> From what I see, it has the ability to execute a command/script based on a
>> failed condition which could include but not be limited to a command to
>> fail over a standby.
>>
>>
>> JD
>>
>> --
>> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
>> PostgreSQL Centered full stack support, consulting and development.
>> Announcing "I'm offended" is basically telling the world you can't
>> control your own emotions, so everyone else should do it for you.
>>

On 07/21/2015 07:07 PM, Tatsuo Ishii wrote:
>
>> But it appears that the fail condition for "watchdog" is the failure of a
>> pgpool-II instance. In the configuration described in the wiki you would
>> put a pgpool-II instance on each Postgres node, and if one of the pgpool-II
>> instances fails it executes a script (which can create the trigger file to
>> promote the standby to master). However, if the fail condition for watchdog
>> is a failure of the pgpool-II instance what happens if the DBMS has
>> availability issues but the pgpool-II process on that server is ok? The
>> fail condition would never be triggered, right?
>
> No. The condition (PostgreSQL failure) will be detected by one of
> pgpool-II (not watchdog process) and the pgpool-II will disconnect the
> failed PostgreSQL, then execute failover script (if defined) which
> will usually promote standby PostgreSQL (if failed PostgreSQL was
> primary) or just ignore the broken PostgreSQL if it was standby.

Thank you Tatsuo, I was having trouble explaining.

Sincerely,

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.