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
