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’