X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4D964329F12 for ; Wed, 6 Oct 2004 17:29:13 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63795-05 for ; Wed, 6 Oct 2004 16:29:09 +0000 (GMT) Received: from web52905.mail.yahoo.com (web52905.mail.yahoo.com [206.190.39.182]) by svr1.postgresql.org (Postfix) with SMTP id B5807329DB1 for ; Wed, 6 Oct 2004 17:29:06 +0100 (BST) Message-ID: <20041006162736.45531.qmail@web52905.mail.yahoo.com> Received: from [68.167.147.42] by web52905.mail.yahoo.com via HTTP; Wed, 06 Oct 2004 09:27:35 PDT Date: Wed, 6 Oct 2004 09:27:35 -0700 (PDT) From: akanksha kulkarni Subject: Re: reconstructing database To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <23946.1096568539@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/104 X-Sequence-Number: 15097 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 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