Some additional data - here is the results gathered from pg_controldata:

pg_control version number: 71
Catalog version number: 200101061
Database state: SHUTDOWNING
pg_control last modified: Mon Feb 11 19:58:12 2002
Current log file id: 5
Next log file segment: 33
Latest checkpoint location: 5/20CD4ED8
Prior checkpoint location: 5/20C81FC8
Latest checkpoint's REDO location: 5/20CD4ED8
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's StartUpID: 353
Latest checkpoint's NextXID: 4278018
Latest checkpoint's NextOID: 8532790
Time of latest checkpoint: Mon Feb 11 17:15:45 2002
Database block size: 8192
Blocks per segment of large relation: 131072
LC_COLLATE: C
LC_CTYPE: C

Is my only option at this point to execute a pg_resetxlog and then attempt a pg_dump and restore?

Regards,

Art Conner

Thank you for responding to me...

I ended up running pg_resetxlog and had to utilize the -f just to get it to complete successfully.

I of course was able to get postmaster up and running at that point and able to view the data.

I immediately ran pg_dump but it was unable to complete successfully and generated the following error.


ERROR: unexpected chunk size 1159 in chunk 0 for toast value 8532788


I then began looking for a way to extract any data that has been changed since the last backup copy of the database and was able to run SQL commands and output some recent data into a delimited ascii file.

Occassionally that same error would be generated while executing those commands. During the course of running those various SQL commands I was able to pinpoint specific records and columns (fields) that would generate the error when requested.

Now my question(s).

By way of SQL commands I believe that I have located all of the troubled records. (I don't know enough about PostgreSQL to even know if that is a valid/plausible statement. How much is corrupt that I just can't see - or isn't causing and error?) Additionally there are some records that are duplicated (even though they violate unique definitions set in the database structure).

Is it at all conceivable to be able to replace/fix those troubled records by way of deleting the records and re-entering the data by hand? At least to the point where I can get pg_dump to complete successfully?

If not, is it possible to define a set of records by way of SQL statement for pg_dump to use during backup?

Just trying to recover what data I can.

Regards,

Art Conner

First of all I hope this will help you a little bit.

The duplicate entries are not the problem.If you have a sequence or an unique index the db returns an error and do not insert the duplicate entry


But attention this only works if you have dumped this with

pg_dump -D .....

the -D dumps teh data in as insert statements
When you reinsert this file it may take a while but you have no duplicates over an index.

Have you tried a vacuum over the database??

You cannot specify a set of records to be dumped
only a table can be selected
look at the optinos
pg_dump --help

I just attempted to do the

pg_dump -D (etc.)

And after just a few minutes it aborted and pg_dump provided a stackdump.

I have been careful to avoid doing anything that would change the state of the data so I have not performed a vacuum on the database. Would you recommend at this point doing that? Could it solve my problem? Or just make it worse?

Your assitance and replies are greatly appreciated.

Regards,

Art Conner

The problem is I don't know exactly what the problem is
but vacuum can solve many problems
it only cleans transactions
if there was the problem data will be lost

I think the best thing will be if you dump the data as you can and try to reimport it in a test database
and clean the wrong things manually

This is a horrible work but if this data is important anything else will be dangerous.

As I have said before I'm not an expert on cygwin systems

Perhaps you will get more help when you mail to the cygwin mailing list on www.postgresql.org