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
Import Notes
Reply to msg id not found: 19544490.1090098427824.JavaMail.root@hercules
=?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
Import Notes
Reply to msg id not found: 19580496.1090102627661.JavaMail.root@hercules
Did you have a long running transaction? That's usually the cause of
this kind of thing.
Show quoted text
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-1000And 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-1000And 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