Re: pg_restore fails on Windows

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.

Tomas