Use ' instead of ". i.e. backend_hostname0 = "db1" <-- no good
backend_hostname0 = 'db1' <-- good -- Tatsuo Ishii SRA OSS, Inc. Japan > 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: > > ## > [EMAIL PROTECTED] ~]# 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 > > > > > > _______________________________________________ > Pgpool-general mailing list > [email protected] > http://pgfoundry.org/mailman/listinfo/pgpool-general _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
