Thread: server crash, need to restore DB functionality

Hello,

we run

7.4.7

on a Redhat RHEL server.


Today that was a severe server crash and NOC seems to have repeatedly
attempted a reboot without properly unmounting  the file systems.

I am getting errors such as the following

Apr 29 13:15:34 cp postgres[4961]: [106-1] ERROR:  could not open relation "users_pkey": Permission denied
Apr 29 13:15:34 cp postgres[4961]: [107-1] ERROR:  could not open relation "users_pkey": Permission denied
Apr 29 13:15:34 cp postgres[4957]: [113-1] ERROR:  could not open relation "users_pkey": Permission denied

when attempting to have my application connect to the DB

Please let me know what sort of procedure are available to us
to have such functionality restored.

We do have backups.

Being it is one day old, are we going to loose information when attempting
to do a restore?

Any other procedures to save the data other than a restore?


Thanks.

On Tue, Apr 29, 2008 at 11:18 AM, e t <albunix@gmail.com> wrote:
> Hello,
>
> we run
>
> 7.4.7
>
> on a Redhat RHEL server.

Two points.
1: The 7.4.x series is pretty old.  You should consider upgrading to a
newer version when you have time.  8.3.1 is stable but a little new.
8.2.7 is very stable and much faster than 7.4.
2: 7.4 series is up to 7.4.19.  You may be missing 3 years of updates,
unless you are using RedHat's version and they are back porting fixes
to 7.4.7.  I didn't think they did that, but Tom Lane would be a good
person to ask about that.

> Today that was a severe server crash and NOC seems to have repeatedly
> attempted a reboot without properly unmounting  the file systems.

The machine that gives them a banana when they push a button probably
wasn't working quite right :)

>  I am getting errors such as the following
>
> Apr 29 13:15:34 cp postgres[4961]: [106-1] ERROR:  could not open relation
> "users_pkey": Permission denied
> Apr 29 13:15:34 cp postgres[4961]: [107-1] ERROR:  could not open relation
> "users_pkey": Permission denied
>  Apr 29 13:15:34 cp postgres[4957]: [113-1] ERROR:  could not open relation
> "users_pkey": Permission denied
>
> when attempting to have my application connect to the DB
>
> Please let me know what sort of procedure are available to us
>  to have such functionality restored.

you should restore from backup if possible.

> We do have backups.

pg_dump backups (good thing) or file system backups (maybe good,
likely not coherent)

> Being it is one day old, are we going to loose information when attempting
> to do a restore?

Well, if you can't connect, then no.  OTOH, if you can still connect
and you've been updating data, then yes.  you'll lost everything you
had since your last backup.

You may be able to fix things.  Trying reindexing the failing table / indexes.

On Tue, Apr 29, 2008 at 11:18 AM, e t <albunix@gmail.com> wrote:
> Apr 29 13:15:34 cp postgres[4961]: [107-1] ERROR:  could not open relation
> "users_pkey": Permission denied

Wait, permission denied?  Sounds like the network monkeys in your NOC
are doing things they should not be doing, like changing permissions.
Try changing the perms of all the stuff under the pgdata directory to
the pg user (usually postgres on RHEL) and see if that helps.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> 2: 7.4 series is up to 7.4.19.  You may be missing 3 years of updates,
> unless you are using RedHat's version and they are back porting fixes
> to 7.4.7.  I didn't think they did that, but Tom Lane would be a good
> person to ask about that.

Nope; Red Hat is currently shipping 7.4.19 in RHEL-4.

            regards, tom lane

All,

thanks for your kind reply.

We are indeed running rhel 2 es, as our application requires such.

I was able to correct this, including permission error, by

1)getting a right after the crash dump of the DB
2)dropping the DB
3)re-installing the RPM's via force option
4)re-importing the DB

I am not aware of how one re-indexes the table(s)

Is there any options or what's the syntax to do so,
in case we ever will need such in the future.

Thank you.

e t wrote:
> All,
>
> thanks for your kind reply.
>
> We are indeed running rhel 2 es, as our application requires such.
>
> I was able to correct this, including permission error, by
>
> 1)getting a right after the crash dump of the DB
> 2)dropping the DB
> 3)re-installing the RPM's via force option
> 4)re-importing the DB
>
> I am not aware of how one re-indexes the table(s)
If you imported from a dump, the indexes will be updated as part of the
import.  However, you would probably be well served to run a ANALYZE to
update statistics information.  Otherwise the query planner might come
out with sub-optimal plans...

I'm assuming that in the statement above you meant that you removed the
cluster and restored the entire cluster.  If you just did the DB, it's
possible that other things might not be so well off....especially since
you don't know the root cause of the problem.

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Ask me about expert on-site and public enrollment PostgreSQL training delivered worldwide!