[Pgpool-general] can't connect to pgpool
James Martin
jmartin at learningobjects.com
Fri Sep 26 00:29:10 UTC 2008
Hi.
I have 3 machines. 1 is the pgpool controller, the other are 2 postgres
nodes. I have a postgres user named "pooler" on both machines, with db
creation priviledges and no password. I am unable to successfully create
a database while running pgpool-II in replication/loadbalanced mode.
The error I'm giving is:
##
[root at localhost ~]# createdb -h localhost -p 9999 -U postgres
--owner=pooler bench_replication
createdb: could not connect to database postgres: server closed the
connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
##
While running the daemon in debug node, the following is spitted out to
the screen when the attempt is made:
2008-09-25 19:38:57 DEBUG: pid 4846: I am PCP 4846
2008-09-25 19:39:12 DEBUG: pid 4820: I am 4820 accept fd 0
2008-09-25 19:39:12 LOG: pid 4820: connection received: host=127.0.0.1
port=33725
2008-09-25 19:39:12 DEBUG: pid 4820: Protocol Major: 1234 Minor: 5679
database: user:
2008-09-25 19:39:12 DEBUG: pid 4820: SSLRequest: sent N; retry startup
2008-09-25 19:39:12 DEBUG: pid 4820: Protocol Major: 3 Minor: 0
database: postgres user: postgres
2008-09-25 19:39:12 DEBUG: pid 4820: new_connection: connecting 0 backend
2008-09-25 19:39:12 ERROR: pid 4820: connect_inet_domain_socket:
gethostbyname() failed: Success host: "
2008-09-25 19:39:12 ERROR: pid 4820: connection to "(5432) failed
2008-09-25 19:39:12 ERROR: pid 4820: new_connection: create_cp() failed
2008-09-25 19:39:12 LOG: pid 4820: notice_backend_error: 0 fail over
request from pid 4820
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler called
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: starting to
select new master node
2008-09-25 19:39:12 LOG: pid 4813: starting degeneration. shutdown
host "(5432)
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4814
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4815
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4816
2008-09-25 19:39:12 DEBUG: pid 4814: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4817
2008-09-25 19:39:12 DEBUG: pid 4816: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4815: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4817: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4818
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4819
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4820
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4821
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4822
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4823
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4824
2008-09-25 19:39:12 DEBUG: pid 4822: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4825
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4826
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4827
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4828
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4829
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4830
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4831
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4832
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4833
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4834
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4835
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4836
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4837
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4838
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4839
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4840
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4841
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4842
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4843
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4844
2008-09-25 19:39:12 DEBUG: pid 4813: failover_handler: kill 4845
2008-09-25 19:39:12 LOG: pid 4813: failover_handler: set new master
node: 1
2008-09-25 19:39:12 DEBUG: pid 4819: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4824: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4820: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4845: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4833: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4826: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4840: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4831: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4821: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4850: I am 4850
2008-09-25 19:39:12 DEBUG: pid 4843: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4848: I am 4848
2008-09-25 19:39:12 DEBUG: pid 4835: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4851: I am 4851
2008-09-25 19:39:12 DEBUG: pid 4844: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4830: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4825: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4828: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4852: I am 4852
2008-09-25 19:39:12 DEBUG: pid 4841: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4832: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4836: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4829: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4853: I am 4853
2008-09-25 19:39:12 DEBUG: pid 4842: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4827: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4837: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4854: I am 4854
2008-09-25 19:39:12 DEBUG: pid 4839: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4838: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4818: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4849: I am 4849
2008-09-25 19:39:12 DEBUG: pid 4834: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4823: child receives shutdown request
signal 3
2008-09-25 19:39:12 DEBUG: pid 4855: I am 4855
2008-09-25 19:39:12 DEBUG: pid 4856: I am 4856
2008-09-25 19:39:12 DEBUG: pid 4857: I am 4857
2008-09-25 19:39:12 DEBUG: pid 4858: I am 4858
2008-09-25 19:39:12 DEBUG: pid 4859: I am 4859
2008-09-25 19:39:12 DEBUG: pid 4861: I am 4861
2008-09-25 19:39:12 DEBUG: pid 4860: I am 4860
2008-09-25 19:39:12 DEBUG: pid 4862: I am 4862
2008-09-25 19:39:12 DEBUG: pid 4863: I am 4863
2008-09-25 19:39:12 DEBUG: pid 4864: I am 4864
2008-09-25 19:39:12 DEBUG: pid 4865: I am 4865
2008-09-25 19:39:12 DEBUG: pid 4867: I am 4867
2008-09-25 19:39:12 DEBUG: pid 4866: I am 4866
2008-09-25 19:39:12 DEBUG: pid 4869: I am 4869
2008-09-25 19:39:12 DEBUG: pid 4868: I am 4868
2008-09-25 19:39:12 DEBUG: pid 4870: I am 4870
2008-09-25 19:39:12 DEBUG: pid 4871: I am 4871
2008-09-25 19:39:12 DEBUG: pid 4872: I am 4872
2008-09-25 19:39:12 DEBUG: pid 4874: I am 4874
2008-09-25 19:39:12 DEBUG: pid 4873: I am 4873
2008-09-25 19:39:12 DEBUG: pid 4875: I am 4875
2008-09-25 19:39:12 DEBUG: pid 4877: I am 4877
2008-09-25 19:39:12 DEBUG: pid 4878: I am 4878
2008-09-25 19:39:12 LOG: pid 4813: failover done. shutdown host "(5432)
2008-09-25 19:39:12 DEBUG: pid 4813: reap_handler called
2008-09-25 19:39:12 DEBUG: pid 4813: reap_handler: call wait3
2008-09-25 19:39:12 DEBUG: pid 4813: child 4814 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4815 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4816 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4817 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4818 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4819 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4820 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4821 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4822 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4823 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4824 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4825 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4826 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4827 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4828 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4879: I am 4879
2008-09-25 19:39:12 DEBUG: pid 4813: child 4829 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4830 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4831 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4832 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4833 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4834 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4835 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4836 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4837 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4838 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4839 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4840 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4841 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4842 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4843 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4844 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: child 4845 exits with status 0 by
signal 0
2008-09-25 19:39:12 DEBUG: pid 4813: reap_handler: normally exited
2008-09-25 19:39:12 DEBUG: pid 4876: I am 4876
#####
The same error is displayed even with a simple connect to localhost -P 9999.
I am able to connect to the other nodes with the psql command without
any problems, but for debugging sake, here's my pg_hba.conf for my db1 &
db2 machines:
###
# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 ident sameuser
host all pooler 10.1.1.88/32 trust
# IPv6 local connections:
host all all ::1/128 trust
####
A copy of my /etc/pgpool.conf:
#
# pgpool-II configuration file sample
# $Header: /cvsroot/pgpool/pgpool-II/pgpool.conf.sample,v 1.21
2008/03/18 08:39: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.
socket_dir = '/tmp'
# Unix domain socket path for pgpool communication manager.
pcp_socket_dir = '/tmp/'
# Unix domain socket path for the backend.
backend_socket_dir = '/tmp/'
# 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
# If client_idle_limit is n (n > 0), the client is forced to be
# disconnected whenever after n seconds idle (even inside an explicit
# transactions!)
# 0 means no disconnect.
client_idle_limit = 0
# Maximum time in seconds to complete client authentication.
# 0 means no timeout.
authentication_timeout = 60
# Logging directory
logdir = '/var/run'
# Replication mode
replication_mode = true
# Load balancing mode, i.e., all SELECTs are load balanced.
# This is ignored if replication_mode is false.
load_balance_mode = true
# 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 = 'nobody'
# Execute command by failover.
# special values: %d = node id
# %h = host name
# %p = port number
# %D = database cluster path
# %m = new master node id
# %M = old master node id
# %% = '%' character
#
failover_command = ''
# Execute command by failback.
# special values: %d = node id
# %h = host name
# %p = port number
# %D = database cluster path
# %m = new master node id
# %M = old master node id
# %% = '%' character
#
failback_command = ''
# 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 = true
# 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 = true
# If true, incoming connections will be printed to the log.
log_connections = true
# 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 = 'pgpool-ctrl'
# 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 = ''
# backend_hostname, backend_port, backend_weight
# here are examples
#backend_hostname0 = 'host1'
#backend_port0 = 5432
#backend_weight0 = 1
#backend_data_directory0 = '/data'
#backend_hostname1 = 'host2'
#backend_port1 = 5433
#backend_weight1 = 1
#backend_data_directory1 = '/data1'
backend_hostname0 = "db1"
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = "db2"
backend_port1 = 5432
backend_weight1 = 1
# - 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 = true
# - online recovery -
# online recovery user
recovery_user = 'nobody'
# online recovery password
recovery_password = ''
# execute a command in first stage.
recovery_1st_stage_command = ''
# execute a command in second stage.
recovery_2nd_stage_command = ''
# maximum time in seconds to wait for remote start-up. 0 means no wait
recovery_timeout = 90
#####
and my /etc/pcp.conf:
# USERID:MD5PASSWD
postgres:e8a48653851e28c69d0506508fb27fc5
#
My host and IP information :
pgpool-ctrl = 10.1.1.88
db1 = 10.1.1.87
db2 = 10.1.1.102
my /etc/pool_hba.conf:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
I can't figure out what the problem is -- the tutorial only gives
examples with using localhost, so it's not very helpful. The connection
logs on db1 and db2 show nothing even making an attempt to connect, so
it looks like it's not getting passed pgpool-ctrl. tcpdump shows no
connection attempts from pgpool-ctrol to db1 or db2 either. I don't
need to load pgpool on all the nodes do I? I'm at a loss, any help is
greatly appreciated!
Thanks,
James
More information about the Pgpool-general
mailing list