recover from base directory?

hi all,

1) do backup always... on cd, tape, raid. my hd just burn.

there is hope for me, in lost+found there is a directory called base with
multiples sub-direcotry (403274 for example). is this my database, can i
recover them with thoses directory?

look in the archive for recover base... nothing really like that was
explain... but sorry if it's somewhere!!!

pat

hi again,

my postgresql version for the base directory is:
8.0.4

now i have installed the last one, but of course if need be i can reinstall
8.0.4.

thanks you!
pat

----- Original Message -----
From: "patrick" <patrick(at)11h11(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, April 04, 2006 3:45 AM
Subject: [GENERAL] recover from base directory?

> hi all,
>
> 1) do backup always... on cd, tape, raid. my hd just burn.
>
> there is hope for me, in lost+found there is a directory called base with
> multiples sub-direcotry (403274 for example). is this my database, can i
> recover them with thoses directory?
>
> look in the archive for recover base... nothing really like that was
> explain... but sorry if it's somewhere!!!
>
> pat
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

"patrick" <patrick(at)11h11(dot)com> writes:
> 1) do backup always... on cd, tape, raid. my hd just burn.

> there is hope for me, in lost+found there is a directory called base with
> multiples sub-direcotry (403274 for example). is this my database,

Only part of it. Can you find the pg_clog and pg_xlog directories?

regards, tom lane

> Only part of it. Can you find the pg_clog and pg_xlog directories?

[root(at)hd-t407cl lost+found]# find . | grep pg_clog
./#3044578/pg_clog
./#3044578/pg_clog/0000
./#3044578/pg_clog/0001
./#3044578/pg_clog/0002

(3 files)

[root(at)hd-t407cl lost+found]# find . | grep pg_xlog
./#3044578/pg_xlog
./#3044578/pg_xlog/archive_status
./#3044578/pg_xlog/00000001000000000000000F
./#3044578/pg_xlog/00000001000000000000000E

looks good?

by the way, is very nice that you are still around and answering question, i
remember that you helped me a lots in the past (like 7 years ago). clap clap
clap!

pat

"patrick" <patrick(at)11h11(dot)com> writes:
>> Only part of it. Can you find the pg_clog and pg_xlog directories?

> [root(at)hd-t407cl lost+found]# find . | grep pg_clog
> ./#3044578/pg_clog
> ./#3044578/pg_clog/0000
> ./#3044578/pg_clog/0001
> ./#3044578/pg_clog/0002

> (3 files)

> [root(at)hd-t407cl lost+found]# find . | grep pg_xlog
> ./#3044578/pg_xlog
> ./#3044578/pg_xlog/archive_status
> ./#3044578/pg_xlog/00000001000000000000000F
> ./#3044578/pg_xlog/00000001000000000000000E

> looks good?

Looks promising. If you've also got the "global" directory then you
should have everything you really need. Just move 'em back into the
correct data directory. You'll need to find or reconstruct the other
stuff such as the config files, but that's not too hard. (You might
want to initdb a new data directory, and then move what you can find
into it, replacing what's there.)

It kinda looks like #3044578 might actually *be* your data directory;
take a look in it ...

BTW, this is not a good time to be doing a PG version upgrade;
keep using the same version you had.

regards, tom lane

> Looks promising. If you've also got the "global" directory then you
> should have everything you really need.

i don't have the global directory... can i do something???
pat

hi again,

the postgresql version i was using before the crash was 8.0.4 - i still
don't have the global directory.

pat

some update... but still not working. so to resume i have base / pg_xlog (2
files and archives dir.) / pg_clog - BUT no global. i reinstalled postgresql
8.0.4 (same version of my backup data/). initdb, createdb mydb1, mydb2 mydb3
and move pg_xlog and _clog over the data. rename my base to fit the oid.

starting postmaster is working fine, but i had to do a pg_resetxlog and now
it start like normal. but the problem is when i pgsql mydb1 \d no relations
founds...

what can i do?
pat

----- Original Message -----
From: "patrick" <patrick(at)11h11(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, April 04, 2006 3:45 AM
Subject: [GENERAL] recover from base directory?

> hi all,
>
> 1) do backup always... on cd, tape, raid. my hd just burn.
>
> there is hope for me, in lost+found there is a directory called base with
> multiples sub-direcotry (403274 for example). is this my database, can i
> recover them with thoses directory?
>
> look in the archive for recover base... nothing really like that was
> explain... but sorry if it's somewhere!!!
>
> pat
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

"patrick" <patrick(at)11h11(dot)com> writes:
> some update... but still not working. so to resume i have base / pg_xlog (2
> files and archives dir.) / pg_clog - BUT no global. i reinstalled postgresql
> 8.0.4 (same version of my backup data/). initdb, createdb mydb1, mydb2 mydb3
> and move pg_xlog and _clog over the data. rename my base to fit the oid.

> starting postmaster is working fine, but i had to do a pg_resetxlog and now
> it start like normal. but the problem is when i pgsql mydb1 \d no relations
> founds...

You probably need to do a pg_resetxlog to move the transaction ID
counter up to where it was before. Too bad you didn't find the old
global directory --- the pg_control file would have been good to reuse.
In its absence, look at the pg_resetxlog manual page concerning how to
determine a reasonable XID setting from the pg_clog file names.

regards, tom lane

hi tom,

thank you for your answer! i am very happy that i will not need the global
directory cause i really don't have it (too many bad sectors).

is the problem of having no relations founds with psql mydb1 \d related to
setting the next oid, next transaction id and WAL starting address with
pg_resetxlog?

pat

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "patrick" <patrick(at)11h11(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, April 04, 2006 3:58 PM
Subject: Re: [GENERAL] recover from base directory?

> "patrick" <patrick(at)11h11(dot)com> writes:
>> some update... but still not working. so to resume i have base / pg_xlog
>> (2
>> files and archives dir.) / pg_clog - BUT no global. i reinstalled
>> postgresql
>> 8.0.4 (same version of my backup data/). initdb, createdb mydb1, mydb2
>> mydb3
>> and move pg_xlog and _clog over the data. rename my base to fit the oid.
>
>> starting postmaster is working fine, but i had to do a pg_resetxlog and
>> now
>> it start like normal. but the problem is when i pgsql mydb1 \d no
>> relations
>> founds...
>
> You probably need to do a pg_resetxlog to move the transaction ID
> counter up to where it was before. Too bad you didn't find the old
> global directory --- the pg_control file would have been good to reuse.
> In its absence, look at the pg_resetxlog manual page concerning how to
> determine a reasonable XID setting from the pg_clog file names.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

hi,

just found about recovery.conf ? should i use it or in my case not having
data/global doesn't help.

pat

On April 4, 2006 03:52 pm, "patrick" <patrick(at)11h11(dot)com> wrote:
> hi,
>
> just found about recovery.conf ? should i use it or in my case not having
> data/global doesn't help.
>

recovery.conf is used to recover from a PITR backup. If you haven't been
making PITR backups (which is only possible as of 8.0), it won't be doing
anything for you.

--
Alan

last day to find a solution for me - i am on the edge.

here's a resume of what i am doing. note that i don't have the global
direcotry of $PGDATA.

----------------
first of all reinstall a new hard-drive and installed the same version
of postgresql (8.0.4) with the same configuration.

now doing a initdb somewhere on the new hd.

then createdb the same database (with the same name)
so now i have a working structure (with a global dir)

now with oid2name i can find what is what in my $PGDATA/base

so now it's time to put my $BACKUP/$PGDATA/base in the new $PGDATA/base
but renaming my back-up with the new oid (only the first folder not
sub-dir.) - i don't replace template0, template1

now a cp -R $BACKUP/$PGDATA/pg_xlog $PGDATA/
now a cp -R $BACKUP/$PGDATA/pg_clog $PGDATA/

now i have to use pg_resetxlog to fit my needs. this is what i'm not
sure, i have 00000001000000000000000E and 00000001000000000000000F in
pg_xlog. what should i use as arguments to pg_resetxlog -o -x -l.

after finding the good arguments, i will be able to start postmaster and
see my tables, cause right now it's always no realtions founds no matter
my test with pg_resetxlog?

----------------

if anybody did this once, please share your knowledge i will be very
generous!

regards,
pat