[Pgpool-general] Pgpool-II - replication problems
nir osovsky
nosovsky1 at gmail.com
Wed Nov 12 07:25:52 UTC 2008
HELP PLEASE
On Tue, Nov 11, 2008 at 10:20 AM, nir osovsky <nosovsky1 at gmail.com> 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 <jsabater at linuxsilo.net>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
>> Pgpool-general at pgfoundry.org
>> http://pgfoundry.org/mailman/listinfo/pgpool-general
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/pgpool-general/attachments/20081111/742b87e9/attachment-0001.html
More information about the Pgpool-general
mailing list