Postgres 7.0.2 Problem
-----------------------------------
 
I am having a rather big problem with an installation of postgres 7.0.2 on
cobalt, in that the db server is unable to see any of the data stored in the
(only) database which is running (other than template1).  I suspect that the
files in the data directory have been conrrupted or otherwise lost
integrity, possibly due to the fact that the disk partition where the db
cluster is running has reached 100% usage.
 
The problem is the -l doesn't list my database in the catalog, although i
can psql xxxxx into the database.  Further, \d lists 'No Relations', however
i can select * from a table, and although the database reports no rows, the
table scheme does report correctly. 
 
In terms of backup, all I have is a copy of the data directory itself.
Unfortunately I was stupid enough not to have ever used pg_dumpall.
 
My Question is, is there ANY means by which I can get at the data.  I
understand that in later versions there is a pg_resetxlog tool which can
help with problems like this, but unfortunately not in 7.0.2.  Does anybody
know if there is an equivalent tool available for my version of the
database?  Alternatively, is there any other facility available which can
extract the data from the data files in a form that I could work with?
 
I have laready tried re initialising the db on a larger disk partion and
moving the data files around, but to no avail.
 
Any help would be very welcome!!
 
Regards.
 





Postgres 7.0.2 
Problem
-----------------------------------
 
I am having a rather 
big problem with an installation of postgres 7.0.2 on cobalt, in that the db 
server is unable to see any of the data stored in the (only) database which is 
running (other than template1).  I suspect that the files in the data 
directory have been conrrupted or otherwise lost integrity, possibly due to 
the fact that the disk partition where the db cluster is running has 
reached 100% usage.
 
The problem is the 
-l doesn't list my database in the catalog, although i can psql xxxxx into 
the database.  Further, \d lists 'No Relations', however i can select * 
from a table, and although the database reports no rows, the table 
scheme does report correctly. 
 
In terms of backup, 
all I have is a copy of the data directory itself.  Unfortunately 
I was stupid enough not to have ever 
used pg_dumpall.
 
My Question is, is 
there ANY means by which I can get at the data.  I understand that in later 
versions there is a pg_resetxlog tool which can help with problems like this, 
but unfortunately not in 7.0.2.  Does anybody know if there is an 
equivalent tool available for my version of the database?  Alternatively, 
is there any other facility available which can extract the data from the data 
files in a form that I could work with?
 
I have laready tried 
re initialising the db on a larger disk partion and moving the data files 
around, but to no avail.
 
Any help would be 
very welcome!!
 
Regards.
Tue, 19 Oct 2004 13:28:52 +0100
"Ben Osborne" <bosborne@bsdmarketing.co.uk>
On Tue, Oct 19, 2004 at 01:28:52PM +0100, Ben Osborne wrote:
> Postgres 7.0.2 Problem
> -----------------------------------

Yikes.  That's old. 

> (only) database which is running (other than template1).  I suspect that the
> files in the data directory have been conrrupted or otherwise lost
> integrity, possibly due to the fact that the disk partition where the db
> cluster is running has reached 100% usage.
>  
> The problem is the -l doesn't list my database in the catalog, although i
> can psql xxxxx into the database.  Further, \d lists 'No Relations', however
> i can select * from a table, and although the database reports no rows, the
> table scheme does report correctly. 

I doubt that's your problem.  I suspect xid wraparound instead. 
Unfortunately, without a pg_dump, I suspect your data is
inaccessible  (see the current docs, section 21.1.3, for an
explanation of why this is.  I think the 7.0 docs don't contain all
that info, BTW).  We have a very dangerous tool we've used for testing
that will thump the xid in 7.2, but I have no idea whether that'd
work in versions prior to that.  Jan Wieck might know, though.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
		--Philip Greenspun

Tue, 19 Oct 2004 11:41:53 -0400
Andrew Sullivan <ajs@crankycanuck.ca>
Re: 
"Ben Osborne" <bosborne@bsdmarketing.co.uk> writes:
> Postgres 7.0.2 Problem
 
> I am having a rather big problem with an installation of postgres 7.0.2 on
> cobalt, in that the db server is unable to see any of the data stored in the
> (only) database which is running (other than template1).

The symptoms seem reasonably consistent with the theory that you have
suffered transaction ID wraparound.  How large is the $PGDATA/pg_log
file?  If it's exactly 1Gb then this is almost certainly the answer.

> My Question is, is there ANY means by which I can get at the data.

I believe it is possible to reset the transaction counter to something a
little bit less than 4 billion, which will make everything up to that
point appear to be "in the past" again.  I have long since forgotten the
details, but digging in the list archives should turn up some discussion
of how to do that in 7.0.  Then do a quick pg_dumpall, initdb and
reload.

You should seriously consider updating to a more modern PG version
while you are at it ...

			regards, tom lane

Tue, 19 Oct 2004 12:30:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 
On 10/19/2004 11:41 AM, Andrew Sullivan wrote:

> On Tue, Oct 19, 2004 at 01:28:52PM +0100, Ben Osborne wrote:
>> Postgres 7.0.2 Problem
>> -----------------------------------
> 
> Yikes.  That's old. 
> 
>> (only) database which is running (other than template1).  I suspect that the
>> files in the data directory have been conrrupted or otherwise lost
>> integrity, possibly due to the fact that the disk partition where the db
>> cluster is running has reached 100% usage.
>>  
>> The problem is the -l doesn't list my database in the catalog, although i
>> can psql xxxxx into the database.  Further, \d lists 'No Relations', however
>> i can select * from a table, and although the database reports no rows, the
>> table scheme does report correctly. 
> 
> I doubt that's your problem.  I suspect xid wraparound instead. 
> Unfortunately, without a pg_dump, I suspect your data is
> inaccessible  (see the current docs, section 21.1.3, for an
> explanation of why this is.  I think the 7.0 docs don't contain all
> that info, BTW).  We have a very dangerous tool we've used for testing
> that will thump the xid in 7.2, but I have no idea whether that'd
> work in versions prior to that.  Jan Wieck might know, though.
> 
> A
> 

It's a user defined function in C that modifies this xid counter on 
call. But I don't think I would recommend mucking with DDL statements in 
a wrapped around DB ... that's not going anywhere.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Tue, 19 Oct 2004 13:42:49 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: 