[Pgpool-general] Pgpool-II - replication problems

Wed Nov 12 08:12:01 UTC 2008

Ok, I have put CVS head source tar ball at:

http://www.sra.co.jp/people/t-ishii/tmp/pgpool-II.tar.gz

Please grab and try.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Hi Tatsuo,
> 
> I urgently need your help.  I appreciate any help you can give.
> 
> While trying to follow the instruction at
> http://pgfoundry.org/scm/?group_id=1000055 I'm getting the following errors:
> 1. cvs -d :pserver:anonymous at cvs.pgfoundry.org:/cvsroot/pgpool login
> Logging in to :pserver:anonymous at cvs.pgfoundry.org:2401/cvsroot/pgpool
> CVS password:
> cvs [login aborted]: connect to [cvs.pgfoundry.org]:2401 failed: Connection
> timed out
> 
> 2. cvs -d :pserver:anonymous at cvs.pgfoundry.org:/cvsroot/pgpool checkout
> pgpool/pgpool-II/
> cvs [checkout aborted]: connect to [cvs.pgfoundry.org]:2401 failed:
> Connection timed out
> 
> Please help me.
> 
> Thanks,
> Nir
> 
> 
> 
> > On Sun, Nov 9, 2008 at 5:35 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >
> >> CVS Head is the lastest development version.
> >>
> >> See:
> >>
> >> http://pgfoundry.org/scm/?group_id=1000055
> >>
> >> to know how to get it. After getting the source code, the way for
> >> compiling and running is exactly same as the production release.
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >>
> >
> 
> > > On Sun, Nov 9, 2008 at 5:02 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >> >
> >> > > Hum. I think I need more info. I have added more debug message to the
> >> > > CVS Head version. Can you grab the CVS head and try again?
> >> > > --
> >> > > Tatsuo Ishii
> >> > > SRA OSS, Inc. Japan
> >> > >
> >> > > > Please ignore the previous log. Here is the correct one:
> >> > > >
> >> > > > [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
> >> > > >
> >> > > > > On Sat, Nov 8, 2008 at 11:43 PM, Tatsuo Ishii <ishii at sraoss.co.jp
> >> >
> >> > > wrote:
> >> > > > >
> >> > > > >> 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
> >>
> >>
> >