RE: Help with Database Recovery

Tom and others: Thanks for the guidance. We rebuilt and restored, and will
just live with an earlier version of the data, sadder but wiser. It was
good to (a) learn about pg_log and (b) realize that pg_dump and pg_dumpall
are our good friends and we should use them.

Today I also learned that starting a subject line with "Help" diverts a
message from going to the list directly. I see why this is a good idea.

Again, thanks, all.

Cheers!
--
David Hancock | dhancock(at)arinc(dot)com | 410-266-4384

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, November 30, 2000 2:24 PM
To: Hancock, David (DHANCOCK)
Cc: 'pgsql-general(at)postgresql(dot)org'
Subject: Re: [GENERAL] Help with Database Recovery

"Hancock, David (DHANCOCK)" <DHANCOCK(at)arinc(dot)com> writes:
> Sorry I didn't give more detail. OS is Linux 2.2 kernel, PostgreSQL is
> 6.5.3. The problem is that I copied the .../base/* directories elsewhere
in
> preparation for making base a symlink to a different filesystem with more
> space. I then screwed up and removed everything in /var/lib/pgsql, not
just
> the base directories. This necessitated a reinstall of PostgreSQL.

> I know, I know ... it was a very stupid maneuver on my part, but it's a
> strange feeling to know that I've GOT the database files, I just can't use
> 'em. Yet.

Unfortunately, you've only got *part* of the database. The above
maneuver destroyed your pg_log file, which is essential. Without it,
you've got a lot of tuples but you don't know which ones are valid.

If you did a VACUUM just before all this, then there's a reasonable
chance that the tuples you have left are mostly just valid ones.
Otherwise I'd say it's hopeless. In any case you will not be able
to reconstruct data that you can trust except after painstaking
manual examination.

How far back was your last regular whole-file-system backup? Restoring
all of /var/lib/pgsql off that is likely to be your best shot at getting
to a state that's somewhat trustworthy.

regards, tom lane