Thread: Almost all access to database hangs (fwd)
This is postgresql 7.0.3. Can someone help with clearing the problem
and saving his data?
------- Forwarded Messages - amalgamated
From: Sven Fischer <sepreh@gmx.de>
To: 92641@bugs.debian.org
Subject: Bug#92641: Similar problem
since yesterday my postgres won't take a generated database any more - it
just hangs with full utilisation of CPU power. The do.maintenance hangs,
also a pg_dump I tried hangs.
...
Simple select * queries on the database still work, but nothing else
.................................
>
> Vacuum each table of the affected database separately and see if you can
> isolate the problem.
The tables generated by me ones were unproblematic to vacuum.
> Quite likely you have a corrupted index - in which case drop and
> recreate the index.
How can I find out which indices I have? I am not a postgres professional,
and the \dt command in psql doesn't work anymore (it hangs also).
.................................
> However, if \dt hangs, the problem is probably in a system index;
> pg_class has two indexes: pg_class_oid_index and pg_class_relname_index.
I can do a select * on pg_class, but that's it. Anything else I tried
hangs.
> See if you can do a pg_dump of this database. If you can, backup the
> database itself, pg_dump it, delete it and recreate it from the dump.
I tried to dump it ( pg_dump -t pg_class etcs ), but it hangs immediately.
> If you can't do that, we'll have to try something else...
Seems like that. But I wonder, why. I update my system regularly
(unstable, that's fun ;-). Can a library conflict be the reason for it?
Because I worked with the db one day, and the other day it gave me this
hangs. The problem is, without dumping I would lose some data, and I
wouldn't like that... :-P
Greetings, Sven
--
Sven Fischer -- Mülheimer Str. 133, 51469 Bergisch Gladbach, Germany
Tel.: +49-(0)2202-458799 Fax.: +49-(0)89-244310437
sepreh@gmx.de
------- End of Forwarded Messages
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"My dear brothers, take note of this: Everyone should
be quick to listen, slow to speak and slow to become
angry, for man's anger does not bring about the
righteous life that God desires." James 1:19,20
"Oliver Elphick" <olly@lfix.co.uk> writes:
>> However, if \dt hangs, the problem is probably in a system index;
>> pg_class has two indexes: pg_class_oid_index and pg_class_relname_index.
> I can do a select * on pg_class, but that's it. Anything else I tried
> hangs.
Sounds like a job for REINDEX. I have not had to do this myself, but
I think the procedure is (a) stop postmaster, (b) start a standalone
backend with -O -P, (c) tell it to REINDEX DATABASE. Not sure about
that, especially about the switches to start the backend with. Read
the documentation.
regards, tom lane
Am Donnerstag, 5. April 2001 16:23 schrieb Tom Lane:
> Sounds like a job for REINDEX. I have not had to do this myself, but
> I think the procedure is (a) stop postmaster, (b) start a standalone
> backend with -O -P, (c) tell it to REINDEX DATABASE. Not sure about
> that, especially about the switches to start the backend with. Read
> the documentation.
Whoops, was too fast...read the documentation, but not completely *blush*
- a "reindex database etcs force" made everything work happily
again...phew.
Thanx a lot, Sven
--
Sven Fischer -- Mülheimer Str. 133, 51469 Bergisch Gladbach, Germany
Tel.: +49-(0)2202-458799 Fax.: +49-(0)89-244310437
sepreh@gmx.de
Am Donnerstag, 5. April 2001 16:23 schrieb Tom Lane:
> "Oliver Elphick" <olly@lfix.co.uk> writes:
> >> However, if \dt hangs, the problem is probably in a system index;
> >> pg_class has two indexes: pg_class_oid_index and
> >> pg_class_relname_index.
> >
> > I can do a select * on pg_class, but that's it. Anything else I tried
> > hangs.
>
> Sounds like a job for REINDEX. I have not had to do this myself, but
> I think the procedure is (a) stop postmaster, (b) start a standalone
> backend with -O -P, (c) tell it to REINDEX DATABASE. Not sure about
> that, especially about the switches to start the backend with. Read
> the documentation.
Read the documentation, made the reindex (reindex database etcs)...
...same symptoms.
Any additional things to reindex? Other hints?
Greetings, Sven
--
Sven Fischer -- Mülheimer Str. 133, 51469 Bergisch Gladbach, Germany
Tel.: +49-(0)2202-458799 Fax.: +49-(0)89-244310437
sepreh@gmx.de