Thread: pg_restore fails on Windows
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?
Tomas
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
> 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 (testedon 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 (testedon 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,
bestpractices 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
onetable 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 onetable 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 onetable 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 perclient , what I am wondering is how multiple agent process share page caches. In many other databases, client agents usesthe multiple thread method, so they can share memory buffers within the same process, are there share-memory mechanismsbetween 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?
Ow Mun Heng wrote:
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.-----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?
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. >
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
In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>: > 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. When talking about pure read performance, the basic rule is the more spindles you can have active simultaneously, the better. By that rule, RAID 0 is the best, but you have to balance that with reliability. If you have 10 disks in a RAID 0, the chance of the entire system going down because of a disk failure is 10x that of a single disk system -- is that acceptable? In theory, you can have so many disks that the bottleneck moves to some other location, such as the IO bus or memory or the CPU, but I've never heard of that happening to anyone. Also, you want to get fast, high- quality disks, as 10 15,000 RPM disks are going to perform better than 10 7,200 RPM disks. Another solution is RAM, if you can get enough RAM in the system to hold your working set of data, then the speed of the disk is not really relevant. Of course, that's tough to do if you've got 3TB of data, which I don't know if that's your case or not. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
On Tue, 2008-08-19 at 02:28 -0400, David Wilson wrote: > 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: I didn't realise the initial questions from this and since I'm lazy to look for the original mail, I'll put in my 2 cents worth. DB is a DSS type store instead of OLTP type. Heavily denormalised data. Master is a celeron 1.7Ghz, 768MB ram, 2x500GB 7200rpm IDE RAID1(data)+ 1 spare, 1x80GB (system). Slave is a celeron 1.7Ghz, 1.5GB RAM, 3x160GB 7200rpm IDE RAID1(data), 1x160GB system Max columns ~120 DB size is ~200+GB ~600+M (denormalised) rows in ~60+ tables (partitioned and otherwise) vacuum is done nightly in addition to turning on autovacuum. I'm both IO and CPU constrainted. :-) Denormalisation/ETL process is done on the master and only the final product is shipped to the slave for read-only via slony. I've got close to 8 indexes on each table (for bitmap scanning) Due to the denormalisation, gettin to the data is very snappy even based on such a "small" server. (adding ram to the slave saw drastic performance improvement over the initial 512MB) Currently looking for an FOSS implementation of a Slice and Dice kind of drilldown for reporting purposes. Tried a variety including pentaho, but never been able to get it set-up.
On Tue, 2008-08-19 at 07:34 -0400, Bill Moran wrote: > > In theory, you can have so many disks that the bottleneck moves to > some > other location, such as the IO bus or memory or the CPU, but I've > never > heard of that happening to anyone. Also, you want to get fast, high- > quality disks, as 10 15,000 RPM disks are going to perform better than > 10 7,200 RPM disks. I've personally experienced this happening. -Mark
On Sat, 2008-08-16 at 11:42 +0800, Amber 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? > 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 onetable scaning. > 8. Single instance or a cluster, what cluster software are you using if you have a cluster? > > Thank you in advance! 1. 2.5-3TB, several others that are of fractional sisize. ... 5. They do pretty well, actually. Our aggregate fact tables regularly join to metadata tables and we have an average query return time of 10-30s. We do make some usage of denormalized mviews for chained/hierarchical metadata tables. 6. Load/copy operations are extremely performant. We pretty well constantly have 10+ concurrent load operations going with 2-3 aggregation processes. 7. About 50, but I'm not sure what the transfer rate is. 8. We have a master and a replica. We have plans to move to a cluster/grid Soon(TM). It's not an emergency and Postgres can easily handle and scale to a 3TB database on reasonable hardware (<$30k). A few notes: our database really can be broken into a very typical ETL database: medium/high input (write) volume with low latency access required. I can provide a developer's view of what is necessary to keep a database of this size running, but I'm under no illusion that it's actually a "large" database. I'd go into more details, but I'd hate to be rambling. If anyone's actually interested about any specific parts, feel free to ask. :) Also, if you feel that we're doing "something wrong", feel free to comment there too. :) -Mark
Mark Roberts wrote: > 1. 2.5-3TB, several others that are of fractional sisize. > > > ... > > > 5. They do pretty well, actually. Our aggregate fact tables regularly > join to metadata tables and we have an average query return time of > 10-30s. We do make some usage of denormalized mviews for > chained/hierarchical metadata tables. > > Just out of curiosity, how do you replicate that amount of data? > ... > > A few notes: our database really can be broken into a very typical ETL > database: medium/high input (write) volume with low latency access > required. I can provide a developer's view of what is necessary to keep > a database of this size running, but I'm under no illusion that it's > actually a "large" database. > > I'd go into more details, but I'd hate to be rambling. If anyone's > actually interested about any specific parts, feel free to ask. :) > I'd be very interested in a developers view of running and maintaining a database this size. Mostly what choices is made during development that might have been different on a smaller database. I'm also curious about the maintenance needed to keep a database this size healthy over time. Regards, /roppert > Also, if you feel that we're doing "something wrong", feel free to > comment there too. :) > > -Mark > > >
> Just out of curiosity, how do you replicate that amount of data? When I started working here, we used Slony-I to replicate our aggregate fact tables. A little over a year ago our data volume had grown to the point that the Slony was regularly unable to keep up with the data volume and around this time Slony hit us with some crazy rollback based data loss bug. We elected to move our aggregate fact tables off of slony, but left metadata tables on Slony. So I wrote a custom in house replication engine into our aggregation process. Replication is accomplished in parallel via piped copy statements, and so far it's been fast enough to keep up with the data volume. Without getting into too much detail, an import process might look like this: - Obtain and transform data into a usable state - Import Data, and earmark it to be reaggregated - Reaggregate (summarize) the changed data - Copy the new data to all databases in the replication set - Integrate new data into the aggregate fact tables > I'd be very interested in a developers view of running and maintaining a > database this size. > Mostly what choices is made during development that might have been > different on a smaller database. > I'm also curious about the maintenance needed to keep a database this > size healthy over time. Ok, so all of this comes from what might be termed as an "ETL Developer" point of view. I pretty much only work on the data warehouse's import/summarization process and look into performance issues. Design: - Natural keys are pretty well strictly forbidden. Don't make a VARCHAR your primary key, please. ;-) - The Data determines partition criteria and the application layer is partition aware. - It's important to have aggregate tables to support common queries. Joins are ok, but repeatedly aggregating thousands of rows together on the fly really takes too much time. - Aggregation processes have to be designed with care. - Parallel processing is important, especially if you ever have to reprocess large amounts of data (for example due to incorrect initial data) Maintenance: - Autovacuum might be more trouble than it's worth. We frequently have mysteriously hung queries that are eventually traced back to being blocked by Autovacuum. The application layer knows exactly what and when is changing... it could *probably* take over this duty. - Pg upgrades are a major PITA and require absurd amounts of downtime for the data processing part of the warehouse. - Queries that have been working for long periods of time and suddenly stop working or hang are usually broken by statistics issues. - Partitioning is important because it allows the aggregate tables to be clustered, backed up, and archived individually If anyone wants to chat with me or direct me to resources about running Postgres on distributed file systems (or other grid computing solutions) please let me know. Yes, I'm aware of the proprietary solutions, but they've been ruled out on account of performance problems with aggregates and absurd licensing costs. Also, any grid computing solution that I write can easily be applied across the company and thus save us (quite literally) millions per year. Again, if you'd like more information about any particular topic, just ask. :) -Mark
> On Sat, 2008-08-16 at 11:42 +0800, Amber 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? >> 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 onetable scaning. >> 8. Single instance or a cluster, what cluster software are you using if you have a cluster? >> >> Thank you in advance! > > 1. 2.5-3TB, several others that are of fractional sisize. How many CPU cores and memory does your server have :)
Another question, how many people are there maintaining this huge database. We have about 2T of compressed SAS datasets, and now considering load them into a RDBMS database, according to your experience, it seems a single PostgreSQL instance can't manage such size databases well, it that right? -------------------------------------------------- From: "Amber" <guxiaobo1982@hotmail.com> Sent: Thursday, August 21, 2008 9:51 PM To: "Mark Roberts" <mailing_lists@pandapocket.com> Cc: <pgsql-general@postgresql.org> Subject: Re: [GENERAL] What's size of your PostgreSQL Database? > > >> On Sat, 2008-08-16 at 11:42 +0800, Amber 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? >>> 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 doingone table scaning. >>> 8. Single instance or a cluster, what cluster software are you using if you have a cluster? >>> >>> Thank you in advance! >> >> 1. 2.5-3TB, several others that are of fractional sisize. > > > How many CPU cores and memory does your server have :) > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Thu, 2008-08-21 at 22:17 +0800, Amber wrote: > Another question, how many people are there maintaining this huge database. > We have about 2T of compressed SAS datasets, and now considering load them into a RDBMS database, > according to your experience, it seems a single PostgreSQL instance can't manage such size databases well, it that right? Yahoo has a 2PB Postgres single instance Postgres database (modified engine), but the biggest pure Pg single instance I've heard of is 4TB. The 4TB database has the additional interesting property in that they've done none of the standard "scalable" architecture changes (such as partitioning, etc). To me, this is really a shining example that even naive Postgres databases can scale to as much hardware as you're willing to throw at them. Of course, clever solutions will get you much more bang for your hardware buck. As for my personal experience, I'd say that the only reason that we're currently running a dual Pg instance (Master/Replica/Hot Standby) configuration is for report times. It's really important to us to have snappy access to our data warehouse. During maintenance our site and processes can easily be powered by the master database with some noticeable performance degradation for the users. The "grid" that we (I) am looking to build is coming out of changing (yet ever static!) business needs: we're looking to immediately get 2x the data volume and soon need to scale to 10x. Couple this with increased user load and the desire to make reports run even faster than they currently do and we're really going to run up against a hardware boundary. Besides, writing grid/distributed databases is *fun*! Uh, for a one sentence answer: A single Pg instance can absolutely handle 2+ TB without flinching. > How many CPU cores and memory does your server have :) My boss asked me not to answer the questions I missed... sorry. I will say that the hardware is pretty modest, but has good RAM and disk space. -Mark
> 8. We have a master and a replica. We have plans to move to a > cluster/grid Soon(TM). It's not an emergency and Postgres can easily > handle and scale to a 3TB database on reasonable hardware (<$30k). > I'd like to know what's your progress of choosing the cluster/grid solution, we are also looking for an appropriate one, following is the our major factors of the ideal solution. 1. Some kind of MPP. 2. No single point of failure. 3. Convenient and multiple access interfaces. And following the is the solutions we have examined: 1. Slony-I: Not a MPP solution, and using triggers to detect changes, which defects performance. 2. pgpool-II: Some kind of MPP, but join operations can't be done on multiple machines parallelly, that is it can't scaleout well. 3. Sequoia : The same problem as pgpool-II, and the major access interface is JDBC.
> Yahoo has a 2PB Postgres single instance Postgres database (modified > engine), but the biggest pure Pg single instance I've heard of is 4TB. > The 4TB database has the additional interesting property in that they've > done none of the standard "scalable" architecture changes (such as > partitioning, etc). To me, this is really a shining example that even > naive Postgres databases can scale to as much hardware as you're willing > to throw at them. Of course, clever solutions will get you much more > bang for your hardware buck. Can you share some ideas of the particular design of the 4T db, it sounds very interesting :)
On Wed, 10 Sep 2008 23:17:40 +0800 "Amber" <guxiaobo1982@hotmail.com> wrote: > > 1. Some kind of MPP. > 2. No single point of failure. > 3. Convenient and multiple access interfaces. > > And following the is the solutions we have examined: http://www.greenplum.com/ Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Yes, we know both Greenplum and Netezza are PostgreSQL based MPP solutions, but they are commercial packages. I'd like to know are there open source ones, and I would suggest the PostgreSQL Team to start a MPP version of PostgreSQL. -------------------------------------------------- From: "Joshua Drake" <jd@commandprompt.com> Sent: Wednesday, September 10, 2008 11:27 PM To: "Amber" <guxiaobo1982@hotmail.com> Cc: "Mark Roberts" <mailing_lists@pandapocket.com>; <pgsql-general@postgresql.org> Subject: Re: [GENERAL] What's size of your PostgreSQL Database? > On Wed, 10 Sep 2008 23:17:40 +0800 > "Amber" <guxiaobo1982@hotmail.com> wrote: >> >> 1. Some kind of MPP. >> 2. No single point of failure. >> 3. Convenient and multiple access interfaces. >> >> And following the is the solutions we have examined: > > http://www.greenplum.com/ > > Joshua D. Drake > > > -- > The PostgreSQL Company since 1997: http://www.commandprompt.com/ > PostgreSQL Community Conference: http://www.postgresqlconference.org/ > United States PostgreSQL Association: http://www.postgresql.us/ > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > > >
On Wed, 10 Sep 2008 23:33:44 +0800 "Amber" <guxiaobo1982@hotmail.com> wrote: > Yes, we know both Greenplum and Netezza are PostgreSQL based MPP > solutions, but they are commercial packages. I'd like to know are > there open source ones, and I would suggest the PostgreSQL Team to > start a MPP version of PostgreSQL. To my knowledge there are no open source MPP versions of PostgreSQL, further AFAIK MPP is not on the OSS PostgreSQL roadmap. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate