Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question: Recovery of tables

Question: Recovery of tables

From
Mark Lawrence
Date:
Hi,

I have a problem:

I started out with a 6.3 version system, and attempted an upgrade to
Debian's unstable release (maybe version 7.?), which unfortunately
appeared to have failed the dump/import to the new database format.

Realizing this was probably not what I wanted in the first place I tried
to go back to what I had originally but of course this is not straight
forward.

Unfortunately all I had backed up were files from the data/base/template1
directory downwards which means I have the raw table data but none of the
important files/information from the data/ directory. I can do a fresh
install, recreate the tables, copy the old template1 directory over the
new but all queries return empty (no) rows. Any ideas on how I can use the
original table files to regain my data?

Thanks in advance,
Mark.

p.s. Please respond to this email address.
--
 ,-_|\
/  .  \   Mark Lawrence
\_,-._/   nomad@null.net
     v

[QUESTION]: Recovery of tables

From
Mark Lawrence
Date:
Hi,

I have a problem:

Due to a mismanaged upgrade I'm in the situation of having a
$PGDATA/base/template1 directory without any of the files from the $PGDATA
directory itself.

Using the unix 'strings' command I can see the data inside the actual
template1 database table files, however I can't get to the data using
postgresql.

I have tried a fresh install, recreating the tables, copying the old
template1 directory over the new but all queries return empty (no) rows.
Any ideas on how I can use the original table files to regain my data?

Thanks in advance,

Mark.
--
 ,-_|\
/  .  \   Mark Lawrence
\_,-._/   nomad@null.net
     v

Rebuilding database from table files

From
Mark Lawrence
Date:
Help!

Is it possible to rebuild a database when you only have the files that
contain the table data?

There was no response when I posted this before. Does this mean that there
is no solution?

Mark.
--
 ,-_|\
/  .  \   Mark Lawrence
\_,-._/   nomad@null.net
     v


On Wed, 25 Apr 2001, Mark Lawrence wrote:

> Hi,
>
> I have a problem:
>
> Due to a mismanaged upgrade I'm in the situation of having a
> $PGDATA/base/template1 directory without any of the files from the $PGDATA
> directory itself.
>
> Using the unix 'strings' command I can see the data inside the actual
> template1 database table files, however I can't get to the data using
> postgresql.
>
> I have tried a fresh install, recreating the tables, copying the old
> template1 directory over the new but all queries return empty (no) rows.
> Any ideas on how I can use the original table files to regain my data?
>
> Thanks in advance,
>
> Mark.
> --
>  ,-_|\
> /  .  \   Mark Lawrence
> \_,-._/   nomad@null.net
>      v
>
>

Re: Rebuilding database from table files

From
Tom Lane
Date:
Mark Lawrence <nomad@null.net> writes:
> Is it possible to rebuild a database when you only have the files that
> contain the table data?

pg_log is critical data.  Without that, you're pretty well hosed,
because there's no way to distinguish live tuples from dead ones.

> There was no response when I posted this before. Does this mean that there
> is no solution?

No *good* solution, but if your back is really against the wall, you
might be able to salvage something with Bryan White's pg_check tool,
http://www.arcamax.com/pg_check/.  Expect to learn more than you
wanted to know about Postgres' internals, and to have to do a lot of
manual cross-validation to decide which tuples you think are good.

            regards, tom lane