[pgpool-general: 1825] Re: [PGPOOL] online recovery fails due to "WAL ends before end of online backup"

Yugo Nagata nagata at sraoss.co.jp
Wed Jun 5 17:51:22 JST 2013


Dear Tina,

Coudl you please tell me what error message there are in pgpool log?
"check_postmaster_started: remote host start up did not finish in 180 sec." yet?

In addition, could you please check error message in backend log, too?
Does PostgreSQL fail to start up yet?

On Wed, 5 Jun 2013 08:16:59 +0000
Tina Wang (王冠婷) <Tina-Wang at quantatw.com> wrote:

> Dear Nagata,
> 
> Thanks for your suggestion. After commenting 
> " if [ $? -eq 0 ];then
>          rm $archdir/* < /dev/null
>  fi", error message "WAL ends before end of online backup" does not show again.
> However, the success rate of online recovery is still very low (<2/5), finding no clues yet.
> Do you have any suggestion?
> Thank you for your patience and considerations.
> 
> 
> Regards,
> Tina
> 
> -----Original Message-----
> From: Yugo Nagata [mailto:nagata at sraoss.co.jp] 
> Sent: Friday, May 31, 2013 2:54 PM
> To: Tina Wang (王冠婷)
> Cc: Kuo Huang (黃種國); Eric Fang (方俊雄); Tatsuo Ishii; pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 1782] Re: [PGPOOL] online recovery fails due to "WAL ends before end of online backup"
> 
> Dear Tina,
> 
> Thanks for your providing log files.
> 
> It seams that postgresql hadn't started up within recovery_timeout (180 sec.).
> 
> I saw your pgpool_remote_restart and found that wal archives are removed just after restarting postgres. 
> 
> > ssh -T -l postgres $DEST $INIT restart < /dev/null if [ $? -eq 0 
> > ];then
> >         rm $archdir/* < /dev/null
> > fi
> 
> However, postgresql takes long time to recover and restart if wal archive is large. Hence, too early removal of archives might be the reason of failure of starting up postgres.
> 
> On Wed, 29 May 2013 11:35:38 +0000
> Tina Wang (王冠婷) <Tina-Wang at quantatw.com> wrote:
> 
> > Dear Nagata,
> > 
> > Type of (id, image, lastUpdateTime, accountID) is (integer, bytes, string, integer).
> > 
> > Since the log files are too big to be received, please download them using the following links.
> > link of syslog-pgpool: 
> > https://docs.google.com/file/d/0B4-Lz8GD2FkBNnVEc3l1c0hHWFE/edit?usp=s
> > haring
> > link of syslog-postgresql: 
> > https://docs.google.com/file/d/0B4-Lz8GD2FkBTUFackpISVBvVW8/edit?usp=s
> > haring
> > 
> > If you have any question or suggestion please let us know. Thank you.
> > 
> > Regards,
> > Tina
> > 
> > -----Original Message-----
> > From: Yugo Nagata [mailto:nagata at sraoss.co.jp]
> > Sent: Tuesday, May 28, 2013 3:07 PM
> > To: Tatsuo Ishii
> > Cc: Tina Wang (王冠婷); Kuo Huang (黃種國); Eric Fang (方俊雄); 
> > pgpool-general at pgpool.net
> > Subject: Re: [pgpool-general: 1782] Re: [PGPOOL] online recovery fails due to "WAL ends before end of online backup"
> > 
> > Dear Tina,
> > 
> > I'll investigate the problem.
> > 
> > Could you please provide the following information?
> >  - What mode are you using pgpool in? (master-slave/replication)
> >  - pgpool.conf, pgpool_remote_start script, recovery scripts
> >  - Sample of queries and table definition?
> > 
> > On Tue, 28 May 2013 15:31:42 +0900 (JST) Tatsuo Ishii <ishii at postgresql.org> wrote:
> > 
> > > Dear Tina,
> > > 
> > > Thanks for testing. It seems there must be a problem with watchdog. 
> > > Nagata is in charge of watchdog and he will make some investigation.
> > > --
> > > Tatsuo Ishii
> > > SRA OSS, Inc. Japan
> > > English: http://www.sraoss.co.jp/index_en.php
> > > Japanese: http://www.sraoss.co.jp
> > > 
> > > > Dear Ishii,
> > > > 
> > > > Thank you for reply so quickly.
> > > > The problem does not happen if we disable watchdog. Online recovery can be done successfully.
> > > > We are wondering why online recovery can be done successfully if watchdog is off. Would you please let us know the cause?
> > > > In our environment, we still need to set watchdog on. Do you plan to solve this issue? If yes, when the patch/version will be available?
> > > > Thank you.
> > > > 
> > > > Regards,
> > > > Tina
> > > > 
> > > > -----Original Message-----
> > > > From: Tatsuo Ishii [mailto:ishii at postgresql.org]
> > > > Sent: Tuesday, May 28, 2013 7:22 AM
> > > > To: Tina Wang (王冠婷)
> > > > Cc: pgpool-general at pgpool.net; Eric Fang (方俊雄); Kuo Huang (黃種國)
> > > > Subject: Re: [PGPOOL] online recovery fails due to "WAL ends before end of online backup"
> > > > 
> > > > Does the problem happen even if you disable watchdog?
> > > > --
> > > > Tatsuo Ishii
> > > > SRA OSS, Inc. Japan
> > > > English: http://www.sraoss.co.jp/index_en.php
> > > > Japanese: http://www.sraoss.co.jp
> > > > 
> > > >> Dear pgpool developers,
> > > >> 
> > > >> I encounter an issue while doing online recovery. I try to solve the problem but still have no idea about it. Recovery usually fails in the step: remote restart database server. It displays “FATAL: WAL ends before end of online backup. HINT: Online backup started with pg_start_backup() must be ended with pg_stop_backup(), and all WAL up to that point must be available at recovery.”
> > > >> 
> > > >> Software:
> > > >> OS: Ubuntu 12.04.2 LTS/Server/precise pgpool version: 
> > > >> pgpool-II-3.2.3 postgresql version: postgresql_9.2.2
> > > >> 
> > > >> Environment:
> > > >> 2 pgpools: PG0 and PG1 (with watchdog on)
> > > >> 2 databases: DB0 and DB1
> > > >> 
> > > >> Reproduce steps:
> > > >> 
> > > >> 1.      Update data via VIP. (23.7kB size JPEG pictures were kept being updated to the database via VIP. And there are 10 requests in one second.)
> > > >> 
> > > >> 2.      Stop DB1 service.
> > > >> 
> > > >> 3.      Use pcp_recovery_node to do recovery on PG1.
> > > >> 
> > > >> 4.      Recovery done.
> > > >> 
> > > >> Note that recovery usually fails at remote restart. It is interesting that the success rate of online recovery is higher when updating DB with only text than with pictures.
> > > >> 
> > > >> Regards,
> > > >> Tina
> > > > 
> > > _______________________________________________
> > > pgpool-general mailing list
> > > pgpool-general at pgpool.net
> > > http://www.pgpool.net/mailman/listinfo/pgpool-general
> > 
> > 
> > --
> > Yugo Nagata <nagata at sraoss.co.jp>
> > 
> 
> 
> --
> Yugo Nagata <nagata at sraoss.co.jp>
> 


-- 
Yugo Nagata <nagata at sraoss.co.jp>


More information about the pgpool-general mailing list