Re: URGENT pg_xlog full impossible to restart ...

Scott Marlowe wrote:
> Did you have a long running transaction? That's usually the cause of
> this kind of thing.

Long transactions to not cause the WAL files to not be recycled. It
must be something else.

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

>
> On Sat, 2004-07-17 at 17:37, Herv? Piedvache wrote:
> > OK it's running again many thanks ! :o)
> >
> > But Tom ... could you clearly explain me what is the parameter to set in the
> > postgresql.conf to never have my pg_xlog partition's going full ??
> >
> > I have 1.8 Gb dedicated to pg_xlog and I have set those options :
> > checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
> > checkpoint_timeout = 300 # range 30-3600, in seconds
> > #checkpoint_warning = 30 # 0 is off, in seconds
> > #commit_delay = 0 # range 0-100000, in microseconds
> > #commit_siblings = 5 # range 1-1000
> >
> > And I was only creating an index ...
> >
> > I'm a little disapointed !?
> >
> > regards,
> >
> > Le dimanche 18 Juillet 2004 00:19, Tom Lane a ?crit :
> > > =?iso-8859-1?q?Herv?_Piedvache?= <footcow(at)noos(dot)fr> writes:
> > > > Tom,
> > > >
> > > > Le samedi 17 Juillet 2004 23:06, Tom Lane a ?crit :
> > > >> =?iso-8859-15?q?Herv?_Piedvache?= <footcow(at)noos(dot)fr> writes:
> > > >>> How to solve this ??? My pg_xlog partition is full ... :o(
> > > >>
> > > >> ... so free up some space ...
> > > >
> > > > But my pg_xlog partition is dedicated to the WAL files ... so I can't
> > > > delete them ??
> > >
> > > Sure you can; all you need are the one(s) that the postmaster will try
> > > to read at restart. Since your log excerpt shows a clean shutdown, the
> > > only one you actually have to have is the one containing the shutdown
> > > checkpoint record, which is probably the one with the latest file mod
> > > time (but check against the checkpoint position shown in the log).
> > >
> > > > So pg_resetxlog -f /usr/local/pgsql/data ??
> > >
> > > That would work too.
> > >
> > > regards, tom lane
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > > message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073