Re: 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
maybeit is also related to the size of the CLOB field. Anyway we are now effectively without any possibility to backup
ourdatabase, 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