Обсуждение: Postgresql 9.0.1 Corrupted

Database Version: Postgresql 9.0.1
OS: Linux rumman.dhaka 2.6.18-92.el5xen #1 SMP Tue Jun 10 19:55:54 EDT 2008 i686 i686 i386 GNU/Linux
Installed from: Source configure
Problem:
When I try to connect to postgres, I got -
psql: FATAL:  role "postgres" does not exist
In fact, it seems to me that no role exists.

Problem Cause:

For some reason, filesystem of my server was corrupted. Our system admin used CENTOS repair tools and it damaged the database files.
It created some of the directory as files. These are -
 - pg_notify
 - pg_xlog
Some files were not readable during start. These were - pg_internal and pg_control in the global directory.

At first, when I tried to start the server after CENTOS repair, I got the error message - 
DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION 0, but the server was compiled with PG_CONTROL_VERSION 903.

I created a fresh cluster and copied the pg_control and pg_internal from this cluster to the corrupted cluster.

Then, I tried to start and I got
FATAL:  could not open directory "pg_notify": Not a directory

This was created as files instead of directory. I removed it and created the directory.

At this point, the server started successfully.

Then I tried to connect using psql command.
But I got -
psql: FATAL:  role "postgres" does not exist

I tried to use CRAETEUSER. But it can't connect to db.


Please help.

On Jul 17, 2011, at 9:36 PM, AI Rumman wrote:

> Database Version: Postgresql 9.0.1
> OS: Linux rumman.dhaka 2.6.18-92.el5xen #1 SMP Tue Jun 10 19:55:54 EDT 2008 i686 i686 i386 GNU/Linux
> Installed from: Source configure
> Problem:
> When I try to connect to postgres, I got -
> psql: FATAL:  role "postgres" does not exist
> In fact, it seems to me that no role exists.
>
> Problem Cause:
>
> For some reason, filesystem of my server was corrupted. Our system admin used CENTOS repair tools and it damaged the
databasefiles. 
> It created some of the directory as files. These are -
>  - pg_notify
>  - pg_xlog
> Some files were not readable during start. These were - pg_internal and pg_control in the global directory.
>
> At first, when I tried to start the server after CENTOS repair, I got the error message -
> DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION 0, but the server was compiled with
PG_CONTROL_VERSION903. 

Your data is probably gone. Check when you last made backups.

But before you do *anything* else, create a full filesystem level copy of the entire database directory, so that when
youend up breaking things further by trying to recover it you'll have a "clean" copy of the corrupted database to start
overfrom. 

Cheers,
  Steve

On 18/07/11 12:36, AI Rumman wrote:

> At first, when I tried to start the server after CENTOS repair

What is "CENTOS repair" ?

Do you mean that you ran a file system check (fsck) and told it to
repair file system damage?

If so, you probably had file system corruption that damaged your data.
Check your hardware for overheating, power problems, failing hard
drives, and defective RAID controller memory.

> I created a fresh cluster and copied the pg_control and pg_internal from
> this cluster to the corrupted cluster.

Urk. Did you make a copy of the damaged database before you started
replacing internal files in it? If you didn't, you might have destroyed
your chance to recover your data.

How recent is your last backup?

--
Craig Ringer

Please reply to the mailing list, not just to me. Use the "reply all"
button if your email program doesn't support reply-to-list.

> Yes I have the backup of the point where the db was corrupted. But no
> backup.

Ouch. I hope your data isn't too important to you.

If your data is vital to you, you should hire somebody who is
experienced in data recovery from damaged PostgreSQL instances. You can
find a list of professional PostgreSQL consultants here:

http://www.postgresql.org/support/professional_support

If it's that important, you should also stop the machine and take a full
disk image. It's probably too late now anyway, but a disk image just
after an fsck problem can capture data that would otherwise be
unrecoverable.


If it's not so important that you want to pay someone for full-service
data recovery, there may still be steps you can try.

First, before you do ANYTHING else, put a copy of the original damaged
database, the one you made before you tried to fix anything, on an
external hard drive or burn it to a DVD if it fits. Keep it somewhere
separate where you cannot accidentally modify it over overwrite it.

Now rename the data directory you've been messing with. I wouldn't
delete it, just to be safe. Where it was, put a copy of the "backup" you
took just after you discovered the damage, before you tried to fix it.

If you try to start the server, you should now get the error:

"DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION
0, but the server was compiled with PG_CONTROL_VERSION 903."

Just like you did before.

At this point you might be able to take careful steps to repair the
database. I don't know much about this, so others may be able to advise
you better.

What I'd try is:
- restore the PG_VERSION file from a newly inited database.
- Make sure the permissions are set so that user "postgres" has
  read/write access to the entire database. Use something like
  "chmod ug=rwX,o= -R /path/to/data/directory" .
- sudo -u postgres bash
- cd to the datadir
- rm pg_notify && mkdir pg_notify
- rm pg_xlog && mkdir -p pg_xlog/archive_status
- pg_resetxlog /path/to/datadir
- Start Pg in single-user mode. On my Ubuntu system that's a command
  like this, but you'll have to adjust paths to suit your machine:

  /usr/lib/postgresql/8.4/bin/postgres --single -D `pwd` \
    -c config_file=/etc/postgresql/8.4/main/postgresql.conf \
    -c hba_file=/etc/postgresql/8.4/main/pg_hba.conf \
    postgres

... and see if it starts at all.

--
Craig Ringer