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 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

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.

Tomas

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)?

//Magnus

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

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

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

=?us-ascii?Q?Tom=20Tom?= <cobold@seznam.cz> writes:

Magnus Hagander wrote:

Can you set up a reproducible test-case that doesn't involve your data,

- 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)

Hmm. So allowing for escaping of the bytea values, this line is going
to be somewhere around 150MB in text form --- and because you used
--inserts rather than COPY mode, it will have to be sent in a single
message.

I think it's pretty obvious that what's happening is we're failing to do
that and then not recovering nicely at all. A look at the pg_dump code
says the error message is coming from here

res = PQexec(conn, qry->data);
if (!res)
die_horribly(AH, modulename, "%s: no result from server\n", desc);

and a look at the libpq code suggests that PQexec will return a NULL on
any send failure, which isn't part of its contract either. So we've
got robustness issues on both sides of that API :-(

Of course the larger issue is why it's failing --- 150MB doesn't seem
like that much for a modern machine. I suspect that PQerrorMessage()
would tell us something useful, but pg_restore isn't letting us see it.

regards, tom lane

I wrote:

Of course the larger issue is why it's failing --- 150MB doesn't seem
like that much for a modern machine. I suspect that PQerrorMessage()
would tell us something useful, but pg_restore isn't letting us see it.

I've applied a patch for the latter issue. But the only way we can find
out what's happening is if someone will build a Windows version from CVS
tip for the OP...

regards, tom lane

Dear all:
We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list:
1. What's size of your database?
2. What Operating System are you using?
3. What level is your RAID array?
4. How many cores and memory does your server have?
5. What about your performance of join operations?
6. What about your performance of load operations?
7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning.
8. Single instance or a cluster, what cluster software are you using if you have a cluster?

Thank you in advance!

On Fri, Aug 15, 2008 at 9:42 PM, Amber <guxiaobo1982@hotmail.com> wrote:

Dear all:
We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list:
1. What's size of your database?

Varies. I've had reporting dbs in the low 100s of gigabytes.

2. What Operating System are you using?

I've generally worked with Linux. RHEL, Centos, or Ubuntu.

3. What level is your RAID array?

For transactional, ALWAYS RAID 10. For reporting sometimes RAID-5,
mostly RAID-10
The reporting server I built at my last company was a collection of
spare parts and ran a software RAID-10 over 4 150G sata drives. It
routinely outran the Oracle RAC cluster with 14 drives in RAID 6
sitting next to it doing reports on the same data.

4. How many cores and memory does your server have?

The reporting server from my last company had a single hyperthreaded
P4 and 4 Gig of ram.
Current transactional server runs on 8 opterons, with 32 Gigs of ram.

5. What about your performance of join operations?

Always been pretty good. Kind of a wide open question really. I'd
say PostgreSQL's query planner is usually very smart planning complex
queries. note that joins were never an issue, but I had to pay
attention to how I designed correlated subqueries and aggregate
queries.

6. What about your performance of load operations?

Pretty much dependent on the hardware you're on. I can replicate the
current ~15 Gig transactional db in about 15 or 20 minutes from one 8
core 16 drive machine to another.

7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning.

Concurrent but idle connections in production are around 600. Active
connections at a time are in the dozens. I can read at about 60 to 70
Megs a second for random access and around 350 to 400 Megs a second
for sequential reads.

8. Single instance or a cluster, what cluster software are you using if you have a cluster?

Two machines with one as slony master and the other as slony slave,
with the application doing weighted load balancing on reads between
the two.

The important thing about pgsql is how well it scales to work on
larger hardware.

If you throw enough drives on a quality RAID controller at it you can
get very good throughput. If you're looking at read only / read
mostly, then RAID5 or 6 might be a better choice than RAID-10. But
RAID 10 is my default choice unless testing shows RAID-5/6 can beat
it.

7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning.

Concurrent but idle connections in production are around 600. Active
connections at a time are in the dozens. I can read at about 60 to 70
Megs a second for random access and around 350 to 400 Megs a second
for sequential reads.

I am not so familiar with PostgreSQL, it uses a one process per connection architecture, let me say one agent process per client , what I am wondering is how multiple agent process share page caches. In many other databases, client agents uses the multiple thread method, so they can share memory buffers within the same process, are there share-memory mechanisms between PostgreSQL agent processes?

On Sun, 17 Aug 2008, Amber wrote:

what I am wondering is how multiple agent process share page caches.

The database allocates a block of shared memory (sized by the
shared_buffers parameter) that all the client processes share for caching
pages.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

-----Original Message-----
From: Scott Marlowe <scott.marlowe@gmail.com>

If you throw enough drives on a quality RAID controller at it you can
get very good throughput. If you're looking at read only / read
mostly, then RAID5 or 6 might be a better choice than RAID-10. But
RAID 10 is my default choice unless testing shows RAID-5/6 can beat
it.

I'm loading my slave server with RAID-0 based on 3 IDE 7200 Drives.
Is this worst off than a RAID 5 implementation?

Magnus Hagander wrote:

Tom Lane wrote:

I wrote:

Of course the larger issue is why it's failing --- 150MB doesn't seem
like that much for a modern machine. I suspect that PQerrorMessage()
would tell us something useful, but pg_restore isn't letting us see it.

I've applied a patch for the latter issue. But the only way we can find
out what's happening is if someone will build a Windows version from CVS
tip for the OP...

Attached is a pg_restore.exe off CVS tip today, which should include the
patch. Please try this one.

//Magnus

I tested the restore using the provided pg_restore.exe. The output is:

for --inserts mode:
-------------------------------
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)] Error from TOC entry 1731; 0 16429 TABLE DATA hibtableattachmentxmldata postgres
pg_restore: [archiver (db)] could not execute query: could not send data to server: No buffer space available (0x0000274
7/10055)
Command was: INSERT INTO hibtableattachmentxmldata VALUES ('1111', 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFFFFFFF...
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for TABLE hibtableattachmentxmldata
WARNING: errors ignored on restore: 1

for COPY mode
------------------------
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)] error returned by PQputCopyData: could not send data to server: No buffer space available (0
x00002747/10055)
pg_restore: *** aborted because of error

The restore in the COPY mode was obviously aborted, whereas the --inserts finished with warnings.

Note: the provided pg_restore.exe is not compiled with the support for compressed dumps.

Regards,
Tomas

Ow Mun Heng wrote:

-----Original Message-----
From: Scott Marlowe <scott.marlowe@gmail.com>

If you throw enough drives on a quality RAID controller at it you can
get very good throughput. If you're looking at read only / read
mostly, then RAID5 or 6 might be a better choice than RAID-10. But
RAID 10 is my default choice unless testing shows RAID-5/6 can beat
it.

I'm loading my slave server with RAID-0 based on 3 IDE 7200 Drives.
Is this worst off than a RAID 5 implementation?

I see no problem using Raid-0 on a purely read only database where there
is a copy of the data somewhere else. RAID 0 gives performance. If one
of the 3 drives dies it takes the server down and lost of data will
happen. The idea behind RAID 1/5/6/10 is if a drive does fail the
system can keep going. Giving you time to shut down and replace the
bad disk or if you have hot swappable just pull and replace. I just
went through failed drives on Email server a few months ago. This a
case where i told the client the server is 5 years old time to replace
it about 3 months latter i get a call "the server is really slow". It
turned out 1 of the drives in the RAID 10 had failed. The client
allowed me to order a new server at that point.

=?us-ascii?Q?Tom=20Tom?= <cobold@seznam.cz> writes:

Magnus Hagander wrote:

Attached is a pg_restore.exe off CVS tip today, which should include the
patch. Please try this one.

I tested the restore using the provided pg_restore.exe. The output is:

pg_restore: [archiver (db)] could not execute query: could not send data to server: No buffer space available (0x00002747/10055)

According to
http://support.microsoft.com/kb/201213
this is an acknowledged bug that's been broken since Windows 95, so
I suppose we should conclude that M$ is unwilling or incompetent to
fix it.

Possibly the best workaround is something like

+ #ifndef WIN32
		sent = pqsecure_write(conn, ptr, len);
+ #else
+ 		/* Windows tends to fail on large sends, see KB 20213 */
+ 		sent = pqsecure_write(conn, ptr, Min(len, 65536));
+ #endif

in pqSendSome(). The backend seems to not be subject to a similar
problem because it's already filtering its output through a limited-size
buffer.

regards, tom lane

On Mon, 2008-08-18 at 11:01 -0400, justin wrote:

Ow Mun Heng wrote:

-----Original Message-----
From: Scott Marlowe <scott.marlowe@gmail.com>

If you're looking at read only / read
mostly, then RAID5 or 6 might be a better choice than RAID-10. But
RAID 10 is my default choice unless testing shows RAID-5/6 can beat
it.

I'm loading my slave server with RAID-0 based on 3 IDE 7200 Drives.
Is this worst off than a RAID 5 implementation?

I see no problem using Raid-0 on a purely read only database where
there is a copy of the data somewhere else. RAID 0 gives performance.
If one of the 3 drives dies it takes the server down and lost of data
will happen. The idea behind RAID 1/5/6/10 is if a drive does fail
the system can keep going. Giving you time to shut down and replace
the bad disk or if you have hot swappable just pull and replace.

I'm looking for purely read-only performance and since I didn't have the
bandwidth to do extensive testing, I didn't know whether a RAID1 or a
Raid 0 will do the better job. In the end, I decided to go with RAID 0
and now, I'm thinking if RAID1 will do a better job.

I have a db (tables with up to 5,000,000 records, up to 70 columns x 1,500,000 records, around 50Gb of disk space for the database (incl data, indexes, etc)

Most records have PostGIS geometry columns, which work very well.

For read performance this is on a (2 yr old) Linux box with 2x software RAID 0 (striped) WD 10,000RPM Raptor drives.

FWIW bonnie gives reads at about 150Mb/sec from the filesystem. We have been more than happy with performance.
though the 4Gb of RAM helps....

For data security, pg_dump backs it up every second day onto another 250Gb drive on the box, & this is copied over the LAN to another server which is backed up to tape every day.

It works for us :-)

Cheers,

Brent Wood

Ow Mun Heng <Ow.Mun.Heng@wdc.com> 08/19/08 4:00 PM >>>

On Mon, 2008-08-18 at 11:01 -0400, justin wrote:

Ow Mun Heng wrote:

-----Original Message-----
From: Scott Marlowe <scott.marlowe@gmail.com>

If you're looking at read only / read
mostly, then RAID5 or 6 might be a better choice than RAID-10. But
RAID 10 is my default choice unless testing shows RAID-5/6 can beat
it.

I'm loading my slave server with RAID-0 based on 3 IDE 7200 Drives.
Is this worst off than a RAID 5 implementation?

I see no problem using Raid-0 on a purely read only database where
there is a copy of the data somewhere else. RAID 0 gives performance.
If one of the 3 drives dies it takes the server down and lost of data
will happen. The idea behind RAID 1/5/6/10 is if a drive does fail
the system can keep going. Giving you time to shut down and replace
the bad disk or if you have hot swappable just pull and replace.

I'm looking for purely read-only performance and since I didn't have the
bandwidth to do extensive testing, I didn't know whether a RAID1 or a
Raid 0 will do the better job. In the end, I decided to go with RAID 0
and now, I'm thinking if RAID1 will do a better job.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Aug 15, 2008 at 11:42 PM, Amber <guxiaobo1982@hotmail.com> wrote:

Dear all:
We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list:

DB is ~650m rows across 10 tables and is currently around 160gb.

Running on Ubuntu, mostly because this db started out as a toy and it
was easy. It's done well enough thus far that it isn't worth the
hassle to replace it with anything else.

Currently only using Raid 0; the database can be regenerated from
scratch if necessary so we don't have to worry overmuch about disk
failures.

Machine is a quad-core Xeon 2.5 with 4g of RAM.

Our access pattern is a little odd; about half the database is wipe
and regenerated at approximately 1-2 month intervals (the regeneration
takes about 2 weeks); in between there's a nightly computation run
that creates a small amount of new data in two of the tables. Both the
regeneration and the addition of the new data depends very heavily on
many, many several table joins that generally involve about 50% of the
database at a time. We've been fairly pleased with the performance
overall, though it's taken some tweaking to get individual operations
to perform adequately.

I can't speak to pure load operations; all of our bulk-load style ops
are 4k-row COPY commands interspersed among a lot of big, complicated
aggregate queries- not exactly ideal from a cache perspective.

Concurrent readers are anywhere from 1-8, and we're not in a cluster.
Sequential transfer rate is usually a touch over 100mb/sec; we don't
have a lot of disks on this machine (though that may change.... oh how
some of our index scans long for more spindles).

The performance improvements made in the past few releases have been
incredibly helpful- and very much noticeable each time.

--
- David T. Wilson
david.t.wilson@gmail.com