Thread: reconstructing database

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

akanksha kulkarni <akanksha_kul@yahoo.com> writes:
> 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

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

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

mlw writes:
  > 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
 ***************************************************








--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> > 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