[pgpool-general: 393] Re: kind mismatch among backends with load balance off
Tatsuo Ishii
ishii at postgresql.org
Fri Apr 27 00:31:04 JST 2012
石井です。
> 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
More information about the pgpool-general
mailing list