HELP PLEASE On Tue, Nov 11, 2008 at 10:20 AM, nir osovsky <[EMAIL PROTECTED]> wrote:
> Thank you. > I sent the logs for pgpool and postgresql. Just in case you don't have > access to them I'm resending. > PLEASE review the following and help me solve this problem. > Thanks, > Nir > ========================================== > I'm trying to configure pgpool-II 2.0.1 to replication between two > computers and postgresql database in each. > pgpool-II is defined on dev1. > postgresql 8.1.11 (testdb) is defined on dev1 > postgresql 8.1.11 (testdb) is defined on dev2 > > I can run the following commands from each computer and run queries from > the databases: > psql -h dev1 -U postgres testdb > psql -h dev2 -U postgres testdb > > I'm also able to do and see the databases bench_replication created on both > computers and have the same data: > createdb -p 9999 bench_replication > pgbench -i -p 9999 bench_replication > > While trying to run: > psql -p 9999 -c "INSERT INTO branches VALUES (3,1,' ')" bench_replication > > I'm getting: > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > connection to server was lost > > And the /tmp/pgpool.log show: > ====================== > 2008-11-08 22:41:53 DEBUG: pid 23744: I am 23744 accept fd 0 > 2008-11-08 22:41:53 DEBUG: pid 23744: Protocol Major: 3 Minor: 0 database: > bench_replication user: postgres > 2008-11-08 22:41:53 DEBUG: pid 23744: new_connection: connecting 0 backend > 2008-11-08 22:41:53 DEBUG: pid 23744: new_connection: connecting 1 backend > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 0 > length: 8 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 1 > length: 8 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 25 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 25 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: client_encoding > value: UTF8 > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: client_encoding > value: UTF8 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 23 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 23 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: DateStyle value: > ISO, MDY > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: DateStyle value: > ISO, MDY > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 26 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 26 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: integer_datetimes > value: off > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: integer_datetimes > value: off > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 20 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 20 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: is_superuser > value: on > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: is_superuser > value: on > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 25 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 25 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: server_encoding > value: UTF8 > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: server_encoding > value: UTF8 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 25 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 25 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: server_version > value: 8.1.2 > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: server_version > value: 8.1.2 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 35 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 35 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: > session_authorization value: postgres > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: > session_authorization value: postgres > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 36 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 36 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: > standard_conforming_strings value: off > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: > standard_conforming_strings value: off > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 0 length: 21 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master > slot: 1 length: 21 > 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: TimeZone value: > PST8PDT > 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: TimeZone value: > PST8PDT > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 0 > length: 12 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 1 > length: 12 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_send_auth_ok: send pid 6156 to > frontend > 2008-11-08 22:41:53 DEBUG: pid 23744: select_load_balancing_node: selected > backend id is 0 > 2008-11-08 22:41:53 DEBUG: pid 23744: read_kind_from_backend: read kind > from 0 th backend Z NUM_BACKENDS: 2 > 2008-11-08 22:41:53 DEBUG: pid 23744: read_kind_from_backend: read kind > from 1 th backend Z NUM_BACKENDS: 2 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_process_query: kind from > backend: Z > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 0 > length: 5 > 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 1 > length: 5 > 2008-11-08 22:41:53 DEBUG: pid 23744: ReadyForQuery: message length: 5 > 2008-11-08 22:41:53 DEBUG: pid 23744: ReadyForQuery: transaction state: I > 2008-11-08 22:41:53 DEBUG: pid 23744: read kind from frontend Q(51) > 2008-11-08 22:41:53 DEBUG: pid 23744: statement2: INSERT INTO branches > VALUES (3,1,' ') > 2008-11-08 22:41:53 DEBUG: pid 23744: do_command: Query: BEGIN > 2008-11-08 22:41:53 LOG: pid 23744: do_command: backend does not > successfully complete command BEGIN status N > 2008-11-08 22:41:53 DEBUG: pid 23744: command tag: SDEBUG > 2008-11-08 22:41:53 LOG: pid 23744: do_command: backend does not > successfully complete command BEGIN status N > 2008-11-08 22:41:53 DEBUG: pid 23744: command tag: SDEBUG > 2008-11-08 22:41:53 LOG: pid 23744: do_command: backend does not > successfully complete command BEGIN status N > 2008-11-08 22:41:53 DEBUG: pid 23744: command tag: SDEBUG > 2008-11-08 22:41:53 LOG: pid 23744: do_command: backend does not > successfully complete command BEGIN status N > 2008-11-08 22:41:53 DEBUG: pid 23744: command tag: SLOG > 2008-11-08 22:41:53 LOG: pid 23744: do_command: backend does not > successfully complete command BEGIN status N > 2008-11-08 22:41:53 DEBUG: pid 23744: command tag: SDEBUG > 2008-11-08 22:41:53 LOG: pid 23744: do_command: backend does not > successfully complete command BEGIN status N > 2008-11-08 22:41:53 DEBUG: pid 23744: command tag: SDEBUG > 2008-11-08 22:41:53 DEBUG: pid 23744: command tag: BEGIN > 2008-11-08 22:41:53 ERROR: pid 23744: do_command: backend does not return > ReadyForQuery > 2008-11-08 22:41:53 LOG: pid 23744: do_child: exits with status 1 due to > error > 2008-11-08 22:41:53 DEBUG: pid 23713: reap_handler called > 2008-11-08 22:41:53 DEBUG: pid 23713: reap_handler: call wait3 > 2008-11-08 22:41:53 DEBUG: pid 23713: child 23744 exits with status 256 by > signal 0 > 2008-11-08 22:41:53 DEBUG: pid 24215: I am 24215 > 2008-11-08 22:41:53 DEBUG: pid 23713: fork a new child pid 24215 > 2008-11-08 22:41:53 DEBUG: pid 23713: reap_handler: normally exited > > Postgresql.log show: > =============== > > [2008-11-09 00:23:19.115 PST] :[unknown] LOG: connection received: host= > 127.0.0.1 port=38487 > [2008-11-09 00:23:19.115 PST] : DEBUG: forked new backend, pid=24766 > socket=8 > [2008-11-09 00:23:19.117 PST] 127.0.0.1:bench_replication LOG: connection > authorized: user=postgres database=bench_replic > ation > [2008-11-09 00:23:19.117 PST] 127.0.0.1:bench_replication DEBUG: > postmaster child[24766]: starting with ( > [2008-11-09 00:23:19.117 PST] 127.0.0.1:bench_replication DEBUG: > postgres > [2008-11-09 00:23:19.117 PST] 127.0.0.1:bench_replication DEBUG: > -v196608 > [2008-11-09 00:23:19.117 PST] 127.0.0.1:bench_replication DEBUG: -p > [2008-11-09 00:23:19.117 PST] 127.0.0.1:bench_replication DEBUG: > bench_replication > [2008-11-09 00:23:19.117 PST] 127.0.0.1:bench_replication DEBUG: ) > [2008-11-09 00:23:19.117 PST] 127.0.0.1:bench_replication DEBUG: > InitPostgres > [2008-11-09 00:23:19.118 PST] 127.0.0.1:bench_replication DEBUG: > StartTransaction > [2008-11-09 00:23:19.118 PST] 127.0.0.1:bench_replication DEBUG: name: > unnamed; blockState: DEFAULT; state: INPROGR > , xid/subid/cid: 28257057/1/0, nestlvl: 1, children: <> > [2008-11-09 00:23:19.118 PST] 127.0.0.1:bench_replication DEBUG: > CommitTransaction > [2008-11-09 00:23:19.118 PST] 127.0.0.1:bench_replication DEBUG: name: > unnamed; blockState: STARTED; state: INPROGR > , xid/subid/cid: 28257057/1/0, nestlvl: 1, children: <> > [2008-11-09 00:23:19.120 PST] 127.0.0.1:bench_replication DEBUG: > StartTransactionCommand > [2008-11-09 00:23:19.120 PST] 127.0.0.1:bench_replication DEBUG: > StartTransaction > [2008-11-09 00:23:19.120 PST] 127.0.0.1:bench_replication DEBUG: name: > unnamed; blockState: DEFAULT; state: INPROGR > , xid/subid/cid: 28257058/1/0, nestlvl: 1, children: <> > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication LOG: statement: > BEGIN > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication DEBUG: > ProcessUtility > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication DEBUG: > CommitTransactionCommand > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication LOG: duration: > 0.269 ms > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication LOG: duration: > 0.269 ms statement: BEGIN > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication LOG: could not > receive data from client: Connection reset by pe > er > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication LOG: unexpected > EOF on client connection > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication DEBUG: > proc_exit(0) > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication DEBUG: > shmem_exit(0) > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication LOG: > disconnection: session time: 0:00:00.00 user=postgres data > base=bench_replication host=127.0.0.1 port=38487 > [2008-11-09 00:23:19.121 PST] 127.0.0.1:bench_replication DEBUG: exit(0) > [2008-11-09 00:23:19.122 PST] : DEBUG: reaping dead processes > [2008-11-09 00:23:19.122 PST] : DEBUG: server process (PID 24766) exited > with exit code 0 > > pg_hba.conf > ========= > # "local" is for Unix domain socket connections only > local all all trust > # IPv4 local connections: > host all all 127.0.0.1/32 trust > host all all 192.168.158.38/32 trust > host all all 192.168.158.39/32 trust > # IPv6 local connections: > host all all ::1/128 trust > > pool_hba.conf > ========== > # "local" is for Unix domain socket connections only > local all all trust > # IPv4 local connections: > host all all 127.0.0.1/32 trust > host all all 192.168.158.38/32 trust > host all all 192.168.158.39/32 trust > > pgpool.conf > ========= > listen_addresses = '*' > port = 9999 > pcp_port = 9898 > socket_dir = '/tmp' > pcp_socket_dir = '/tmp' > backend_socket_dir = '/tmp' > pcp_timeout = 10 > num_init_children = 32 > max_pool = 4 > child_life_time = 300 > connection_life_time = 0 > child_max_connections = 0 > client_idle_limit = 0 > authentication_timeout = 60 > logdir = '/tmp' > > # Replication mode > replication_mode = true > replication_timeout = 9000 > load_balance_mode = true > replication_stop_on_mismatch = false > replicate_select = false > reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' > print_timestamp = true > master_slave_mode = false > connection_cache = true > > health_check_timeout = 20 > health_check_period = 0 > health_check_user = 'nobody' > failover_command = '' > failback_command = '' > insert_lock = false > ignore_leading_white_space = true > log_statement = false > > log_connections = false > log_hostname = false > parallel_mode = false > enable_query_cache = false > pgpool2_hostname = '' > 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_hostname0 = 'localhost' > backend_port0 = 5432 > backend_weight0 = 1 > backend_data_directory0 = '/usr/local/pgsql/data' > backend_hostname1 = '192.168.158.39' > backend_port1 = 5432 > backend_weight1 = 1 > backend_data_directory1 = '/usr/local/pgsql/data' > > # - HBA - > > #enable_pool_hba = false > enable_pool_hba = true > recovery_user = 'nobody' > recovery_password = '' > recovery_1st_stage_command = '' > recovery_2nd_stage_command = '' > > My questions are: > 1. Why the above insert command failed? > 2. What do I need to configure in order for any change to the testdb on > dev1 to replicate automaticly to testdb on dev2? > > > Thanks, > Nir > > > On Tue, Nov 11, 2008 at 12:11 AM, Jaume Sabater <[EMAIL PROTECTED]>wrote: > >> nir osovsky wrote: >> >> > I urgently need your help here. Please reply to my previous email and >> > let me know if you have any idea what the problem is. >> >> Try setting pgpool-II in debug mode and see what you get in the log. >> That is why you have logs, don't you? ;-) >> >> -- >> Jaume Sabater >> http://linuxsilo.net/ >> >> "Ubi sapientas ibi libertas" >> >> _______________________________________________ >> 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
