Re: [GENERAL] pg_restore fails on Windows

> 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.

Tomas