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