[pgpool-general: 2684] Re: Restart Postgresql service
Tatsuo Ishii
ishii at postgresql.org
Wed Apr 2 07:58:50 JST 2014
I think you need to modify the pgpool source to get the desired
behavior.
around line 4938 of pool_process_query.c:
/*
* admin shutdown postmaster or postmaster goes down
*/
r = detect_postmaster_down_error(CONNECTION(backend, i), MAJOR(backend));
if (r == SPECIFIED_ERROR)
{
pool_log("postmaster on DB node %d was shutdown by administrative command", i);
/* detach backend node. */
was_error = 1;
if (!VALID_BACKEND(i))
break;
notice_backend_error(i);
sleep(5);
break;
}
else if (r < 0)
{
/*
* This could happen after detecting backend errors and before actually
* detaching the backend. In this case reading from backend socket will
* return EOF and it's better to close this session. So returns POOL_END.
*/
pool_log("detect_postmaster_down_error returns error on backend %d. Going to close this session.", i);
return POOL_END;
}
By removing those lines, you could get the desired behavior though I
have not tested it.
In the long term, we should be able to enable/disable failover when
PostgreSQL is shutdwon.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> Any suggestions?
>
> I forgot to mention that I'm using streaming replication.
>
> The thing is some PostgreSQL configuration parameters (like max_connections or shared_buffers) apply only on server start. Therefore if I need to change them I have to restart PostgreSQL backends.
>
> But if my application is writing or reading from the database at the moment the restart almost every time makes Pgpool consider the restarted backend as faulty.
> If it happens to the slave node I can always attach it again with pcp_attach_node almost instantly. But if I restart the master Pgpool promotes the slave to master and then I need to run pcp_recovery_node which will cause to resync the new slave with the new master. And this takes some time and is very inconvenient.
>
> I have the following health_check parameters at the moment:
>
> health_check_period = 12
> health_check_timeout = 20
> health_check_max_retries = 16
> health_check_retry_delay = 2
>
> And also restart backends with:
>
> sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ restart -m fast
>
> But the behaviour remains the same.
>
> I can stop Pgpool while restarting Postgresql backends and then start it again. In this case everything will be ok. But it is very inconvenient as well.
>
>
>
>
>
>
>
> On Mar 27, 2014, at 11:21 PM, Sergey Arlashin <sergeyarl.maillist at gmail.com> wrote:
>
>> I tried different health check settings and used
>>
>> sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ restart -m fast
>>
>> to restart postgresl, but still each time I restart one of postgresql backends I get something like this in pgpool.log:
>>
>> Mar 27 19:13:35 lb-node1 pgpool[23886]: ProcessFrontendResponse: failed to read kind from frontend. frontend abnormally exited
>> Mar 27 19:13:35 lb-node1 pgpool[13960]: postmaster on DB node 1 was shutdown by administrative command
>> Mar 27 19:13:35 lb-node1 pgpool[32459]: postmaster on DB node 1 was shutdown by administrative command
>> Mar 27 19:13:35 lb-node1 pgpool[13960]: degenerate_backend_set: 1 fail over request from pid 13960
>> Mar 27 19:13:35 lb-node1 pgpool[32146]: postmaster on DB node 1 was shutdown by administrative command
>> Mar 27 19:13:35 lb-node1 pgpool[32459]: degenerate_backend_set: 1 fail over request from pid 32459
>> Mar 27 19:13:35 lb-node1 pgpool[32146]: degenerate_backend_set: 1 fail over request from pid 32146
>> Mar 27 19:13:35 lb-node1 pgpool[32187]: starting degeneration. shutdown host db-node2.site(5432)
>> Mar 27 19:13:35 lb-node1 pgpool[32187]: Restart all children
>> Mar 27 19:13:35 lb-node1 pgpool[4684]: postmaster on DB node 1 was shutdown by administrative command
>> Mar 27 19:13:35 lb-node1 pgpool[32187]: execute command: /etc/pgpool2/scripts/failover.sh 1 1 db-node1.site /var/lib/postgresql/9.3/main/switch_master
>> Mar 27 19:13:40 lb-node1 pgpool[32187]: find_primary_node_repeatedly: waiting for finding a primary node
>> Mar 27 19:13:44 lb-node1 pgpool[32187]: find_primary_node: primary node id is 0
>> Mar 27 19:13:44 lb-node1 pgpool[32187]: failover: set new primary node: 0
>> Mar 27 19:13:44 lb-node1 pgpool[32187]: failover: set new master node: 0
>> Mar 27 19:13:44 lb-node1 pgpool[4684]: degenerate_backend_set: 1 fail over request from pid 4684
>> Mar 27 19:13:44 lb-node1 pgpool[32295]: worker process received restart request
>> Mar 27 19:13:44 lb-node1 pgpool[32187]: failover done. shutdown host db-node2.site(5432)
>> Mar 27 19:13:45 lb-node1 pgpool[32289]: pcp child process received restart request
>> Mar 27 19:13:45 lb-node1 pgpool[32187]: PCP child 32289 exits with status 256 in failover()
>> Mar 27 19:13:45 lb-node1 pgpool[32187]: fork a new PCP child pid 15547 in failover()
>> Mar 27 19:13:45 lb-node1 pgpool[32187]: worker child 32295 exits with status 256
>> Mar 27 19:13:46 lb-node1 pgpool[32187]: fork a new worker child pid 15548
>> Mar 27 19:13:46 lb-node1 pgpool[32187]: failover: no backends are degenerated
>>
>>
>>
>>
>>
>> On Mar 27, 2014, at 11:46 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>
>>>> Hi!
>>>> Almost every time I restart backend postgresql service ( while pgpool is working ) it causes pgpool to mark this node as failed.
>>>> So I'm wondering if there is some 'proper' way to restart postgresql backends ( without stopping pgpool service ) which doesn't break the cluster.
>>>
>>> You can tweak health check parameters. For example,
>>>
>>> health_check_max_retries = 10
>>> health_check_retry_delay = 1
>>>
>>> Also make sure that you restart PostgreSQL with "fast" mode.
>>>
>>> Best regards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese: http://www.sraoss.co.jp
>>
>
More information about the pgpool-general
mailing list