Setting up HA postgresql

Hello Aviel,

On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila <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.

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila <aviel33@gmail.com> wrote:

hey will,
Is there any open-source tool instead of developing the fail-over logic by
myself?

2015-07-21 18:34 GMT+03:00 William Dunn <dunnwjr@gmail.com>:

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.

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

Can you link me up to a good tutorial using pgpool-II?

2015-07-21 20:02 GMT+03:00 Joshua D. Drake <jd@commandprompt.com>:

Hi,

I made the following document 4 years back:
http://www.rummandba.com/2011/02/postgresql-failover-with-pgpool-ii.html

You may have a look if it makes any good to your work.

BTW, if you want to setup a share-nothing high scalable system with
data-sharding, you can go for pl/proxy.

Thanks.

On Tue, Jul 21, 2015 at 10:55 AM, Aviel Buskila <aviel33@gmail.com> 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.

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jul 21, 2015 at 1:55 PM, Aviel Buskila <aviel33@gmail.com> wrote:

Implementing failover logic is quite simple, and probably even preferred
over a pre-built solution because you can implement it in the way that
integrates well with your architecture.

The basic logic is as follows:

- On the standby server you would configure the "trigger" file. When you
create a trigger file with the name you specify the standby will promote to
master.
- You have your queries send to the master.
- If your application starts failing to connect to the master you would
have logic evaluate why
- When the master goes down you should
- Generate a trigger file to promote the standby to master
- Modify your DNS entries to have your applications point to the new
master

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jul 21, 2015 at 12:20 PM, Aviel Buskila <aviel33@gmail.com> wrote:

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.

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

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.

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jul 21, 2015 at 3:31 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.

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

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?

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jul 21, 2015 at 4:27 PM, Joshua D. Drake <jd@commandprompt.com>
wrote:

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.

--
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, Jul 21, 2015 at 5:12 PM, Joshua D. Drake <jd@commandprompt.com>
wrote:

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

You're taking that out of context, though. That is coming from the doc
http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog, which is
describing how Watchdog coordinates fault of *pgpool-II* backend processes
and coordinates the pgpool-II instances with each other. It is for
maintaining availability of pgpool-II and monitoring for failure of
pgpool-II backends, not Postgres/postmaster.

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jul 21, 2015 at 5:12 PM, Joshua D. Drake <jd@commandprompt.com>
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

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jul 21, 2015 at 5:35 PM, William Dunn <dunnwjr@gmail.com> wrote:

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.

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

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&gt;*

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.

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

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.

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

Thanks so much for lending your expertise Tatsuo.

Aviel I have found and linked below the relevant section of the official
pgpool-II docs on using pgpool-II for failover. It is available starting in
pgpool-II version 3.1:
http://www.pgpool.net/docs/latest/pgpool-en.html#stream

*Will J. Dunn*

On Tue, Jul 21, 2015 at 10:07 PM, Tatsuo Ishii <ishii@postgresql.org> wrote: