[pgpool-general: 6150] Re: What happens when node is assigned as shutdown

Mariel Cherkassky mariel.cherkassky at gmail.com
Wed Jun 27 18:55:30 JST 2018

The problem is that after 2 minutes I connected and attached the node and
it worked like magic. I I checked the health_check settings in my pool and
I guess that it worked immediatly because the health check parameters are
default in my pool :
health_check_period0 = 0
health_check_timeout0 = 20
health_check_user0 = 'nobody'
health_check_password0 = ''
health_check_database0 = ''
health_check_max_retries0 = 5
health_check_retry_delay0 = 20
connect_timeout0 = 10000
health_check_max_retries = 0


health_check_period = 0
                                   # Health check period
                                   # Disabled (0) by default
health_check_timeout = 20
                                   # Health check timeout
                                   # 0 means no timeout
health_check_user = 'nobody'
                                   # Health check user
health_check_password = ''
                                   # Password for health check user
health_check_database = ''
                                   # Database name for health check. If '',
tries 'postgres' frist, then 'template1'

health_check_max_retries = 0
                                   # Maximum number of times to retry a
failed health check before giving up.
health_check_retry_delay = 1
                                   # Amount of time to wait (in seconds)
between retries.
connect_timeout = 10000
                                   # Timeout value in milliseconds before
giving up to connect to backend.
Default is 10000 ms (10 second). Flaky network user may want to increase
                                                                   # the
value. 0 means no timeout.
                                                                   # Note
that this value is not only used for health check,
                                                                   # but
also for ordinary conection to backend.

So, I guess I should change those defaults right ? Even if I dont want the
pool to be responsible on the failover of the primary and the replication.

Just to understand your last paragraph, In case my first standby database
id down pgpool will destroy all the connections to him(and all the queries
that run at that moment will be destroyed) and new sessions will be created
to the second node or to the primary. After the failover the pool wont run
those queries right ?

2018-06-26 22:20 GMT+03:00 Pierre Timmermans <ptim007 at yahoo.com>:

> Mariel
> I understand what you mean, but don't forget that the connections to the
> postgres database are established by pgpool, so the applications are not
> connected directly to postgres : the application connects to pgpool and
> pgpool connects to postgres. Therefore if pgpool cannot see the database
> anymore, because of a network failure, then all connections are stucked
> (even if postgres DB is still fine).
> So when pgpool cannot see the database anymore (health check), it will try
> a few time (health_check_max_retries) and then it will do the failover :
> during the failover all backend connections are destroyed and then
> re-created (exception is when the standby fails, then backend connections
> to the primary are not affected, even if load_balance is on, as Tatsuo
> confirmed in other mail)
> So it is not the same situation as in Oracle with the listener, in the
> case of the oracle listener, the listener process just establish the
> connection and then it hand it over to the client application and from this
> point the listener is not needed anymore for this process. The advantage of
> pgpool is that it can re-establish the connections transparently when a
> fail-over happens. it is not completely transparent because when the master
> is is failed-over all connections are first disconnected and then
> re-connected but it is impossible to avoid that I guess.
> Pierre
> On Tuesday, June 26, 2018, 4:53:05 PM GMT+2, Mariel Cherkassky <
> mariel.cherkassky at gmail.com> wrote:
> But what if my standby didnt fail ? I mean, in my case I had a network
> conflict between the standby and the pool. The standby was up and was
> working. In that case stil the session will be disconneced ? And why only
> in case the load_balance_mode is off the seesion will not be disrupted ?
> Isnt it suppose to be the the opposite ? I have 2 standbys and In case one
> of my standby`s is down or doesnt work all the current sessions to him will
> be moved to the second standby.
> Mariel.
> 2018-06-26 17:20 GMT+03:00 Pierre Timmermans <ptim007 at yahoo.com>:
> Note that if standby node fails and if load_balance_node is off, then the
> pgpool connection will not be disrupted by the failover happening on the
> standby (at least with recent version of pgpool): this is documented in the
> doc and I can confirm that it is working in my tests.
> In all other cases the backend connection will be disconnected and I
> suppose the client will receive an error and the query result will be lost
> Pierre
> On Tuesday, June 26, 2018, 3:35:41 PM GMT+2, Mariel Cherkassky <
> mariel.cherkassky at gmail.com> wrote:
> The moment the pool assign the shutdown status to a specific node all
> active session to that node are closed immediatly ?
> 2018-06-26 16:11 GMT+03:00 Tatsuo Ishii <ishii at sraoss.co.jp>:
> > Hi,
> > I wanted to understand what happens to a session that was opened from the
> > pool to   one of the nodes in my cluster (query that is taking x time).
> > Suddenly there was a network conflict between the pool node and the
> > database node and the pool change the status of the database node to
> > shutdown(the node is slave). What happens to the query that runned ? What
> > happens to the session that was open ?
> The session will be closed. This is pretty much similar when you
> directly connect to PostgreSQL.
> > Will the output of the query will
> > come back to the user ?
> Though it really depends on the timing of failure, probably the output
> of the query will not come bacl to the user in most cases.
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_ en.php
> <http://www.sraoss.co.jp/index_en.php>
> Japanese:http://www.sraoss.co. jp <http://www.sraoss.co.jp>
> ______________________________ _________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/ listinfo/pgpool-general
> <http://www.pgpool.net/mailman/listinfo/pgpool-general>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180627/562fcd5e/attachment.html>

More information about the pgpool-general mailing list