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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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 able to dump the DB.

--


Best Regards,

Tarlika Elisabeth Schmitz

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly