Message-ID: <4EBBAF18.7080102@googlemail.com>
Date: Thu, 10 Nov 2011 12:01:44 +0100
From: Thorsten Strufe <strufe.pub@googlemail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.23) Gecko/20110922 Lightning/1.0b2 Thunderbird/3.1.15
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: lost tablespace
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi everybody,

sorry, noob here. So we've just started using postgre and right at the
start we had a hardware problem with a hard drive failing. We can still
start postgre but a tablespace for a db that we don't even need anymore
is lost. Now we would think that shouldn't be a problem, we could simply
drop that db. postgre, however, doesn't allow us to, but tells us to
vacuume the db first. Starting postgre singleuser and trying to run
vacuum still gives us the same trouble.

Now a naive question: is there any (sane) way to simply get postgre to
entirely forget about the broken db - and that's it (dumping the other
db and loading it back might not be the best solution, since it's rather
large..)?


TIA, best,

Thorsten

syslog-snippet:
2011-11-10 11:05:10 CET LOG:  incomplete startup packet
2011-11-10 11:05:10 CET LOG:  could not open tablespace directory
"pg_tblspc/17488/PG_9.0_201008051": No such file or directory
2011-11-10 11:05:10 CET LOG:  could not open tablespace directory
"pg_tblspc/18488/PG_9.0_201008051": No such file or directory
2011-11-10 11:05:10 CET LOG:  could not open tablespace directory
"pg_tblspc/18055/PG_9.0_201008051": No such file or directory
2011-11-10 11:05:10 CET WARNING:  database with OID 17546 must be
vacuumed within 999999 transactions
2011-11-10 11:05:10 CET HINT:  To avoid a database shutdown, execute a
database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared
transactions.
2011-11-10 11:05:10 CET LOG:  autovacuum launcher started
2011-11-10 11:05:10 CET LOG:  database system is ready to accept connections
2011-11-10 11:05:19 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18260": No such file or directory
2011-11-10 11:05:19 CET CONTEXT:  automatic vacuum of table
"postgres.public.user"
2011-11-10 11:05:19 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18276": No such file or directory
2011-11-10 11:05:19 CET CONTEXT:  automatic vacuum of table
"postgres.public.task"
2011-11-10 11:05:19 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18298": No such file or directory
2011-11-10 11:05:19 CET CONTEXT:  automatic vacuum of table
"postgres.public.queue"
2011-11-10 11:05:19 CET ERROR:  database is not accepting commands to
avoid wraparound data loss in database with OID 17546
2011-11-10 11:05:19 CET HINT:  Stop the postmaster and use a standalone
backend to vacuum that database.
        You might also need to commit or roll back old prepared
transactions.
2011-11-10 11:05:28 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18260": No such file or directory
2011-11-10 11:05:28 CET CONTEXT:  automatic vacuum of table
"postgres.public.user"
2011-11-10 11:05:28 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18276": No such file or directory
2011-11-10 11:05:28 CET CONTEXT:  automatic vacuum of table
"postgres.public.task"
2011-11-10 11:05:28 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18298": No such file or directory
2011-11-10 11:05:28 CET CONTEXT:  automatic vacuum of table
"postgres.public.queue"
2011-11-10 11:05:28 CET ERROR:  database is not accepting commands to
avoid wraparound data loss in database with OID 17546
2011-11-10 11:05:28 CET HINT:  Stop the postmaster and use a standalone
backend to vacuum that database.
        You might also need to commit or roll back old prepared
transactions.
2011-11-10 11:05:36 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18260": No such file or directory
2011-11-10 11:05:36 CET CONTEXT:  automatic vacuum of table
"postgres.public.user"
2011-11-10 11:05:36 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18276": No such file or directory
2011-11-10 11:05:36 CET CONTEXT:  automatic vacuum of table
"postgres.public.task"
2011-11-10 11:05:36 CET ERROR:  could not open file
"pg_tblspc/18055/PG_9.0_201008051/11874/18298": No such file or directory
2011-11-10 11:05:36 CET CONTEXT:  automatic vacuum of table
"postgres.public.queue"
2011-11-10 11:05:36 CET ERROR:  database is not accepting commands to
avoid wraparound data loss in database with OID 17546
2011-11-10 11:05:36 CET HINT:  Stop the postmaster and use a standalone
backend to vacuum that database.
        You might also need to commit or roll back old prepared
transactions.

...

etc.etc.etc