[Pgpool-general] Pgpool-II - replication problems

Sun Nov 9 06:50:50 UTC 2008

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/pgpool-general/attachments/20081108/04269515/attachment.html 