Thread: little PITR annoyance

little PITR annoyance

From
ohp@pyrenet.fr
Date:
Hi all,

I've benn biten twice by this one:
I enabled archiving on both my pgsql servers.
the archive command was a simple gzip on an nfs mounted dir.
Now the nfs server crashed.
Both wal space filled up until postgresql shuts itself down because of no
more space for WAL.
That perfectly normal and expected.

What I did'nt expect and don't understand is that postgresql refused to
start up after the nfs server was up and running until I added some more
space on the WAL fs although if it had started archiving ,
space would have been there .

I wonder if archiving could start before postgresql at least to make a
little room for database engine to really start.

Hope I'm clear...

Thank you for your attention

Best Regards

-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Re: little PITR annoyance

From
"Simon Riggs"
Date:
On Thu, 2007-06-07 at 17:01 +0200, ohp@pyrenet.fr wrote:

> I've benn biten twice by this one:
> I enabled archiving on both my pgsql servers.
> the archive command was a simple gzip on an nfs mounted dir.
> Now the nfs server crashed.
> Both wal space filled up until postgresql shuts itself down because of no
> more space for WAL.
> That perfectly normal and expected.
> 
> What I did'nt expect and don't understand is that postgresql refused to
> start up after the nfs server was up and running until I added some more
> space on the WAL fs although if it had started archiving ,
> space would have been there .
> 
> I wonder if archiving could start before postgresql at least to make a
> little room for database engine to really start.

gzip write a new file and then deletes the old, doesn't it? So it must
require space on the xlog drive.

Does it still fail if you avoid using gzip and just use scp?

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com




Re: little PITR annoyance

From
ohp@pyrenet.fr
Date:
Hi Simon,
On Thu, 7 Jun 2007, Simon Riggs wrote:

> Date: Thu, 07 Jun 2007 23:10:06 +0100
> From: Simon Riggs <simon@2ndquadrant.com>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] little PITR annoyance
>
> On Thu, 2007-06-07 at 17:01 +0200, ohp@pyrenet.fr wrote:
>
> > I've benn biten twice by this one:
> > I enabled archiving on both my pgsql servers.
> > the archive command was a simple gzip on an nfs mounted dir.
> > Now the nfs server crashed.
> > Both wal space filled up until postgresql shuts itself down because of no
> > more space for WAL.
> > That perfectly normal and expected.
> >
> > What I did'nt expect and don't understand is that postgresql refused to
> > start up after the nfs server was up and running until I added some more
> > space on the WAL fs although if it had started archiving ,
> > space would have been there .
> >
> > I wonder if archiving could start before postgresql at least to make a
> > little room for database engine to really start.
>
> gzip write a new file and then deletes the old, doesn't it? So it must
> require space on the xlog drive.
>
> Does it still fail if you avoid using gzip and just use scp?
The problem is not with gzip or scp, it is that postmaster refuses to
start because wal FS is full.
My questions was: why don't we start the archiving *BEFORE* postmaster to
make room.
Another related question would be: What if postmaster instead of shutting
down in that case, refused connection until archiving is back?

Many thanks
>
>

-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Re: little PITR annoyance

From
"Simon Riggs"
Date:
Hi Olivier,

On Fri, 2007-06-08 at 11:41 +0200, ohp@pyrenet.fr wrote: 
> The problem is not with gzip or scp, it is that postmaster refuses to
> start because wal FS is full.

> My questions was: why don't we start the archiving *BEFORE* postmaster to
> make room.

The archiver is executed from the postmaster, so thats not possible.

We could investigate where best to put some code, but it wouldn't be
executed very frequently.

Why not just execute the archive_command in a script, replacing
the .ready with .done files in archive_status directory when its
processed?

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com




Re: little PITR annoyance

From
ohp@pyrenet.fr
Date:
Hi Simon,
Sorry for replying so late...
On Fri, 8 Jun 2007, Simon Riggs wrote:

> Date: Fri, 08 Jun 2007 20:16:35 +0100
> From: Simon Riggs <simon@2ndquadrant.com>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] little PITR annoyance
>
> Hi Olivier,
>
> On Fri, 2007-06-08 at 11:41 +0200, ohp@pyrenet.fr wrote:
> > The problem is not with gzip or scp, it is that postmaster refuses to
> > start because wal FS is full.
>
> > My questions was: why don't we start the archiving *BEFORE* postmaster to
> > make room.
>
> The archiver is executed from the postmaster, so thats not possible.
>
I'm aware of that, my point is maybe the archiver doesn't need postmaster
to be fully operational  (responding to db requests) to be started
> We could investigate where best to put some code, but it wouldn't be
> executed very frequently.
I agree, OTOH, the more PITR is used on big busy db to more this may
happend.
>
> Why not just execute the archive_command in a script, replacing
> the .ready with .done files in archive_status directory when its
> processed?
>
Sure, but if *I* can do it, why can't the system?

What do you think,
>
Best regards,
-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Re: little PITR annoyance

From
"Simon Riggs"
Date:
On Sun, 2007-06-10 at 20:48 +0200, ohp@pyrenet.fr wrote:
> >
> > > My questions was: why don't we start the archiving *BEFORE* postmaster to
> > > make room.
> >
> > The archiver is executed from the postmaster, so thats not possible.
> >
> I'm aware of that, my point is maybe the archiver doesn't need postmaster
> to be fully operational  (responding to db requests) to be started
> > We could investigate where best to put some code, but it wouldn't be
> > executed very frequently.
> I agree, OTOH, the more PITR is used on big busy db to more this may
> happend.
> >
> > Why not just execute the archive_command in a script, replacing
> > the .ready with .done files in archive_status directory when its
> > processed?
> >
> Sure, but if *I* can do it, why can't the system?
> 
> What do you think,

Just looked at the code. Does seem possible to start archiver earlier -
it has no hooks into anything else and doesn't need transactions. 

Starting archiver earlier would not be the only change required, since
recovery could be very short. That will take some thought on how to
resolve.

I have other things pressing on me now, but I'll add this to my todo,
though I'll be relying on you to test it when I get round to it.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com




Re: little PITR annoyance

From
ohp@pyrenet.fr
Date:
Hi Simon,

I'll be glad to test it for you when you're ready!
Thanks for looking at this issue.

Best regards,
On Sun, 10 Jun 2007, Simon Riggs wrote:

> Date: Sun, 10 Jun 2007 23:55:32 +0100
> From: Simon Riggs <simon@2ndquadrant.com>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] little PITR annoyance
>
> On Sun, 2007-06-10 at 20:48 +0200, ohp@pyrenet.fr wrote:
> > >
> > > > My questions was: why don't we start the archiving *BEFORE* postmaster to
> > > > make room.
> > >
> > > The archiver is executed from the postmaster, so thats not possible.
> > >
> > I'm aware of that, my point is maybe the archiver doesn't need postmaster
> > to be fully operational  (responding to db requests) to be started
> > > We could investigate where best to put some code, but it wouldn't be
> > > executed very frequently.
> > I agree, OTOH, the more PITR is used on big busy db to more this may
> > happend.
> > >
> > > Why not just execute the archive_command in a script, replacing
> > > the .ready with .done files in archive_status directory when its
> > > processed?
> > >
> > Sure, but if *I* can do it, why can't the system?
> >
> > What do you think,
>
> Just looked at the code. Does seem possible to start archiver earlier -
> it has no hooks into anything else and doesn't need transactions.
>
> Starting archiver earlier would not be the only change required, since
> recovery could be very short. That will take some thought on how to
> resolve.
>
> I have other things pressing on me now, but I'll add this to my todo,
> though I'll be relying on you to test it when I get round to it.
>
>

-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Re: little PITR annoyance

From
Jim Nasby
Date:
How about adding to the general TODO in case someone finds time  
before Simon?

On Jun 10, 2007, at 5:55 PM, Simon Riggs wrote:
> On Sun, 2007-06-10 at 20:48 +0200, ohp@pyrenet.fr wrote:
>>>
>>>> My questions was: why don't we start the archiving *BEFORE*  
>>>> postmaster to
>>>> make room.
>>>
>>> The archiver is executed from the postmaster, so thats not possible.
>>>
>> I'm aware of that, my point is maybe the archiver doesn't need  
>> postmaster
>> to be fully operational  (responding to db requests) to be started
>>> We could investigate where best to put some code, but it wouldn't be
>>> executed very frequently.
>> I agree, OTOH, the more PITR is used on big busy db to more this may
>> happend.
>>>
>>> Why not just execute the archive_command in a script, replacing
>>> the .ready with .done files in archive_status directory when its
>>> processed?
>>>
>> Sure, but if *I* can do it, why can't the system?
>>
>> What do you think,
>
> Just looked at the code. Does seem possible to start archiver  
> earlier -
> it has no hooks into anything else and doesn't need transactions.
>
> Starting archiver earlier would not be the only change required, since
> recovery could be very short. That will take some thought on how to
> resolve.
>
> I have other things pressing on me now, but I'll add this to my todo,
> though I'll be relying on you to test it when I get round to it.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




Re: little PITR annoyance

From
Bruce Momjian
Date:
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Simon Riggs wrote:
> On Sun, 2007-06-10 at 20:48 +0200, ohp@pyrenet.fr wrote:
> > >
> > > > My questions was: why don't we start the archiving *BEFORE* postmaster to
> > > > make room.
> > >
> > > The archiver is executed from the postmaster, so thats not possible.
> > >
> > I'm aware of that, my point is maybe the archiver doesn't need postmaster
> > to be fully operational  (responding to db requests) to be started
> > > We could investigate where best to put some code, but it wouldn't be
> > > executed very frequently.
> > I agree, OTOH, the more PITR is used on big busy db to more this may
> > happend.
> > >
> > > Why not just execute the archive_command in a script, replacing
> > > the .ready with .done files in archive_status directory when its
> > > processed?
> > >
> > Sure, but if *I* can do it, why can't the system?
> > 
> > What do you think,
> 
> Just looked at the code. Does seem possible to start archiver earlier -
> it has no hooks into anything else and doesn't need transactions. 
> 
> Starting archiver earlier would not be the only change required, since
> recovery could be very short. That will take some thought on how to
> resolve.
> 
> I have other things pressing on me now, but I'll add this to my todo,
> though I'll be relying on you to test it when I get round to it.
> 
> -- 
>   Simon Riggs             
>   EnterpriseDB   http://www.enterprisedb.com
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +