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 <[email protected] <mailto:[email protected]>>
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
[email protected] <mailto:[email protected]>
http://pgfoundry.org/mailman/listinfo/pgpool-general
--
Ruben Alves
System Analyst
USI @ Instituto de Medicina Molecular
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
