Subject: Re: Database recovery in postgres 7.2.4.
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Santosh <ssbhujbal@gmail.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1162203909.082487.192540@m7g2000cwm.googlegroups.com>
References: <1162139808.031657.23340@f16g2000cwb.googlegroups.com>
	<1162203909.082487.192540@m7g2000cwm.googlegroups.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1162843692.5195.37.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Mon, 06 Nov 2006 14:08:12 -0600

On Mon, 2006-10-30 at 04:25, Santosh wrote:
> Hi All.
> 
> My setup is as follows:
> OS: Sun Solaris 5.8.
> Postgres: 7.2.4

Just so you know, 7.2 is ancient. You should, at a minimum be running
the latest 7.2 release, 7.2.8.  You should really look into upgrading to
a later version as soon as possible.

> I have very large database, which contain 15 tables and each table is
> contain more than 10,00,000 records.
> 
> My application is parsing text data files and inserting records into
> database.
> 
> When this process was running last night, machine was got down because
> of power failure.
> 
> Today when I come back to office and I have compaired record count in
> data files and in database and find that some records are missing in
> database.

You may have lost data.  Hard to say from what you've told us.  How did
you determine that records are missing?

> Then I have checked postgres log and found log similar to as follows:
> =================================
> DEBUG:  The DataBase system was not properly shut down
> 	Automatic recovery is in progress...
> DEBUG:  Redo starts at (0, 1064)
> =================================

This is normal.  It's the messages after this you need to worry about. 
Did the logs say anything else after this?

> I have read some WAL related stuff on postgres site but not found any
> solution to recover those uncommited records.

If the records were committed, then they'd get put into the db by the
wal recovery process.  If the hardware (i.e. the hard drive and its
controller) wasn't lying about fsync.  If the hardware was lying, you
need to look into that (hint, IDE and many SATA drives lie about fsync)

> Can any one tell me how to recover those missing records?

there are some older tools laying about, like pgfsck that might help. 
I'd ask on admin or another list.  SQL isn't really the list for admin /
system problems...