Re: after using pg_resetxlog, db lost - Mailing list pgsql-general

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 <dev@archonet.com> 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