Re: after using pg_resetxlog, db lost

zhicheng wang wrote:
> Dear Richard
> 
> you have pointed me to a very good direction.
> under /var/lib/pgsql/data/base there three directoies:
> 
> 1
> 16975
> 4205811
> 
> i think that the first two are template0/1 and the
> third one is our db.
> 
> SELECT oid,datname FROM pg_database;
> 
> only listed template0/1 as you have preducted.
> 
> can you please help me with more details;
> 
> how do i Start a single backend (connected to
> template0/1) and reindex the 
> 
> thanks
> 
> cheng
> 
> 
> 
> 
> 
> 
>  --- Richard Huxton  wrote: >
> zhicheng wang wrote:
> 
>>>Dear Richard
>>>it was not a crash. we issued poweroff command,
>>
>>then
>>
>>>we used a dos floppy to upgrade bios on the
>>
>>fibrecard.
>>
>>> then when we reboot into the redhat AS3, the rhdb
>>>could not start.
>>>
>>>the log is attached.
>>
>>Thanks. The first line was:
>>
>>Jun  1 10:43:55 linux708 postgres[5537]: [30] LOG: 
>>database system 
>>shutdown was interrupted at 2004-05-28 16:32:08 BST
>>
>>This suggests the poweroff closed down your server
>>before PG had 
>>finished shutting down. You probably want to inspect
>>/var/log/messages 
>>at around this time and see if there is anything
>>else of value.
>>
>>This shouldn't happen, especially since you are
>>using RedHat's version 
>>of the database on their enterprise server -
>>probably worth logging a 
>>bug (unless there was a good reason why PG couldn't
>>shut down in a 
>>reasonable time).
>>
>>First thing we should do though is halt the database
>>and backup the 
>>/var/lib/pgsql/data/base directory (or wherever
>>PGDATA is). Once we have 
>>a backup we can restart the database and see what is
>>going on.
>>
>>
>>>after using pg_resetxlog, we cannot see our db,
>>
>>only
>>
>>>template0/1 listed by psql -l
>>
>>I'm puzzled why this should affect what databases
>>you can see. AFAIK the 
>>  pg_resetxlog utility should just affect
>>transactions that were in 
>>progress.
>>
>>Look in your /var/lib/pgsql/data/base directory (or
>>wherever PGDATA is) 
>>and you should see one directory for each database,
>>the name is the OID 
>>of that database. As the "postgres" user you should
>>be able to run the 
>>"oid2name" utility to display the names of each. Of
>>course, there might 
>>be problems.
>>
>>Finally, connect to template1 as user postgres and
>>run:
>>   SELECT oid,datname FROM pg_database;
>>Which will probably list the same databases as
>>oid2name/psql -l.
>>
>>If the directories are there, but the databases
>>aren't listed then there 
>>might be a damaged system-table index. To fix this:
>>1. Make sure your backups are still there.
>>2. Halt the database server
>>3. Start a single backend (connected to template0/1)
>>and reindex the 
>>database as described in the REINDEX command
>>reference.
>>
>>The docs are online and describe the required
>>settings quite well. Once 
>>reindexed, exit the single backend and restart the
>>database. Any better?

Follow the step-by-step instructions in the REINDEX section of the docs.
The manuals are online at http://www.postgresql.org/docs/ and you want 
to look in the "SQL Command reference" section.

No guarantee your data is OK though, I can't think why the system index 
should be damaged unless you were e.g. creating a new database as you 
were shutting down the machine.

-- 
   Richard Huxton
   Archonet Ltd