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

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?
>
> Good luck
> --
>    Richard Huxton
>    Archonet Ltd
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend

=====
Best wishes
Z C Wang





____________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html