Message-ID: <4EBC913E.7080803@ringerc.id.au>
Date: Fri, 11 Nov 2011 11:06:38 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20110930 Thunderbird/7.0.1
MIME-Version: 1.0
To: Thorsten Strufe <strufe.pub@googlemail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: lost tablespace
References: <4EBBAF18.7080102@googlemail.com>
In-Reply-To: <4EBBAF18.7080102@googlemail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

It'd help to know what Pg version you're running. Comments inline below.

On 11/10/2011 07:01 PM, Thorsten Strufe wrote:

> 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.

It'd be nice if PostgreSQL would `DROP DATABASE' successfully when there 
were missing files, just raising WARNINGs for those files. I'm not too 
shocked that it's not implemented though, as the files backing a 
database should never just go away, and if they do the database is 
considered severely damaged at best.

> 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..)?

No sane way I know of. A less-than-sane way is to mess with pg_catalog 
to get rid of the database, but that's unsafe at very best. I'd just 
dump the other databases, then re-initdb and reload them; that's the 
safest way by far.

> 2011-11-10 11:05:10 CET WARNING:  database with OID 17546 must be
> vacuumed within 999999 transactions

Now that's interesting. I wonder why it thinks the database is at risk 
of OID wraparound? It could genuinely be, or it might be confused 
because the files backing the database are missing.

Anyone have an opinion on whether it should be possible to drop a 
database that's hit or nearly hit OID wraparound w/o a vacuum first? I 
don't see any point vacuuming just to drop it, but this may be too much 
of a corner case to justify the testing a special case like this would 
require.

--
Craig Ringer