reconstructing database

[akanksha kulkarni]

Hi,

Is there any way to reconstruct database from data
files that are present in
/var/lib/pgsql/data/<database oid folders>

Actually I upgraded to new version of postgres (7.4)
but again reverted back to older version (7.2) after
uninstalling new version.

How can I make the new install of older version
recognize old databases.

I did not take any standard backups with pg_dump. So
the only data files I have, are the files that are
created when you create database, tables etc.

Help is urgently needed and appreciated.

Thanks,
Akanksha



__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by akanksha kulkarni
Is there any way to reconstruct database from data
files that are present in
/var/lib/pgsql/data/<database oid folders>
Actually I upgraded to new version of postgres (7.4)
but again reverted back to older version (7.2) after
uninstalling new version.

That alone would not have broken your data, because the new version
would have refused to start in an older data directory, and so it would
not have modified the existing files. I think you are neglecting to
tell us what you actually did.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[akanksha kulkarni]

Hi Tom,

I am still not able to recover my old postgres
database.

As postmaster was not starting due to invalid
checkpoints, I ran pg_resetxlog utility . After that
postmaster started, but I am not getting complete
data.
I made pg_dump of database but that comes out to be
very small file. I am sure most of the data is not
there.

Also most of the tables are showing empty when I
connect to database.

I have old /data directory with me.

Is there any other way to extract data from raw data
files in /data/base.

I have found one of your posting related to this in
DBForums which is as below. Can you explain a little
bit more about 0x55 stuff.

Or if you know of some tool/script/postgres recovery
specialist(person) to extract data from raw /data
files, PLEASE let me know.

Thanks for your help.

********* your reply to mlw's posting
**********************
Re: Recovery tools

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

Just suppose that all the log files are gone, and

the only thing left is

some of the files in the /data directory. Is there

any way to scan this

data and dump it to a file which could

subsequently be used with a "COPY

FROM STDIN" on a new database?

There aren't separate tools, and I'm not sure there
could or should be.
What I'd do in that situation is:
* pg_resetxlog to get a minimally valid xlog
* if clog is missing, gin up files containing 0x55
everywhere
(to make it look like every transaction has committed
--- or
put 00 everywhere if you'd rather assume that recent
transactions all aborted)
* start postmaster, look around, fix problems until I
can pg_dump.

AFAICS, you can make tools that work at the page/item
level (like
pg_filedump, see http://sources.redhat.com/rhdb/), but
there is hardly
any scope for doing anything intermediate between that
and a full
postmaster. There's no hope of decoding the contents
of a tuple without
access to the table's tuple descriptor, which means
you need most of the
system catalog mechanisms; plus you'd need the I/O
routines for the
datatypes involved. Might as well just use the
postmaster as your data
inspection tool.

regards, tom lane
***************************************************

5. I then tried uninstalling new version by giving
command gmake clean. I also moved the new data
directory to temp directory.

"gmake clean" is not going to do an uninstall.

6. I reinstalled old version of postgres from RH
package manager.I copied old data directory of
previous install of older version of postgres and

then

tried running postmaster but it didn't ran saying
there is newer version of postgres.

I think you had both sets of executables installed
at once and somehow
you were picking up the wrong one. There is nothing
wrong with your old
data directory, it's the executables you need to get
straight.
regards, tom lane

_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster