[Pgpool-general] Pgpool-II - replication problems

Sun Nov 9 07:43:54 UTC 2008

Can you show me the error log of PostgreSQL?

Also is it possible to try the latest version of of pgpool-II (2.1)?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Hi,
> I'm trying to configure pgpool-II 2.to 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
> 
> 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?
> 
> Can someone please help? If there is a need for additional information/files
> please let me know.
> 
> Than you for any help,
> Nir