[pgpool-general: 5089] Re: Pgpool: create database is not replicated

[Tatsuo Ishii]

Status = 3 means PostgreSQL down, Pgpool-II cannot reach to it, or
PostgreSQL denies to connect because of some issues (authentication
fails, not enough max_connections). Sop and start Pgpool-II to see
what the Pgpool-II log says.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi Ishii,
> 
> I can connect perfectly with
> psql -p 9999 -hvdiccs01 postgres
> 
> postgres=# SHOW pool_nodes;
> 2016-10-30 04:25:01: pid 23968: DEBUG:  processing frontend response
> 2016-10-30 04:25:01: pid 23968: DETAIL:  received kind 'Q'(51) from frontend
> 2016-10-30 04:25:01: pid 23968: DEBUG:  session context: clearing doing
> extended query messaging. DONE
> 2016-10-30 04:25:01: pid 23968: DEBUG:  session context: setting query in
> progress. DONE
> 2016-10-30 04:25:01: pid 23968: DEBUG:  SimpleQuery
> 2016-10-30 04:25:01: pid 23968: DETAIL:  nodes reporting
> 2016-10-30 04:25:01: pid 23968: DEBUG:  session context: unsetting query in
> progress. DONE
> 2016-10-30 04:25:01: pid 23968: DEBUG:  session context: setting skip
> reading from backends. DONE
>  node_id | hostname | port | status | lb_weight |  role  | select_cnt
> ---------+----------+------+--------+-----------+--------+------------
>  0       | vdiccs01 | 5432 | 2      | 0.333333  | master | 1
>  1       | vdiccs02 | 5432 | 3      | 0.333333  | slave  | 0
>  2       | vdiccs03 | 5432 | 3      | 0.333333  | slave  | 0
> (3 rows)
> 
> pgpool version:
> 
> pgpool-II-96.x86_64
>        3.5.4-1.rhel7
>  @pgdg96
> 
> Related to logs,
> 
> bench_replication=# insert into t1 values (4);
> 2016-10-30 04:33:47: pid 23957: DEBUG:  processing frontend response
> 2016-10-30 04:33:47: pid 23957: DETAIL:  received kind 'Q'(51) from frontend
> 2016-10-30 04:33:47: pid 23957: DEBUG:  session context: clearing doing
> extended query messaging. DONE
> 2016-10-30 04:33:47: pid 23957: DEBUG:  session context: setting query in
> progress. DONE
> 2016-10-30 04:33:47: pid 23957: DEBUG:  make table name from rangevar:
> tablename:""t1""
> 2016-10-30 04:33:47: pid 23957: DEBUG:  pool_write: to backend: 0 kind:Q
> 2016-10-30 04:33:47: pid 23957: DEBUG:  waiting for query response
> 2016-10-30 04:33:47: pid 23957: DETAIL:  waiting for backend:0 to complete
> the query
> 2016-10-30 04:33:47: pid 23957: DEBUG:  pool_read: read 22 bytes from
> backend 0
> 2016-10-30 04:33:47: pid 23957: DEBUG:  detect error: kind: C
> 2016-10-30 04:33:47: pid 23957: DEBUG:  detect error: kind: C
> 2016-10-30 04:33:47: pid 23957: DEBUG:  detect error: kind: C
> 2016-10-30 04:33:47: pid 23957: DEBUG:  detect error: kind: C
> 2016-10-30 04:33:47: pid 23957: DEBUG:  pool_use_sync_map: we cannot use
> sync map because STREAM: 0 query in progress: 1 doing extended query: 0
> 2016-10-30 04:33:47: pid 23957: DEBUG:  reading backend data packet kind
> 2016-10-30 04:33:47: pid 23957: DETAIL:  backend:0 of 3 kind = 'C'
> 2016-10-30 04:33:47: pid 23957: DEBUG:  read_kind_from_backend
> max_count:1.000000 num_executed_nodes:1
> 2016-10-30 04:33:47: pid 23957: DEBUG:  processing backend response
> 2016-10-30 04:33:47: pid 23957: DETAIL:  received kind 'C'(43) from backend
> 2016-10-30 04:33:47: pid 23957: DEBUG:  session context: setting command
> success. DONE
> 2016-10-30 04:33:47: pid 23957: DEBUG:  pool_use_sync_map: we cannot use
> sync map because STREAM: 0 query in progress: 1 doing extended query: 0
> 2016-10-30 04:33:47: pid 23957: DEBUG:  reading backend data packet kind
> 2016-10-30 04:33:47: pid 23957: DETAIL:  backend:0 of 3 kind = 'Z'
> 2016-10-30 04:33:47: pid 23957: DEBUG:  read_kind_from_backend
> max_count:1.000000 num_executed_nodes:1
> 2016-10-30 04:33:47: pid 23957: DEBUG:  processing backend response
> 2016-10-30 04:33:47: pid 23957: DETAIL:  received kind 'Z'(5a) from backend
> 2016-10-30 04:33:47: pid 23957: DEBUG:  processing backend response
> 2016-10-30 04:33:47: pid 23957: DETAIL:  Ready For Query received
> 2016-10-30 04:33:47: pid 23957: DEBUG:  session context: clearing ignore
> till sync. DONE
> 2016-10-30 04:33:47: pid 23957: DEBUG:  pool_use_sync_map: we cannot use
> sync map because STREAM: 0 query in progress: 1 doing extended query: 0
> 2016-10-30 04:33:47: pid 23957: DEBUG:  pool_use_sync_map: we cannot use
> sync map because STREAM: 0 query in progress: 1 doing extended query: 0
> 2016-10-30 04:33:47: pid 23957: DEBUG:  pool_use_sync_map: we cannot use
> sync map because STREAM: 0 query in progress: 1 doing extended query: 0
> 2016-10-30 04:33:47: pid 23957: DEBUG:  reading message length
> 2016-10-30 04:33:47: pid 23957: DETAIL:  slot: 0 length: 5
> 2016-10-30 04:33:47: pid 23957: DEBUG:  processing ReadyForQuery
> 2016-10-30 04:33:47: pid 23957: DETAIL:  transaction state 'I'(49)
> 2016-10-30 04:33:47: pid 23957: DEBUG:  session context: unsetting query in
> progress. DONE
> 2016-10-30 04:33:47: pid 23957: DEBUG:  session context: unsetting query in
> progress. DONE
> INSERT 0 1
> 
> Thanks a lot.
> 
> 
> 2016-10-30 23:30 GMT+01:00 Tatsuo Ishii <ishii at sraoss.co.jp>:
> 
>> > Hi,
>> >
>> > In my environment I have 3 nodes (vdiccs01, vdiccs02 and vdiccs03). I
>> have
>> > installed pgpool in all nodes for testing purposes but I'm planning to
>> use
>> > just the pgpool daemon runing on vdicss01.
>> >
>> > I'm following step by step the tutorial that is available on the web:
>> >
>> > http://www.pgpool.net/docs/latest/tutorial-en.html#replication
>> >
>> > In the step:
>> >
>> > createdb -p 9999 -hvdiccs01 bench_replication
>> >
>> > I have expected that the database creation to be executed in all nodes
>> > because I have set to true the replication_mode = true nevertheless, the
>> > bench_replication is only created in node vdicss01.
>> >
>> > I'm very new and I supose I'm not understanding perfectly the correct way
>> > of working of pgpool.
>> >
>> > Any help will be welcome to understand what's happening.
>> >
>> > Thanks a lot.
>>
>> psql -p 9999 -hvdiccs01 postgres
>>
>> will show what are the status of vdiccs01, vdiccs02 and vdiccs03
>> recognized by Pgpool-II. I guess Pgpool-II does not think vdiccs02 and
>> vdiccs03 are online. Also you'd better tell what version of Pgpool-II
>> you are using along with pgool.conf and Pgpool-II log.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>