Обсуждение: pg_dump fails (timestamp out of range)

Apologies for cross-posting (already sent to pgadmin-support) but I am 
totally stuck with this:
====================================================================


I run an ecommerce system on a webserver, which I want to move to a
different machine.

However, I am stalled because pg_dump fails with the following error:

pg_dump: ERROR:  timestamp out of range
pg_dump: SQL command to dump the contents of table "server_hit_bin"
failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR:  timestamp out of range
pg_dump: The command was: COPY public.server_hit_bin (server_hit_bin_id,
content_id, internal_content_id, hit_type_id, server_ip_address,
server_host_name, bin_start_date_time, bin_end_date_time, number_hits,
total_time_millis, min_time_millis, max_time_millis, last_updated_stamp,
last_updated_tx_stamp, created_stamp, created_tx_stamp) TO stdout;



The table contains about 50,000 records, 6 of which are timestamps.
SELECT bin_end_date_time fails with the same error.

SELECT last_updated_tx_stamp  fails with another error:
server closed the connection unexpectedly


The server has crashied manies a time, presumably a hardware fault. I
presume that the table got corrupted.

Any ideas what I can do to dump/restore the database would be much
appreciated.

-- 


Regards,

Tarlika Elisabeth Schmitz

T E Schmitz <mailreg@numerixtechnology.de> writes:
> However, I am stalled because pg_dump fails with the following error:

> pg_dump: ERROR:  timestamp out of range
> pg_dump: SQL command to dump the contents of table "server_hit_bin"
> failed: PQendcopy() failed.

You should treat this as a corrupt-data exercise: you need to identify
and fix (or delete) the offending row(s).  In this case you might try
tests like "bin_end_date_time > '1 Jan 9999'" and so on to see if you
can determine exactly which rows are bad.
        regards, tom lane

Tom Lane wrote:
> T E Schmitz <mailreg@numerixtechnology.de> writes:
> 
>>pg_dump: ERROR:  timestamp out of range
>>pg_dump: SQL command to dump the contents of table "server_hit_bin"
>>failed: PQendcopy() failed.
> 
> 
> You should treat this as a corrupt-data exercise: you need to identify
> and fix (or delete) the offending row(s).  In this case you might try
> tests like "bin_end_date_time > '1 Jan 9999'" and so on to see if you
> can determine exactly which rows are bad.

Dear Tom,
I can't thank you enough for the above advice. The test above identified  3 records and once they were removed I was
ableto dump the DB.
 

-- 


Best Regards,

Tarlika Elisabeth Schmitz

On Mon, Feb 26, 2007 at 11:05:08AM +0000, T E Schmitz wrote:
> I can't thank you enough for the above advice. The test above identified 
>  3 records and once they were removed I was able to dump the DB.

You'll be wanting to make sure your hardware is fixed after this,
don't forget, or you'll end up in the same place next time.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?    --attr. John Maynard Keynes

Andrew Sullivan wrote:
> On Mon, Feb 26, 2007 at 11:05:08AM +0000, T E Schmitz wrote:
> 
>>I can't thank you enough for the above advice. The test above identified 
>> 3 records and once they were removed I was able to dump the DB.
> 
> 
> You'll be wanting to make sure your hardware is fixed after this,
> don't forget, or you'll end up in the same place next time.

This was the last step to get rid of the darned machine. Everything else 
had already been moved over to the new server. Wish I'd posted a week 
earlier; that would have saved me a month's rent.

-- 


Regards,

Tarlika Elisabeth Schmitz