Thread: URGENT pg_xlog full impossible to restart ...
Hi, I have my database stoping by itself during a vacuum full analyse; Impossible to restart it ... I have this message : Jul 17 22:03:09 stats-ugc postgres[7239]: [2-1] LOG: database system was shut down at 2004-07-17 20:53:06 CEST Jul 17 22:03:09 stats-ugc postgres[7239]: [3-1] LOG: checkpoint record is at 400/49FFE4B0 Jul 17 22:03:09 stats-ugc postgres[7239]: [4-1] LOG: redo record is at 400/49FFE4B0; undo record is at 0/0; shutdown TRUE Jul 17 22:03:09 stats-ugc postgres[7239]: [5-1] LOG: next transaction ID: 499350; next OID: 1874767379 Jul 17 22:03:09 stats-ugc postgres[7239]: [6-1] PANIC: could not write to file "/usr/local/pgsql/data/pg_xlog/xlogtemp.7239": No space left on device Jul 17 22:03:09 stats-ugc postgres[7236]: [2-1] LOG: startup process (PID 7239) was terminated by signal 6 Jul 17 22:03:09 stats-ugc postgres[7236]: [3-1] LOG: aborting startup due to startup process failure How to solve this ??? My pg_xlog partition is full ... :o( Thanks ! -- Bill Footcow
=?iso-8859-15?q?Herv=E9_Piedvache?= <footcow@noos.fr> writes:
> How to solve this ??? My pg_xlog partition is full ... :o(
... so free up some space ...
regards, tom lane
Tom, Le samedi 17 Juillet 2004 23:06, Tom Lane a écrit : > =?iso-8859-15?q?Herv=E9_Piedvache?= <footcow@noos.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 ?? So pg_resetxlog -f /usr/local/pgsql/data ?? Any risk ? regards, -- Bill Footcow
=?iso-8859-1?q?Herv=E9_Piedvache?= <footcow@noos.fr> writes:
> Tom,
> Le samedi 17 Juillet 2004 23:06, Tom Lane a �crit :
>> =?iso-8859-15?q?Herv=E9_Piedvache?= <footcow@noos.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
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@noos.fr> writes: > > Tom, > > > > Le samedi 17 Juillet 2004 23:06, Tom Lane a écrit : > >> =?iso-8859-15?q?Hervé_Piedvache?= <footcow@noos.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@postgresql.org so that your > message can get through to the mailing list cleanly -- Bill Footcow
Did you have a long running transaction? That's usually the cause of this kind of thing. 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@noos.fr> writes: > > > Tom, > > > > > > Le samedi 17 Juillet 2004 23:06, Tom Lane a écrit : > > >> =?iso-8859-15?q?Hervé_Piedvache?= <footcow@noos.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@postgresql.org so that your > > message can get through to the mailing list cleanly
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@noos.fr> writes: > > > > Tom, > > > > > > > > Le samedi 17 Juillet 2004 23:06, Tom Lane a ?crit : > > > >> =?iso-8859-15?q?Herv?_Piedvache?= <footcow@noos.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@postgresql.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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
=?iso-8859-15?q?Herv=E9_Piedvache?= <footcow@noos.fr> writes:
> 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 ??
> And I was only creating an index ...
This is a known issue in 7.3 and 7.4: a large CREATE INDEX holds shared
buffer locks for unreasonable amounts of time, which can block
CHECKPOINT and thereby delay recycling of WAL files. It's fixed for
7.5, but I don't know of any good way to avoid the problem in the
earlier releases. See discussions back in May --- the fix went in here:
2004-06-02 13:28 tgl
* src/: backend/access/nbtree/nbtpage.c,
backend/access/nbtree/nbtree.c, backend/access/nbtree/nbtsort.c,
backend/access/nbtree/nbtxlog.c, backend/storage/smgr/md.c,
backend/storage/smgr/smgr.c, include/access/nbtree.h,
include/storage/smgr.h: Adjust btree index build to not use shared
buffers, thereby avoiding the locking conflict against concurrent
CHECKPOINT that was discussed a few weeks ago. Also, if not using
WAL archiving (which is always true ATM but won't be if PITR makes
it into this release), there's no need to WAL-log the index build
process; it's sufficient to force-fsync the completed index before
commit. This seems to gain about a factor of 2 in my tests, which
is consistent with writing half as much data. I did not try it
with WAL on a separate drive though --- probably the gain would be
a lot less in that scenario.
regards, tom lane