Thread: RFH (req for HELP!) -- Putting the pieces back together...

Hi folks,

Sorry for the crosspost, but I'm new to the lists & wasn't sure which was
more appropriate.

The short story is I'm trying to rescue at least some data from a PG 6.5.1
database that's gone belly up.  I've got a recent (day before I was called
in to help rescue) "core" in data/base/mydb, a pg_log that's almost 1 GB
(the databases themselves, 2 or 3 of them, are only 1-5MB of data), and a PG
engine that thinks no databases exist (\L says "no databases") even though I
can connect to mydb.  Once connected to mydb (through psql), if I "select *
from known_table;", it returns 0 records.  The "known_table" file is still
about 5MB, I'm assuming the data is still actually in there?

Is there *any* hope of recovering the data from those tables?  The last
pg_dump was apparently the one I demonstrated over a week ago.  I was
working on getting them automated and the dumps added to my backup cycle,
but I assumed (yeah, I know <sigh>) that in the meantime the developers were
still manually doing pg_dump's daily or so......  >:-P

Thanks for any pointers, and Happy Holidays (yes, I did put this aside long
enough this weekend to finish decorating the tree and spend some time with
my little girl <g>).

 - Brian Bender
   Part-time (usually the *wrong* times) sysadmin
   Vocollect, Inc.
   Pittsburgh, PA, USA

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Hi folks,
>
> Sorry for the crosspost, but I'm new to the lists & wasn't sure which was
> more appropriate.
>
> The short story is I'm trying to rescue at least some data from a PG 6.5.1
> database that's gone belly up.  I've got a recent (day before I was called
> in to help rescue) "core" in data/base/mydb, a pg_log that's almost 1 GB
> (the databases themselves, 2 or 3 of them, are only 1-5MB of data), and a PG
> engine that thinks no databases exist (\L says "no databases") even though I
> can connect to mydb.  Once connected to mydb (through psql), if I "select *
> from known_table;", it returns 0 records.  The "known_table" file is still
> about 5MB, I'm assuming the data is still actually in there?

Yes, you can.  My recommendation is to first backup all the files under
pgsql.  Then, use pg_upgrade on your old pg_dump.  pg_upgrade can use
the schema from the old pg_dump output, and places your current data
files into place after the upgrade.  The script is intended to be used
only when moving from release to release, but it can be used to fix
problems where your system tables are messed up.

Another idea is to insert into pg_database and see if that will help you
pg_dump.

You may need to edit pg_upgrade so it will run. It was disabled for
6.5.* because file formats changed from 6.4.*.

You may need to phone me if you need more help.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026