[pgpool-general: 393] Re: kind mismatch among backends with load balance off

[Tatsuo Ishii]

石井です。



> Hello,
>     I have faced with a problem running pgpool-II version 3.1 and two
>     postgresql servers 9.1.2 running streaming replication with hot
>     standby.
>     Pgpool configuration:
>     backend_weight0 = 1
>     backend_weight1 = 0
>     load_balance_mode = off
>     replication_mode = off
>     master_slave_mode = on
>     master_slave_sub_mode = 'stream'
> 
>     Postgres specific configuration:
>     max_standby_archive_delay = 1200s
>     max_standby_streaming_delay = 1200s
>     (this parameters were configured because of long running queries)
> 
>     Our backend 1 server works only for HA and night backup process. We
>     are making backup of standby server every night using 2 steps rsync,
>     first step with running db, second step disconnects database from
>     pgpool (with pcp_detach), stops database, runs second rsync, starts db
>     and attaches it (after 1440s timeout) to pgpool. What is specific in
>     this configuration is, that second stage rsync needs about 4 hours to
>     complete, this why after database startup difference between master
>     and standby is about 2 GB (using our network speed we had calculated
>     that we need about 1440s of timeout after db startup until connecting
>     it to pgpool).
>      After all processes are done, database is connected to pool. But on
>      pgpool we are getting errors like:
> 
>  2012-04-26 12:10:40 ERROR: pid 29814: read_kind_from_backend: 1 th
>  kind D does not match with master or majority connection kind C
> 2012-04-26 12:10:40 ERROR: pid 29814: kind mismatch among
> backends. Possible last query was: "SET LOCAL TIMEZONE TO
> 'RUP-2:00DST,M3.5.0/3:00,M10.5.0/4:00';
>                 SELECT
>                     *
>                 FROM data WHERE datetime between 2012-04-25 00:00:00' AT TIME ZONE
>                 'GMT' and '2012-04-26 23:59:59' AT TIME ZONE 'GMT'" kind details are:
>                 0[C] 1[D];
> 
> 
> The strangest think is, that after some time (2 or maybe 3 hours)
> exactly the same query is running good without any problems.. 

Probably replication delay?

> Also how
> could be "kind mismatch error" when load balance is off?

You use "multi statement query". Pgpool-I does not support it as
stated in docs. Pgpool thinks that whole query is SET, and sends it to
primary and standby.

> What could be the problem? Maybe postgres configuration issue? Or
> something else? What additiona information could I provide to solve
> this problem?

The solution would be not to use multi statement queries.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp