Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8A5672E33D9 for ; Fri, 25 Jan 2008 15:10:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41502-07-3 for ; Fri, 25 Jan 2008 15:10:19 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9E7852E01D4 for ; Fri, 25 Jan 2008 15:10:07 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m0PJA6c6003758; Fri, 25 Jan 2008 14:10:06 -0500 (EST) To: Steven Rosenstein cc: pgsql-admin@postgresql.org Subject: Re: Recovering a database in danger of transaction wrap-around In-reply-to: References: Comments: In-reply-to Steven Rosenstein message dated "Fri, 25 Jan 2008 13:53:57 -0500" Date: Fri, 25 Jan 2008 14:10:06 -0500 Message-ID: <3757.1201288206@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/247 X-Sequence-Number: 28021 Steven Rosenstein writes: > I did as instructed, and fired up the standalone backend. I then started > VACUUM. About four days later, the standalone backend terminated with the > message: > WARNING: terminating connection because of crash of another server process > DETAIL: The postmaster has commanded this server process to roll back the > current transaction and exit, because another server process exited > abnormally and possibly corrupted shared memory. > HINT: In a moment you should be able to reconnect to the database and > repeat your command. > CONTEXT: writing block 465 of relation 1663/16384/863912 Ugh. Something sent the standalone backend a SIGQUIT signal. You need to find out what did that. > I used lsof to monitor which files the backend was actually working on. It > took two of the four days for it to vacuum a single table with 43 > one-gigabyte extents. I have one table with over 300 extents. I'm looking > at a vacuum process which can ultimately take weeks (if not months) to > complete. Yipes. You are just using plain VACUUM, right, not VACUUM FULL? Have you checked that vacuum_cost_delay isn't enabled? > Bottom line. Is there *any* way of faking out the 1 million transaction > limit which prevents the postmaster from running, long enough for me to use > pg_dump to rescue the data? In 8.1 those limits are all hard-wired; you'd need to modify SetTransactionIdLimit() in src/backend/access/transam/varsup.c and recompile. Might be worth doing, if you think these tables have been bloated by a complete lack of vacuuming. regards, tom lane