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

Hi Richard Huxton

sorry to have bothered you with trivial things.

the reindex give these error:

backend> REINDEX DATABASE miamevice;
ERROR:  XLogFlush: request 0/BB4C3560 is not satisfied
--- flushed only to 0/20001D8
WARNING:  write error may be permanent: cannot write
block 29 for 4205811/1249
backend> \q;
ERROR:  parser: parse error at or near "\" at
character 1
backend> q\;
ERROR:  parser: parse error at or near "q" at
character 1
backend> LOG:  shutting down
PANIC:  XLogFlush: request 0/BB4C3560 is not satisfied
--- flushed only to 0/20001D8
Aborted


does this mean that we cannot recover our data?

cheng

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

=====
Best wishes
Z C Wang





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