Re: pg_restore fails on Windows - Mailing list pgsql-general

Magnus Hagander wrote:
> 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.
>

OK, first, thank you for your efforts in this case.

Windows test case:
- PG 8.1.10 was installed on the Windows XP Professional machine w. 2G memory, using the standard msi installer from
postgresql.org.No special db setting/tuning was made after the installation.  

- database "config" was created using pgAdmin tool, using template1
CREATE DATABASE "config"
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default;

- table "hibtableattachmentxmldata" was created
CREATE TABLE hibtableattachmentxmldata
(
  xmldata_id varchar(255) NOT NULL,
  xmldata text,
  blobdata bytea
)
WITHOUT OIDS;
ALTER TABLE hibtableattachmentxmldata OWNER TO postgres;

- test row was inserted using the Java client code
INSERT INTO hibtableattachmentxmldata VALUES (?,?,?)
where value 1 is "1111"
value 2 is 25MB (i.e. 1024*1024*25) long text of char 'F' (0x46)
value 3 is 25MB (i.e. 1024*1024*25) long byte array filled with values of 5 (0x5)

- the db dump was made by
pg_dump -F c -C --username=postgres --inserts --file c:\Share\trial.backup config

- the hibtableattachmentxmldata was dropped by
  DROP TABLE hibtableattachmentxmldata

- the restore was performed
pg_restore -i -h localhost -p 5432 -U postgres -d config -v "c:\Share\trial.backup"

-the output was:
pg_restore: connecting to database for restore
Password:
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating TABLE hibtableattachmentxmldata
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

If it is of any help, I can provide the related test dump or test Java client code off-list.

Tomas