[Pgpool-general] Confusing... pgpool starts on both nodes, but don't replicate.

[» Ruben Alves]

Thanks to all. Now I understand better how pgpool works.
Now I've been able to made some successful test. Replication Mode is 
working perfectly.
The problem now is a little bit diferrent... I've just made a backup 
from the postgres production database, and freshly imported in two new 
databases in docsys and docsys2.
The problem is when I try any kind of connection to pgpool (in 9999 
port) I have the "kind mismatch error".

ex:
docsys:/home/rubenalves# /usr/lib/postgresql/8.3/bin/pgbench -i -h 
docsys -p 9999 imm_dbv12 -U postgres
ERROR:  kind mismatch among backends. Possible last query was: "SET 
search_path = public" kind details are: 0[C] 1[N]
HINT:  check data consistency among db nodes
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
docsys:/home/rubenalves#

I really can't understand because the both databases are IDENTICAL, how 
pgpool may find some differences?

Someone got an idea about this error?


//Ruben

J. Carlos Muro wrote:
> As Gerd already pointed, you need to activate just one pgpool, then 
> connect to it from your sql client. (I leave here the question for 
> experts: what happens if I set up two or more pgpool all of them 
> accessing to the same backends at the same time?)
>
> As a guideline to find the problem to your setup, you can enable debug 
> mode and try to find kind of these lines in your logs:
>
> [....]
> 2009-06-30 16:29:15 DEBUG: pid 11297: backend 0 weight: 1073741823.500000
> 2009-06-30 16:29:15 DEBUG: pid 11297: backend 1 weight: 1073741823.500000
> [....]
> 2009-06-30 16:29:15 LOG:   pid 11297: pgpool successfully started
>
> From then an on, when you try to connect pgpool in order to execute 
> queries you can check netstat as to see if pgpool child opened 
> connection to both of your backends:
>
> $ sudo netstat -puta | grep pool
> [....]
> tcp        0      0 pgpoolserver:51190          
> backend0:postgresql     ESTABLISHED 11319/pgpool
> tcp        0      0 pgpoolserver:36816          
> backend1:postgresql     ESTABLISHED 11319/pgpool
>
> Also in your logs you should find something like this:
>
> 2009-06-30 16:30:47 DEBUG: pid 11319: new_connection: connecting 0 backend
> 2009-06-30 16:30:47 DEBUG: pid 11319: new_connection: connecting 1 backend
> [....]
> 2009-06-30 16:30:47 DEBUG: pid 11319: select_load_balancing_node: 
> selected backend id is 0
> 2009-06-30 16:30:47 DEBUG: pid 11319: read_kind_from_backend: read 
> kind from 0 th backend Z NUM_BACKENDS: 2
> 2009-06-30 16:30:47 DEBUG: pid 11319: read_kind_from_backend: read 
> kind from 1 th backend Z NUM_BACKENDS: 2
>
> Hope it helps!
> J. Carlos Muro
>
> 2009/7/2 » Ruben Alves <rubenalves at fm.ul.pt <mailto:rubenalves at fm.ul.pt>>
>
>     Hi everyone,
>     I installed pgpool with postgreSQL on Monday. I've been busy since
>     yesterday morning, and in the afternoon I've tried to configure
>     following those instructions:
>
>       *
>     http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#start
>       *
>     http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#replication
>
>     Everything is running fine, pgptool is running, listening for news
>     connections etc.. But NOTHING happens. If I change something in
>     node1 (docsys) nothing is replicated on node2 (docsys2).
>
>     I have this king of configuration (exactly the same file for node1
>     and node2):
>
>     [....]
>     # Replication mode
>     replication_mode = true
>
>     # Load balancing mode, i.e., all SELECTs are load balanced.
>     load_balance_mode = true
>
>     # if there's a data mismatch between master and secondary
>     replication_stop_on_mismatch = false
>     [...]
>
>     [...]
>     #set pgpool2 hostname
>     pgpool2_hostname = 'docsys'
>
>     # system DB info
>     system_db_hostname = 'docsys'
>     system_db_port = 5432
>     system_db_dbname = 'pgbench'
>     system_db_schema = 'pgbench_public'
>     system_db_user = 'pgpool_001'
>     system_db_password = 'pass'
>
>     # backend_hostname, backend_port, backend_weight
>     # here are examples
>     backend_hostname0 = 'docsys'
>     backend_port0 = 5432
>     backend_weight0 = 1
>     backend_data_directory0 = '/backup/postgres/data_001/'
>
>     backend_hostname1 = 'docsys2'
>     backend_port1 = 5433
>     backend_weight1 = 1
>     backend_data_directory1 = '/backup/postgres/data_002/'
>     [...]
>
>
>     (I've just paste parts of the config file in order to not flood
>     this ML)
>
>     I don't really understand how pgpool initialize the replication
>     system. I've build a MySQL replication system (master-slaves) and
>     maybe i'm too focused on this past experience.
>
>     Can somebody give me a hand ?
>
>
>
>
>     -- 
>     Ruben Alves
>     System Analyst USI @ Instituto de Medicina Molecular
>
>     _______________________________________________
>     Pgpool-general mailing list
>     Pgpool-general at pgfoundry.org <mailto:Pgpool-general at pgfoundry.org>
>     http://pgfoundry.org/mailman/listinfo/pgpool-general
>
>


-- 
Ruben Alves 

System Analyst 
USI @ Instituto de Medicina Molecular