Skip site navigation (1) Skip section navigation (2)

Still big problems with pg_dump!

From: Wim <wdh(at)belbone(dot)be>
To: pgsql-admin(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org,pgsql-hackers(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Still big problems with pg_dump!
Date: 2002-09-17 08:11:41
Message-ID: 3D86E3BD.1020702@belbone.be (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-generalpgsql-hackerspgsql-novice

Hello guys,

I have still problems with dumping my database....

I have postgres 7.2.1 running on a solaris 8 server. When I try to do a 
pg_dump of my database, I get the following message:
pg_dump: query to obtain list of tables failed: server closed the 
connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

I connect to the database and try to view the tables with \dt and \dS. 
Now I get:
ERROR:  AllocSetFree: cannot find block containing chunk 4c5ad0
I retry:
ERROR:  AllocSetFree: cannot find block containing chunk 4860d0

I can view the tables with: \d tablename

Some people suggest a drive failure, but I checked that and found no 
problems...
I REINDEXED the whole database... problem still the same...
Tried a VACUUM... still not working...


I must say that one of the table contains more than 3.000.000 rows, 
another more than 1.400.000...
Select, update, delete, insert works, just the pg_dump(all) and the \dt 
\dS commands...
I must say that I had this problem a few months before, I got some help 
then, but that couldn't solve my problem,
I recreated the database from scratch and copied the data, to fix thing 
quickly. Thing went well for about two months :-(
Now the problem raises again, and I'm trying to find a solution without 
reinstalling the whole thing allover again.


Some advice/help from the specialists?

Cheers!

Wim.

Some info from the debug logfile:
---------------------------------

DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' 
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','')
  AND c.relname !~ '^pg_'
ORDER BY 1;
DEBUG:  parse tree: { QUERY :command 1  :utility <> :resultRelation 0 
:into <> :isPortal false :isBinary false :isTemp false :hasAggs false 
:hasSubLinks false :rtable ({ RTE :relname pg_class :relid 1259  
:subquery <> :alias { ATTR :relname c :attrs <>} :eref { ATTR :relname c 
:attrs ( "relname"   "reltype"   "relowner"   "relam"   "relfilenode"   
"relpages"   "reltuples"   "reltoastrelid"   "reltoastidxid"   
"relhasindex"   "relisshared"   "relkind"   "relnatts"   "relchecks"   
"reltriggers"   "relukeys"   "relfkeys"   "relrefs"   "relhasoids"   
"relhaspkey"   "relhasrules"   "relhassubclass"   "relacl" )} :inh true 
:inFromCl true :checkForRead true :checkForWrite false :checkAsUser 0} { 
RTE :relname pg_user :relid 16478  :subquery <> :alias { ATTR :relname u 
:attrs <>} :eref { ATTR :relname u :attrs ( "usename"   "usesysid"   
"usecreatedb"   "usetrace"   "usesuper"   "usecatupd"   "passwd"   
"valuntil" )} :inh true :inFromCl true :checkForRead true :checkForWrite 
false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({ JOINEXPR 
:jointype 1 :isNatural false :larg { RANGETBLREF 1 } :rarg { RANGETBLREF 
2 } :using <> :quals { EXPR :typeOid 16  :opType op :oper { OPER :opno 
96 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 3 :vartype 
23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 3} { VAR :varno 
2 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 
:varoattno 2})} :alias <> :colnames ( "relname"   "reltype"   
"relowner"   "relam"   "relfilenode"   "relpages"   "reltuples"   
"reltoastrelid"   "reltoastidxid"   "relhasindex"   "relisshared"   
"relkind"   "relnatts"   "relchecks"   "reltriggers"   "relukeys"   
"relfkeys"   "relrefs"   "relhasoids"   "relhaspkey"   "relhasrules"   
"relhassubclass"   "relacl"   "usename"   "usesysid"   "usecreatedb"   
"usetrace"   "usesuper"   "usecatupd"   "passwd"   "valuntil" ) :colvars 
({ VAR :varno 1 :varattno 1 :vartype 19 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 1} { VAR :varno 1 :varattno 2 :vartype 26 
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 2} { VAR :varno 1 
:varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 3} { VAR :varno 1 :varattno 4 :vartype 26 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 4} { VAR :varno 1 :varattno 5 
:vartype 26 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 5} { 
VAR :varno 1 :varattno 6 :vartype 23 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 6} { VAR :varno 1 :varattno 7 :vartype 700 
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7} { VAR :varno 1 
:varattno 8 :vartype 26 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 8} { VAR :varno 1 :varattno 9 :vartype 26 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 9} { VAR :varno 1 :varattno 10 
:vartype 16 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 10} { 
VAR :varno 1 :varattno 11 :vartype 16 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 11} { VAR :varno 1 :varattno 12 :vartype 18 
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12} { VAR :varno 1 
:varattno 13 :vartype 21 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 13} { VAR :varno 1 :varattno 14 :vartype 21 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 14} { VAR :varno 1 :varattno 15 
:vartype 21 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 15} { 
VAR :varno 1 :varattno 16 :vartype 21 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 16} { VAR :varno 1 :varattno 17 :vartype 21 
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 17} { VAR :varno 1 
:varattno 18 :vartype 21 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 18} { VAR :varno 1 :varattno 19 :vartype 16 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 19} { VAR :varno 1 :varattno 20 
:vartype 16 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 20} { 
VAR :varno 1 :varattno 21 :vartype 16 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 21} { VAR :varno 1 :varattno 22 :vartype 16 
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 22} { VAR :varno 1 
:varattno 23 :vartype 1034 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 23} { VAR :varno 2 :varattno 1 :vartype 19 :vartypmod -1  
:varlevelsup 0 :varnoold 2 :varoattno 1} { VAR :varno 2 :varattno 2 
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 2} { 
VAR :varno 2 :varattno 3 :vartype 16 :vartypmod -1  :varlevelsup 0 
:varnoold 2 :varoattno 3} { VAR :varno 2 :varattno 4 :vartype 16 
:vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 4} { VAR :varno 2 
:varattno 5 :vartype 16 :vartypmod -1  :varlevelsup 0 :varnoold 2 
:varoattno 5} { VAR :varno 2 :varattno 6 :vartype 16 :vartypmod -1  
:varlevelsup 0 :varnoold 2 :varoattno 6} { VAR :varno 2 :varattno 7 
:vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 7} { 
VAR :varno 2 :varattno 8 :vartype 702 :vartypmod -1  :varlevelsup 0 
:varnoold 2 :varoattno 8})}) :quals { EXPR :typeOid 16  :opType and 
:oper <> :args ({ EXPR :typeOid 16  :opType or :oper <> :args ({ EXPR 
:typeOid 16  :opType op :oper { OPER :opno 92 :opid 0 :opresulttype 16 } 
:args ({ VAR :varno 1 :varattno 12 :vartype 18 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 12} { CONST :consttype 18 
:constlen 1 :constbyval true :constisnull false :constvalue  1 [ 0 0 0 
114 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 92 :opid 0 
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 12 :vartype 18 
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12} { CONST 
:consttype 18 :constlen 1 :constbyval true :constisnull false 
:constvalue  1 [ 0 0 0 0 ] })})} { EXPR :typeOid 16  :opType op :oper { 
OPER :opno 640 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 
:varattno 1 :vartype 19 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 1} { CONST :consttype 25 :constlen -1 :constbyval false 
:constisnull false :constvalue  8 [ 0 0 0 8 94 112 103 95 ] })})}} 
:rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 
:restype 19 :restypmod -1 :resname Name :reskey 0 :reskeyop 0 
:ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 1 
:vartype 19 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { 
TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname 
Type :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
CASE :casetype 25 :arg <> :args ({ WHEN { EXPR :typeOid 16  :opType op 
:oper { OPER :opno 92 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 
:varattno 12 :vartype 18 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 12} { CONST :consttype 18 :constlen 1 :constbyval true 
:constisnull false :constvalue  1 [ 0 0 0 114 ] })} :then { CONST 
:consttype 25 :constlen -1 :constbyval false :constisnull false 
:constvalue  9 [ 0 0 0 9 116 97 98 108 101 ] }} { WHEN { EXPR :typeOid 
16  :opType op :oper { OPER :opno 92 :opid 0 :opresulttype 16 } :args ({ 
VAR :varno 1 :varattno 12 :vartype 18 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 12} { CONST :consttype 18 :constlen 1 :constbyval 
true :constisnull false :constvalue  1 [ 0 0 0 118 ] })} :then { CONST 
:consttype 25 :constlen -1 :constbyval false :constisnull false 
:constvalue  8 [ 0 0 0 8 118 105 101 119 ] }} { WHEN { EXPR :typeOid 16  
:opType op :oper { OPER :opno 92 :opid 0 :opresulttype 16 } :args ({ VAR 
:varno 1 :varattno 12 :vartype 18 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 12} { CONST :consttype 18 :constlen 1 :constbyval 
true :constisnull false :constvalue  1 [ 0 0 0 105 ] })} :then { CONST 
:consttype 25 :constlen -1 :constbyval false :constisnull false 
:constvalue  9 [ 0 0 0 9 105 110 100 101 120 ] }} { WHEN { EXPR :typeOid 
16  :opType op :oper { OPER :opno 92 :opid 0 :opresulttype 16 } :args ({ 
VAR :varno 1 :varattno 12 :vartype 18 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 12} { CONST :consttype 18 :constlen 1 :constbyval 
true :constisnull false :constvalue  1 [ 0 0 0 83 ] })} :then { CONST 
:consttype 25 :constlen -1 :constbyval false :constisnull false 
:constvalue  12 [ 0 0 0 12 115 101 113 117 101 110 99 101 ] }} { WHEN { 
EXPR :typeOid 16  :opType op :oper { OPER :opno 92 :opid 0 :opresulttype 
16 } :args ({ VAR :varno 1 :varattno 12 :vartype 18 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 12} { CONST :consttype 18 
:constlen 1 :constbyval true :constisnull false :constvalue  1 [ 0 0 0 
115 ] })} :then { CONST :consttype 25 :constlen -1 :constbyval false 
:constisnull false :constvalue  11 [ 0 0 0 11 115 112 101 99 105 97 108 
] }}) :defresult { CONST :consttype 25 :constlen -1 :constbyval false 
:constisnull true :constvalue <>}}} { TARGETENTRY :resdom { RESDOM 
:resno 3 :restype 19 :restypmod -1 :resname Owner :reskey 0 :reskeyop 0 
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 1 
:vartype 19 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 1}}) 
:groupClause <> :havingQual <> :distinctClause <> :sortClause ({ 
SORTCLAUSE :tleSortGroupRef 1 :sortop 660 }) :limitOffset <> :limitCount 
<> :setOperations <> :resultRelations ()}
ERROR:  AllocSetFree: cannot find block containing chunk 48a6d8
DEBUG:  AbortCurrentTransaction
DEBUG:  BackendStartup: forked pid=9789 socket=9
postmaster child[9789]: starting with (postgres -d3 -v131072 -p 
belbonedb_v2 )
DEBUG:  InitPostgres
DEBUG:  StartTransactionCommand
DEBUG:  query: set DateStyle to 'ISO'
DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation 0 
:into <> :isPortal false :isBinary false :isTemp false :hasAggs false 
:hasSubLinks false :rtable <> :jointree <> :rowMarks () :targetList <> 
:groupClause <> :havingQual <> :distinctClause <> :sortClause <> 
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG:  ProcessUtility: set DateStyle to 'ISO'
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: set geqo to 'OFF'
DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation 0 
:into <> :isPortal false :isBinary false :isTemp false :hasAggs false 
:hasSubLinks false :rtable <> :jointree <> :rowMarks () :targetList <> 
:groupClause <> :havingQual <> :distinctClause <> :sortClause <> 
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG:  ProcessUtility: set geqo to 'OFF'
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: set ksqo to 'ON'
DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation 0 
:into <> :isPortal false :isBinary false :isTemp false :hasAggs false 
:hasSubLinks false :rtable <> :jointree <> :rowMarks () :targetList <> 
:groupClause <> :havingQual <> :distinctClause <> :sortClause <> 
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG:  ProcessUtility: set ksqo to 'ON'
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: select oid from pg_type where typname='lo'
DEBUG:  parse tree: { QUERY :command 1  :utility <> :resultRelation 0 
:into <> :isPortal false :isBinary false :isTemp false :hasAggs false 
:hasSubLinks false :rtable ({ RTE :relname pg_type :relid 1247  
:subquery <> :alias <> :eref { ATTR :relname pg_type :attrs ( 
"typname"   "typowner"   "typlen"   "typprtlen"   "typbyval"   
"typtype"   "typisdefined"   "typdelim"   "typrelid"   "typelem"   
"typinput"   "typoutput"   "typreceive"   "typsend"   "typalign"   
"typstorage"   "typdefault" )} :inh true :inFromCl true :checkForRead 
true :checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR 
:fromlist ({ RANGETBLREF 1 }) :quals { EXPR :typeOid 16  :opType op 
:oper { OPER :opno 93 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 
:varattno 1 :vartype 19 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 1} { CONST :consttype 19 :constlen 32 :constbyval false 
:constisnull false :constvalue  32 [ 108 111 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ] })}} :rowMarks () :targetList ({ 
TARGETENTRY :resdom { RESDOM :resno 1 :restype 26 :restypmod -1 :resname 
oid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno -2 :vartype 26 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno -2}}) :groupClause <> :havingQual <> 
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <> 
:setOperations <> :resultRelations ()}
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: select version()
DEBUG:  parse tree: { QUERY :command 1  :utility <> :resultRelation 0 
:into <> :isPortal false :isBinary false :isTemp false :hasAggs false 
:hasSubLinks false :rtable <> :jointree { FROMEXPR :fromlist <> :quals 
<>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 
:restype 25 :restypmod -1 :resname version :reskey 0 :reskeyop 0 
:ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 25  :opType 
func :oper { FUNC :funcid 89 :functype 25 } :args <>}}) :groupClause <> 
:havingQual <> :distinctClause <> :sortClause <> :limitOffset <> 
:limitCount <> :setOperations <> :resultRelations ()}
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: select pg_client_encoding()
DEBUG:  parse tree: { QUERY :command 1  :utility <> :resultRelation 0 
:into <> :isPortal false :isBinary false :isTemp false :hasAggs false 
:hasSubLinks false :rtable <> :jointree { FROMEXPR :fromlist <> :quals 
<>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 
:restype 19 :restypmod -1 :resname pg_client_encoding :reskey 0 
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 
19  :opType func :oper { FUNC :funcid 810 :functype 19 } :args <>}}) 
:groupClause <> :havingQual <> :distinctClause <> :sortClause <> 
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT Config, nValue FROM MSysConf
ERROR:  Relation "msysconf" does not exist
DEBUG:  AbortCurrentTransaction
DEBUG:  StartTransactionCommand





Responses

pgsql-novice by date

Next:From: Joel RodriguesDate: 2002-09-17 10:06:37
Subject: 7.2.2 java configure problem on Mac OS X 10.1.5
Previous:From: Tom LaneDate: 2002-09-17 06:07:31
Subject: Re: Postgres storing time in strange manner

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2002-09-17 08:11:47
Subject: Re: PGXLOG variable worthwhile?
Previous:From: Christopher Kings-LynneDate: 2002-09-17 08:04:38
Subject: Re: PGXLOG variable worthwhile?

pgsql-admin by date

Next:From: Gavin SherryDate: 2002-09-17 13:50:49
Subject: Re: Still big problems with pg_dump!
Previous:From: Tahira AslamDate: 2002-09-17 07:08:09
Subject: Re: Could not connect to remote socket

pgsql-general by date

Next:From: Christopher Kings-LynneDate: 2002-09-17 08:11:47
Subject: Re: PGXLOG variable worthwhile?
Previous:From: Christopher Kings-LynneDate: 2002-09-17 08:04:38
Subject: Re: PGXLOG variable worthwhile?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group