Re: pg_restore fails on Windows

Tom Tom wrote:
> Magnus Hagander wrote:
>> Tom Tom wrote:
>>>> Tom Tom wrote:
>>>>> Hello,
>>>>>
>>>>> We have a very strange problem when restoring a database on Windows XP.
>>>>> The PG version is 8.1.10
>>>>> The backup was made with the pg_dump on the same machine.
>>>>>
>>>>> pg_restore -F c -h localhost -p 5432 -U postgres -d "configV3" -v
>>>> "c:\Share\POSTGRES.backup"
>>>>> pg_restore: connecting to database for restore
>>>>> Password:
>>>>> pg_restore: creating SCHEMA public
>>>>> pg_restore: creating COMMENT SCHEMA public
>>>>> pg_restore: creating PROCEDURAL LANGUAGE plpgsql
>>>>> pg_restore: creating SEQUENCE hi_value
>>>>> pg_restore: executing SEQUENCE SET hi_value
>>>>> pg_restore: creating TABLE hibconfigelement
>>>>> pg_restore: creating TABLE hibrefconfigbase
>>>>> pg_restore: creating TABLE hibrefconfigreference
>>>>> pg_restore: creating TABLE hibtableattachment
>>>>> pg_restore: creating TABLE hibtableattachmentxmldata
>>>>> pg_restore: creating TABLE hibtableelementversion
>>>>> pg_restore: creating TABLE hibtableelementversionxmldata
>>>>> pg_restore: creating TABLE hibtablerootelement
>>>>> pg_restore: creating TABLE hibtablerootelementxmldata
>>>>> pg_restore: creating TABLE hibtableunversionedelement
>>>>> pg_restore: creating TABLE hibtableunversionedelementxmldata
>>>>> pg_restore: creating TABLE hibtableversionedelement
>>>>> pg_restore: creating TABLE hibtableversionedelementxmldata
>>>>> pg_restore: creating TABLE versionedelement_history
>>>>> pg_restore: creating TABLE versionedelement_refs
>>>>> pg_restore: restoring data for table "hibconfigelement"
>>>>> pg_restore: restoring data for table "hibrefconfigbase"
>>>>> pg_restore: restoring data for table "hibrefconfigreference"
>>>>> pg_restore: restoring data for table "hibtableattachment"
>>>>> pg_restore: restoring data for table "hibtableattachmentxmldata"
>>>>> pg_restore: [archiver (db)] could not execute query: no result from server
>>>>> pg_restore: *** aborted because of error
>>>>>
>>>>> The restore unexpectedly fails on hibtableattachmentxmldata table, which is
>> as
>>>> follows:
>>>>> CREATE TABLE hibtablerootelementxmldata
>>>>> (
>>>>> xmldata_id varchar(255) NOT NULL,
>>>>> xmldata text
>>>>> )
>>>>> WITHOUT OIDS;
>>>>>
>>>>> and contains thousands of rows with text field having even 40MB, encoded in
>>>> UTF8.
>>>>> The database is created as follows:
>>>>>
>>>>> CREATE DATABASE "configV3"
>>>>> WITH OWNER = postgres
>>>>> ENCODING = 'UTF8'
>>>>> TABLESPACE = pg_default;
>>>>>
>>>>>
>>>>> The really strange is that the db restore runs OK on linux (tested on
>> RHEL4,
>>>> PG version 8.1.9).
>>>>> The pg_restore output is _not_ very descriptive but I suspect some
>> dependency
>>>> on OS system libraries (encoding), or maybe it is also related to the size
>> of
>>>> the CLOB field. Anyway we are now effectively without any possibility to
>> backup
>>>> our database, which is VERY serious.
>>>>> Have you ever came across something similar to this?
>>>> Check what you have in your server logs (pg_log directory) and the
>>>> eventlog around this time. There is probably a better error message
>>>> available there.
>>>>
>>>> //Magnus
>>>>
>>> Thank you for your hint.
>>> The server logs does not display any errors, except for
>>>
>>> 2008-08-08 11:14:16 CEST LOG: checkpoints are occurring too frequently (14
>> seconds apart)
>>> 2008-08-08 11:14:16 CEST HINT: Consider increasing the configuration
>> parameter "checkpoint_segments".
>>> 2008-08-08 11:14:38 CEST LOG: checkpoints are occurring too frequently (22
>> seconds apart)
>>> 2008-08-08 11:14:38 CEST HINT: Consider increasing the configuration
>> parameter "checkpoint_segments".
>>> 2008-08-08 11:14:57 CEST LOG: checkpoints are occurring too frequently (19
>> seconds apart)
>>> 2008-08-08 11:14:57 CEST HINT: Consider increasing the configuration
>> parameter "checkpoint_segments".
>>> 2008-08-08 11:15:14 CEST LOG: checkpoints are occurring too frequently (17
>> seconds apart)
>>> 2008-08-08 11:15:14 CEST HINT: Consider increasing the configuration
>> parameter "checkpoint_segments".
>>> 2008-08-08 11:15:36 CEST LOG: checkpoints are occurring too frequently (22
>> seconds apart)
>>> 2008-08-08 11:15:36 CEST HINT: Consider increasing the configuration
>> parameter "checkpoint_segments".
>>> 2008-08-08 11:15:56 CEST LOG: checkpoints are occurring too frequently (20
>> seconds apart)
>>> 2008-08-08 11:15:56 CEST HINT: Consider increasing the configuration
>> parameter "checkpoint_segments".
>>> 2008-08-08 11:16:16 CEST LOG: checkpoints are occurring too frequently (20
>> seconds apart)
>>> 2008-08-08 11:16:16 CEST HINT: Consider increasing the configuration
>> parameter "checkpoint_segments".
>>> The warnings disappeared when the "checkpoint_segments" value was increased to
>> 10. The restore still failed however :(
>>> The Windows eventlogs show no errors, just informational messages about
>> starting/stopping the pg service.
>>
>> That's rather strange. There really should be *something* in the logs
>> there. Hmm.
>>
>> Does this happen for just this one dump, or does it happen for all dumps
>> you create on this machine (for example, can you dump single tables and
>> get those to come through - thus isolating the issue to one table or so)?
>>
>
> So after all I was able to isolate the issue to one table/one row. Now I have one small dump that (if trying to restore) positively fails on windows system (tested on 3 machines with winXP, PG 8.1.10) and passes through on Linux (tested on RHEL4, PG 8.1.9). Logs on the db side shows no relevant information, neither pg_restore.
> Seems that this is a base for a bug report.

Yup.
Can you set up a reproducible test-case that doesn't involve your data,
just the specific table definitions and test data?

If not, can you send me a copy of the dump (off-list) and I can see if I
can find something out from it.

//Magnus