Re: pg_restore fails on Windows

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)?

//Magnus