[pgpool-general: 3575] Re: Out of sync with actual db state (replication / load balancing mode)

[Gervais de Montbrun]

Hi,

Thank you for your reply.

I was concerned about ignoring the pgpool_status file at startup. When pgpool starts, it check to see if the backend nodes are running, but does not communicate with the other pgpool server to check the state of the backends. It is possible for postgresql server to be running and still be in a detached state. I changed the failover and failback commands to include shutting down the postgresql-server on the backend that is being detached and attached. This seems to be working fine now and I think that I have my setup properly configured for all edge cases.

One thing that I noticed is that the result of the show pool_nodes command seems to be incorrect.
	psql -Upostgres -hcara0 -c "show pool_nodes”

	 node_id | hostname | port  | status | lb_weight | role
	---------+----------+-------+--------+-----------+————
	 0       | cara0    | 54321 | 2      | 0.500000  | master
	 1       | cara1    | 54321 | 2      | 0.500000  | slave
	(2 rows) 


	pcp_watchdog_info 10 cara0 9898 postgres <password> 
	cara0 5432 9000 2

	pcp_watchdog_info 10 cara0 9898 postgres <password> 0
	cara1 5432 9000 3

We can see that the server called cara1 is in fact the active node. I’m not concerned about this if it is just a reporting issue and is safe to ignore, but thought that I should ask. 

Cheers,
Gervais

> On Mar 27, 2015, at 6:03 AM, Yugo Nagata <nagata at sraoss.co.jp> wrote:
> 
> Hi Gervais,
> 
> On Thu, 26 Mar 2015 11:39:40 -0300
> Gervais de Montbrun <gervais at silverorange.com> wrote:
> 
>> I have two servers running with replication, load balancing, watchdog (with virtual IP) enabled.
>> 
>> My plan is to remove any single point of failure from my database. The web sites I am running would point to the virtual interface’s IP on whatever is running as a primary in the pgpool and there would be two database servers behind the pool. Here is my setup as it stands:
>> 
>> dbserver0:
>> 	pgpool - get’s virtual IP if it starts first
>> 	postgres
>> 
>> dbserver1:
>> 	pgpool
>> 	postgres 
>> 
>> I have everything working except that the there can be a situation where one of the pgpools starts up with a stale pgpool_staus file and it thinks a backend node is either up when it is down (or down when it is up). I am not sure how to sort this out as this file seems to only be written to when pgpool stops.
> 
> When pgpool starts up with -D option, an old pgpool_status is discarded
> and pgpool recognizes the current status of backends.
> If you want to modify backend status while pgpool is runing,
> you can use pcp_attach_node or pcp_detach_node command.
> 
>> 
>> Can someone suggest how to sort this out. I know that you will likely need to review any number of config files and/or scripts in my configuration. Can you let me know what you would want to see before I sanitize them all and send them on.
>> 
>> Also, if my plan is completely off, I am open to any alternative suggestion as to how to best achieve my goal as described above.
>> 
>> Cheers,
>> Gervais
>> ------------------------------------
>> Gervais de Montbrun
>> Systems Administrator
>> GTalk: gervais at silverorange.com
>> 
> 
> 
> -- 
> Yugo Nagata <nagata at sraoss.co.jp>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150330/e03a7272/attachment.html>