pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedlost tablespace
3+ messages / 3 participants
[nested] [flat]
* lost tablespace
@ 2011-11-10 11:01 Thorsten Strufe <strufe.pub@googlemail.com>
2011-11-10 16:02 ` Re: lost tablespace Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-11-11 03:06 ` Re: lost tablespace Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 2 replies; 3+ messages in thread
From: Thorsten Strufe @ 2011-11-10 11:01 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: lost tablespace
2011-11-10 11:01 lost tablespace Thorsten Strufe <strufe.pub@googlemail.com>
@ 2011-11-10 16:02 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2011-11-10 16:02 UTC (permalink / raw)
To: Thorsten Strufe <strufe.pub@googlemail.com>; pgsql-admin
Thorsten Strufe <strufe.pub@googlemail.com> wrote:
> 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..)?
I think that using pg_dump for the good databases is sane, and the
simplest solution. Another option would normally be one of the
trigger-based replication systems, like Slony (to reduce down time
to a matter of minutes or seconds); but the crippled state of the
cluster may make the hard.
http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
-Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: lost tablespace
2011-11-10 11:01 lost tablespace Thorsten Strufe <strufe.pub@googlemail.com>
@ 2011-11-11 03:06 ` Craig Ringer <ringerc@ringerc.id.au>
1 sibling, 0 replies; 3+ messages in thread
From: Craig Ringer @ 2011-11-11 03:06 UTC (permalink / raw)
To: Thorsten Strufe <strufe.pub@googlemail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-11-11 03:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-10 11:01 lost tablespace Thorsten Strufe <strufe.pub@googlemail.com>
2011-11-10 16:02 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-11-11 03:06 ` Craig Ringer <ringerc@ringerc.id.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox