Hilfe pgpool2

Hallo zusammen,
beschäftige mich zur Zeit mit pgpool2. Das ganze sollte zum testen erstmal auf zwei identischen virtuellen Maschinen mit Ubuntu laufen, aber ich bekomms net hin. PGPool scheint zwar zu laufen aber wenn ich mich über port 9999 connecten will bekomme ich Fehlermeldungen. Ich poste einfach mal meine Daten und hoffe das mir jemand helfen kann.

sudo pgpool -n -d
[sudo] password for thomas:
2009-02-17 15:24:50 DEBUG: pid 6290: key: listen_addresses
2009-02-17 15:24:50 DEBUG: pid 6290: value: ‘*’ kind: 4
2009-02-17 15:24:50 DEBUG: pid 6290: key: port
2009-02-17 15:24:50 DEBUG: pid 6290: value: 9999 kind: 2
2009-02-17 15:24:50 DEBUG: pid 6290: key: pcp_port
2009-02-17 15:24:50 DEBUG: pid 6290: value: 9898 kind: 2
2009-02-17 15:24:50 DEBUG: pid 6290: key: socket_dir
2009-02-17 15:24:50 DEBUG: pid 6290: value: ‘/var/run/postgresql/’ kind: 4
2009-02-17 15:24:50 DEBUG: pid 6290: key: pcp_socket_dir
2009-02-17 15:24:50 DEBUG: pid 6290: value: ‘/var/run/postgresql’ kind: 4
2009-02-17 15:24:50 DEBUG: pid 6290: key: backend_socket_dir
2009-02-17 15:24:50 DEBUG: pid 6290: value: ‘/var/run/postgresql/’ kind: 4
2009-02-17 15:24:50 DEBUG: pid 6290: key: pcp_timeout


2009-02-17 15:24:50 DEBUG: pid 6290: value: 1 kind: 2
2009-02-17 15:24:50 DEBUG: pid 6290: pool_config: weight slot number 0 weight: 1.000000
2009-02-17 15:24:50 DEBUG: pid 6290: key: backend_data_directory0
2009-02-17 15:24:50 DEBUG: pid 6290: value: ‘/var/lib/postgresql/8.1/main’ kind: 4
2009-02-17 15:24:50 DEBUG: pid 6290: num_backends: 1 num_backends: 1 total_weight: 1.000000
2009-02-17 15:24:50 DEBUG: pid 6290: backend 0 weight: 2147483647.000000
2009-02-17 15:24:50 DEBUG: pid 6291: I am 6291
2009-02-17 15:24:50 DEBUG: pid 6292: I am 6292
*
*
*
*
2009-02-17 15:24:50 DEBUG: pid 6322: I am 6322
2009-02-17 15:24:50 LOG: pid 6290: pgpool successfully started
2009-02-17 15:24:50 DEBUG: pid 6323: I am PCP 6323


Wenn ich mich z.b connectiere so zum Beispiel: psql -p 9999 -h localhost passiert folgendendes:


2009-02-17 15:27:44 DEBUG: pid 6291: I am 6291 accept fd 0
2009-02-17 15:27:44 DEBUG: pid 6291: Protocol Major: 1234 Minor: 5679 database: user:
2009-02-17 15:27:44 DEBUG: pid 6291: SSLRequest: sent N; retry startup
2009-02-17 15:27:44 DEBUG: pid 6291: Protocol Major: 3 Minor: 0 database: thomas user: thomas
2009-02-17 15:27:44 DEBUG: pid 6291: new_connection: connecting 0 backend
2009-02-17 15:27:44 ERROR: pid 6291: connect_inet_domain_socket: connect() failed: Connection refused
2009-02-17 15:27:44 ERROR: pid 6291: connection to 10.211.55.7(5432) failed
2009-02-17 15:27:44 ERROR: pid 6291: new_connection: create_cp() failed
2009-02-17 15:27:44 LOG: pid 6291: notice_backend_error: 0 fail over request from pid 6291
2009-02-17 15:27:44 DEBUG: pid 6290: failover_handler called
2009-02-17 15:27:44 LOG: pid 6290: starting degeneration. shutdown host 10.211.55.7(5432)
2009-02-17 15:27:44 DEBUG: pid 6290: failover_handler: starting to select new master node
2009-02-17 15:27:44 ERROR: pid 6290: failover_handler: no valid DB node found
2009-02-17 15:27:44 DEBUG: pid 6290: failover_handler: kill 6291
*
*
*
*
2009-02-17 15:27:45 DEBUG: pid 6290: failover_handler: kill 6322
2009-02-17 15:27:45 DEBUG: pid 6333: I am 6333
2009-02-17 15:27:45 DEBUG: pid 6321: child receives shutdown request signal 3
2009-02-17 15:27:45 DEBUG: pid 6334: I am 6334
2009-02-17 15:27:45 DEBUG: pid 6322: child receives shutdown request signal 3
2009-02-17 15:27:45 DEBUG: pid 6335: I am 6335
2009-02-17 15:27:45 DEBUG: pid 6291: child receives shutdown request signal 3
2009-02-17 15:27:45 DEBUG: pid 6336: I am 6336
*
*
*
*
*
2009-02-17 15:27:45 DEBUG: pid 6364: I am 6364
2009-02-17 15:27:45 LOG: pid 6290: failover done. shutdown host 10.211.55.7(5432)
2009-02-17 15:27:45 DEBUG: pid 6290: reap_handler called
2009-02-17 15:27:45 DEBUG: pid 6290: reap_handler: call wait3
2009-02-17 15:27:45 DEBUG: pid 6290: child 6291 exits with status 0 by signal 0
2009-02-17 15:27:45 DEBUG: pid 6290: child 6292 exits with status 0 by signal 0
*
*
*
2009-02-17 15:27:45 DEBUG: pid 6290: child 6321 exits with status 0 by signal 0
2009-02-17 15:27:45 DEBUG: pid 6290: child 6322 exits with status 0 by signal 0
2009-02-17 15:27:45 DEBUG: pid 6290: reap_handler: normally exited


Hier mal mein config datei:

pgpool-II configuration file sample

$Header: /cvsroot/pgpool/pgpool-II/pgpool.conf.sample,v 1.4.2.3 2007/10/12 09:15:02 y-asaba Exp $

Host name or IP address to listen on: ‘*’ for all, ‘’ for no TCP/IP

connections

listen_addresses = ‘*’

Port number for pgpool

port = 9999

Port number for pgpool communication manager

pcp_port = 9898

Unix domain socket path. (The Debian package defaults to

/var/run/postgresql.)

socket_dir = ‘/var/run/postgresql/’

Unix domain socket path for pgpool communication manager.

(Debian package defaults to /var/run/postgresql)

pcp_socket_dir = ‘/var/run/postgresql’

Unix domain socket path for the backend. Debian package defaults to /var/run/postgresql!

backend_socket_dir = ‘/var/run/postgresql/’

pgpool communication manager timeout. 0 means no timeout, but strongly not recommended!

pcp_timeout = 10

number of pre-forked child process

num_init_children = 32

Number of connection pools allowed for a child process

max_pool = 4

If idle for this many seconds, child exits. 0 means no timeout.

child_life_time = 300

If idle for this many seconds, connection to PostgreSQL closes.

0 means no timeout.

connection_life_time = 0

If child_max_connections connections were received, child exits.

0 means no exit.

child_max_connections = 0

Maximum time in seconds to complete client authentication.

0 means no timeout.

authentication_timeout = 60

Logging directory (more accurately, the directory for the PID file)

logdir = ‘/var/run/postgresql/’

Replication mode

replication_mode = true

Set this to true if you want to avoid deadlock situations when

replication is enabled. There will, however, be a noticable performance

degradation. A workaround is to set this to false and insert a /STRICT/

comment at the beginning of the SQL command.

replication_strict = true

When replication_strict is set to false, there will be a chance for

deadlocks. Set this to nonzero (in milliseconds) to detect this

situation and resolve the deadlock by aborting current session.

replication_timeout = 5000

Load balancing mode, i.e., all SELECTs except in a transaction block

are load balanced. This is ignored if replication_mode is false.

load_balance_mode = false

if there’s a data mismatch between master and secondary

start degeneration to stop replication mode

replication_stop_on_mismatch = false

If true, replicate SELECT statement when load balancing is disabled.

If false, it is only sent to the master node.

replicate_select = false

Semicolon separated list of queries to be issued at the end of a session

reset_query_list = ‘ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT’

If true print timestamp on each log line.

print_timestamp = true

If true, operate in master/slave mode.

master_slave_mode = false

If true, cache connection pool.

connection_cache = true

Health check timeout. 0 means no timeout.

health_check_timeout = 20

Health check period. 0 means no health check.

health_check_period = 0

Health check user

health_check_user = ‘postgres’

If true, automatically lock table with INSERT statements to keep SERIAL

data consistency. An /INSERT LOCK/ comment has the same effect. A

/NO INSERT LOCK*/ comment disables the effect.

insert_lock = false

If true, ignore leading white spaces of each query while pgpool judges

whether the query is a SELECT so that it can be load balanced. This

is useful for certain APIs such as DBI/DBD which is known to adding an

extra leading white space.

ignore_leading_white_space = false

If true, print all statements to the log. Like the log_statement option

to PostgreSQL, this allows for observing queries without engaging in full

debugging.

log_statement = false

If true, incoming connections will be printed to the log.

log_connections = false

If true, hostname will be shown in ps status. Also shown in

connection log if log_connections = true.

Be warned that this feature will add overhead to look up hostname.

log_hostname = false

if non 0, run in parallel query mode

parallel_mode = false

if non 0, use query cache

enable_query_cache = false

#set pgpool2 hostname
pgpool2_hostname = ‘’

system DB info

system_db_hostname = ‘localhost’
system_db_port = 5432
system_db_dbname = ‘pgpool’
system_db_schema = ‘pgpool_catalog’
system_db_user = ‘pgpool’
system_db_password = ‘4soldier’

backend_hostname, backend_port, backend_weight

here are examples

- HBA -

If true, use pool_hba.conf for client authentication. In pgpool-II

1.1, the default value is false. The default value will be true in

1.2.

enable_pool_hba = false
client_idle_limit = 0
pid_file_name = ‘/var/run/postresqll/pgpool.pid’
recovery_user = ‘postgres’
recovery_password = ‘4soldier’
recovery_1st_stage_command = ‘’
recovery_2nd_stage_command = ‘’
recovery_timeout = 90
failover_command = ‘’
failback_command = ‘’
client_idle_limit_in_recovery = 0
backend_hostname0 = ‘10.211.55.7’
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/var/lib/postgresql/8.1/main’
backend_hostname1 = ‘10.211.55.8’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/var/lib/postgresql/8.1/main’

hat sich erledigt. pg_hba.conf mußte editiert werden, damit ip von außen zugreifen können