[pgpool-general: 2683] Re: Restart Postgresql service

[Sergey Arlashin]

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
>