Thread: 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