[Pgpool-general] Question about Online Recovery

Tue Apr 7 22:27:13 UTC 2009

Hi DM,

pgpool seems to be working fine.

I am still getting this message 

LOG: could not receive data from client: connection reset by peer
LOG: unexpected EOF within message length word

When I try to do online recovery:
pgpool runs my script and my new node's postgresql is now started by pgpool.

However, even though postgresql is already running in my new node, pgpool can't seem to be able to ping it.

2009-04-07 18:11:17 DEBUG: pid 9628: starting health checking
2009-04-07 18:11:47 DEBUG: pid 9628: starting health checking
2009-04-07 18:12:17 DEBUG: pid 9628: starting health checking
2009-04-07 18:12:43 ERROR: pid 9661: check_postmaster_started: remote host start up did not finish in 90 sec.
2009-04-07 18:12:43 ERROR: pid 9661: start_recovery: check start failed
2009-04-07 18:12:43 DEBUG: pid 9661: pcp_child: received PCP packet type of service 'X'
2009-04-07 18:12:43 DEBUG: pid 9661: pcp_child: client disconnecting. close connection

When I check that node, it seems to be able to recover and get the latest database tables but pgpool is still saying that it failed?

-Harold

--- On Tue, 4/7/09, DM <dm.aeqa at gmail.com> wrote:

> From: DM <dm.aeqa at gmail.com>
> Subject: Re: [Pgpool-general] Question about Online Recovery
> To: rold_50 at yahoo.com
> Cc: pgpool-general at pgfoundry.org
> Date: Tuesday, April 7, 2009, 5:19 PM
> Hi Harold,
> 
> Attached is the steps to recover the database using pgpool
> recovery node.
> (the document is not a standard, i prepared while i was
> testing pgpool
> recovery)
> Email me for any questions on pgpool recovery i will try to
> answer to my
> best of knowledge.
> 
> ------------------------------------------------------
> Question: have a couple of errors.
> LOG: could not receive data from client: connection reset
> by peer
> LOG: unexpected EOF within message length word
> 
> Let me know if pgpool is working fine or not?
> 
> ------------------------------------------------------
> You dont need to define postgres password, but you have to
> create user id
> and password for using the file pcp.conf file under
> "/opt/pgpoolII/etc/"
> 
> snippiet form pcp.conf file..
> # USERID:MD5PASSWD
> pgpuser:09613b137a2e9be2ae93588c5970ab53
> 
> my pcp_recovery_node command looks like this
> usr/pgpool-II/2.2RC2/bin/pcp_recovery_node 20
> <hostname> 9898 pgpuser
> test123 1
> ------------------------------------------------------
> 
> My suggestion is execute your scripts base-backup.sh,
> pgpool-recovery.sh,
> pgpool_remote_start manually and see if it works or not. If
> your scripts
> work manually then pcp_recovery_node command should also
> work.
> Enjoy
> 
> - Deepak Murthy
> 
> 
> On Tue, Apr 7, 2009 at 1:05 PM, Harold Lim
> <rold_50 at yahoo.com> wrote:
> 
> >
> > Hi DM,
> >
> > Can you send me your steps to recovery?
> >
> > Also, when I look at my postgresql log,
> >
> > I have a couple of errors.
> >
> > LOG: could not receive data from client: connection
> reset by peer
> > LOG: unexpected EOF within message length word
> >
> 
> 
> >
> >
> > I also have
> >
> > Password for user postgres:
> > psql: fe_sendauth: no password supplied
> >
> >
> >
> > But I did supply a password when I run
> pcp_recovery_node
> >
> > /opt/pgpoolII/bin/pcp_recovery_node 20 node1 9898
> postgres postgres 1
> >
> >
> > Thanks,
> > Harold
> >
> >
> >
> > --- On Tue, 4/7/09, DM <dm.aeqa at gmail.com>
> wrote:
> >
> > > From: DM <dm.aeqa at gmail.com>
> > > Subject: Re: [Pgpool-general] Question about
> Online Recovery
> > > To: rold_50 at yahoo.com,
> pgpool-general at pgfoundry.org
> > > Date: Tuesday, April 7, 2009, 12:07 PM
> >  > Harold,
> > >
> > > WAL archives can be enabled on one machine or
> many machines
> > > it depends on
> > > how you want.
> > > Assuming you have 2 systems one primary and
> another
> > > standby, in real time
> > > scenario any system can go down. Its better to
> enable WAL
> > > on both system so
> > > that if one fails you could recover from other.
> > >
> > > For your issue with recovery make sure that you
> have added
> > > both of your
> > > system ip address or host name in pgpool_hba.conf
> file and
> > > try executing
> > > your scripts one by one you should be able to
> debug it.
> > >
> > > Also copy the scripts to recover the database on
> both
> > > machines.
> > >
> > > I can send you my steps to recovery if you want.
> Its same
> > > as Gerd's but
> > > little modification.
> > >
> > > - Deepak
> > >
> -----------------------------------------------------------
> > >
> > > > Message: 2
> > > > Date: Mon, 6 Apr 2009 15:32:34 -0700 (PDT)
> > > > From: Harold Lim <rold_50 at yahoo.com>
> > > > Subject: [Pgpool-general] Question about
> Online
> > > Recovery
> > > > To: pgpool-general at pgfoundry.org
> > > > Message-ID:
> > > <5188.66741.qm at web51003.mail.re2.yahoo.com>
> > > > Content-Type: text/plain; charset=us-ascii
> > > >
> > > >
> > > > Hi All,
> > > >
> > > > I'm trying to setup the online recovery.
> I'm
> > > following the
> > > > tutorial/beginners guide written by Gerd.
> > > >
> > > > I am getting an error for 2nd stage. Any
> idea what the
> > > problem might be?
> > > >
> > > >
> > > > Below is the log file:
> > > >
> > > > 2009-04-06 18:28:14 DEBUG: pid 25867:
> pcp_child:
> > > authentication OK
> > > > 2009-04-06 18:28:14 DEBUG: pid 25867:
> pcp_child:
> > > received PCP packet type
> > > > of service 'O'
> > > > 2009-04-06 18:28:14 DEBUG: pid 25867:
> pcp_child: start
> > > online recovery
> > > > 2009-04-06 18:28:14 LOG:   pid 25867:
> starting
> > > recovering node 1
> > > > 2009-04-06 18:28:14 DEBUG: pid 25867:
> exec_checkpoint:
> > > start checkpoint
> > > > 2009-04-06 18:28:14 DEBUG: pid 25867:
> exec_checkpoint:
> > > finish checkpoint
> > > > 2009-04-06 18:28:14 LOG:   pid 25867:
> CHECKPOINT in
> > > the 1st stage done
> > > > 2009-04-06 18:28:14 LOG:   pid 25867:
> starting
> > > recovery command: "SELECT
> > > > pgpool_recovery('copy_base_backup',
> > > '172.16.63.10',
> > > > '/opt/PostgreSQL/8.3/data')"
> > > > 2009-04-06 18:28:14 DEBUG: pid 25867:
> exec_recovery:
> > > start recovery
> > > > 2009-04-06 18:28:22 DEBUG: pid 25834:
> starting health
> > > checking
> > > > 2009-04-06 18:28:22 DEBUG: pid 25834:
> health_check: 0
> > > th DB node status: 1
> > > > 2009-04-06 18:28:22 DEBUG: pid 25834:
> health_check: 1
> > > th DB node status: 3
> > > > 2009-04-06 18:28:33 DEBUG: pid 25867:
> exec_recovery:
> > > finish recovery
> > > > 2009-04-06 18:28:33 LOG:   pid 25867: 1st
> stage is
> > > done
> > > > 2009-04-06 18:28:33 LOG:   pid 25867:
> starting 2nd
> > > stage
> > > > 2009-04-06 18:28:33 LOG:   pid 25867: all
> connections
> > > from clients have
> > > > been closed
> > > > 2009-04-06 18:28:33 DEBUG: pid 25867:
> exec_checkpoint:
> > > start checkpoint
> > > > 2009-04-06 18:28:33 DEBUG: pid 25867:
> exec_checkpoint:
> > > finish checkpoint
> > > > 2009-04-06 18:28:33 LOG:   pid 25867:
> CHECKPOINT in
> > > the 2nd stage done
> > > > 2009-04-06 18:28:33 LOG:   pid 25867:
> starting
> > > recovery command: "SELECT
> > > >
> pgpool_recovery('pgpool_recovery_pitr',
> > > '172.16.63.10',
> > > > '/opt/PostgreSQL/8.3/data')"
> > > > 2009-04-06 18:28:33 DEBUG: pid 25867:
> exec_recovery:
> > > start recovery
> > > > 2009-04-06 18:28:33 ERROR: pid 25867:
> exec_recovery:
> > > pgpool_recovery_pitr
> > > > command failed at 2nd stage
> > > > 2009-04-06 18:28:33 DEBUG: pid 25867:
> exec_recovery:
> > > finish recovery
> > > > 2009-04-06 18:28:33 DEBUG: pid 25867:
> pcp_child:
> > > received PCP packet type
> > > > of service 'X'
> > > > 2009-04-06 18:28:33 DEBUG: pid 25867:
> pcp_child:
> > > client disconnecting.
> > > > close connection
> > > >
> > > > Thanks!
> > > > Harold
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------
> > > >
> > > > Message: 3
> > > > Date: Mon, 6 Apr 2009 16:42:20 -0700 (PDT)
> > > > From: Harold Lim <rold_50 at yahoo.com>
> > > > Subject: [Pgpool-general] Online recovery +
> WAL
> > > archiving
> > > > To: pgpool-general at pgfoundry.org
> > > > Message-ID:
> > >
> <858505.36310.qm at web51010.mail.re2.yahoo.com>
> > > > Content-Type: text/plain; charset=us-ascii
> > > >
> > > >
> > > > Hi,
> > > >
> > > > I'm currently looking at the pgpool-ii
> > > beginner's guide.
> > > > I'm mainly interested in online recovery
> (e.g,
> > > dynamically adding a new
> > > > postgresql node).
> > > >
> > > > Do I have to enable wal archiving for all of
> my node?
> > > or just for my first
> > > > node?
> > > >
> > > >
> > > >
> > > > Thanks!,
> > > > Harold
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------
> > > >
> > > >
> _______________________________________________
> > > > Pgpool-general mailing list
> > > > Pgpool-general at pgfoundry.org
> > > >
> http://pgfoundry.org/mailman/listinfo/pgpool-general
> > > >
> > > >
> > > > End of Pgpool-general Digest, Vol 53, Issue
> 5
> > > >
> *********************************************
> > > >
> >
> >
> >
> >