Обсуждение: 7.4 - data recovery

  -- I'm re-sending this email to pgsql-admin mailing list
  -- to get more people involved (already sent to
  -- pgsql-general)
  -- Very sorry, if it's not well seen on this mailing list!

Hi,

I'm not postgresql experienced user, so please forgive me, if I missed
something obvious!

I have the following problem:
1. Partition with postgresql v.7.4 data has been damaged
2. No backups!
3. I've recovered some files and it looks to me they're from
$PGDATA/base/data/xxx (it looks this is the complete folder)
4. No other files! (no pg_clog, pg_xlog etc)

I've tried to deal with the issue in the following way:
1. I created new database (named test) on new 7.4 PG instance (it creates
/var/lib/postgresql/data/base/121339 folder for data)
2. I stopped the postgresql
3. Then I replaced the data in  /var/lib/postgresql/data/base/121339 with
my just recovered files
4. Then I started DB

When I connect to "test" database I see no tables in the schema. Something
went wrong! ;-(

I realise this is not a best way (correct one) to recover data from
damaged partition, but I have no idea how to proceed. It was my desperate
try to recover those data - does anyone can advice me how to proceed? Is
it possible to recover it at all?

To clarify the issue, this is what I've got now: (the result of ls -l
command in recovered directory)

-rwxr-xr-x  1 bart users   49152 2006-08-25 10:54 1247
-rwxr-xr-x  1 bart users  311296 2006-08-25 10:54 1249
-rwxr-xr-x  1 bart users  532480 2006-08-25 10:54 1255
-rwxr-xr-x  1 bart users   90112 2006-08-25 10:54 1259
-rwxr-xr-x  1 bart users   16384 2006-08-25 10:54 16384
-rwxr-xr-x  1 bart users   16384 2006-08-25 10:54 16386
-rwxr-xr-x  1 bart users       0 2006-08-25 10:54 16388
-rwxr-xr-x  1 bart users   32768 2006-08-25 10:54 16390
...
-rwxr-xr-x  1 bart users   16384 2006-08-25 10:54 46575
-rwxr-xr-x  1 bart users   69385 2006-08-25 10:54 pg_internal.init
-rwxr-xr-x  1 bart users       4 2006-08-25 10:54 PG_VERSION


Thanks in advice for any help!


best regards,
Bartek


--
______________________________________________________________________
Bartosz Belter           Network Research and Development
bart@man.poznan.pl       Poznan Supercomputing and Networking Center
+48 61 858 20 28         http://www.man.poznan.pl
______________________________________________________________________

Bartosz Belter wrote:
> 3. I've recovered some files and it looks to me they're from
> $PGDATA/base/data/xxx (it looks this is the complete folder)
> 4. No other files! (no pg_clog, pg_xlog etc)

If you don't have a clog, then all your transactions will appear
as "didn't happen yet", so your table data appears to be in the future.
You can just commit dummy transactions until it appears again.  This is
obvious not an official and approved way, but it's at least a shot
before you're forced to get out your hex editor.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:
> Bartosz Belter wrote:
>> 3. I've recovered some files and it looks to me they're from
>> $PGDATA/base/data/xxx (it looks this is the complete folder)
>> 4. No other files! (no pg_clog, pg_xlog etc)

> If you don't have a clog, then all your transactions will appear
> as "didn't happen yet", so your table data appears to be in the future.
> You can just commit dummy transactions until it appears again.  This is
> obvious not an official and approved way, but it's at least a shot
> before you're forced to get out your hex editor.

Less painfully, use pg_resetxlog to push the XID and WAL counters up
past what's in the database.  The problem is to know where to set them
:-(

            regards, tom lane