Errors on pg_dumpall

[Terry Khatri]

Somebody PLEASE HELP ! is Tome Lane around !
Tks
T

---------- Forwarded message ----------
From: Terry Khatri <***@gmail.com>
Date: 22 October 2012 00:20
Subject: Errors on pg_dumpall
To: pgsql-admin-***@postgresql.org


Hi

I am getting the following error msg when I do a pg_dumpall

-----

***@OraLinux /usr/local/pgsql/backups
$ pg_dumpall -U sns84 > completebackup20121020,sql
pg_dump: schema with OID 74363 does not exist
pg_dumpall: pg_dump failed on database "bihistory", exiting

-----

I have set the logs to debug5 to catch where the problem is while taking
the dump but nothing shows up there.

If you have any ideas please help me.

And thanks to all for responding to my earlier post.

Rgds
T

[Fernando Hevia]

You are posting to the wrong list.
You should address this in the
pgsql-admin<http://archives.postgresql.org/pgsql-admin/>
list.

Post by Terry Khatri
Somebody PLEASE HELP ! is Tome Lane around !
Tks
T
---------- Forwarded message ----------
Date: 22 October 2012 00:20
Subject: Errors on pg_dumpall
Hi
I am getting the following error msg when I do a pg_dumpall
-----
$ pg_dumpall -U sns84 > completebackup20121020,sql
pg_dump: schema with OID 74363 does not exist
pg_dumpall: pg_dump failed on database "bihistory", exiting
-----
I have set the logs to debug5 to catch where the problem is while taking
the dump but nothing shows up there.
If you have any ideas please help me.
And thanks to all for responding to my earlier post.
Rgds
T

[Fernando Hevia]

Oops. You were posting to the right list.
I took it for the pgAdmin list. Sorry. :)

Post by Fernando Hevia
You are posting to the wrong list.
You should address this in the pgsql-admin<http://archives.postgresql.org/pgsql-admin/>
list.

Post by Terry Khatri
Somebody PLEASE HELP ! is Tome Lane around !
Tks
T
---------- Forwarded message ----------
Date: 22 October 2012 00:20
Subject: Errors on pg_dumpall
Hi
I am getting the following error msg when I do a pg_dumpall
-----
$ pg_dumpall -U sns84 > completebackup20121020,sql
pg_dump: schema with OID 74363 does not exist
pg_dumpall: pg_dump failed on database "bihistory", exiting
-----
I have set the logs to debug5 to catch where the problem is while taking
the dump but nothing shows up there.
If you have any ideas please help me.
And thanks to all for responding to my earlier post.
Rgds
T

[raghu ram]

Post by Terry Khatri
Hi
I am getting the following error msg when I do a pg_dumpall
-----
$ pg_dumpall -U sns84 > completebackup20121020,sql
pg_dump: schema with OID 74363 does not exist
pg_dumpall: pg_dump failed on database "bihistory", exiting
-----
I have set the logs to debug5 to catch where the problem is while taking
the dump but nothing shows up there.
If you have any ideas please help me.
And thanks to all for responding to my earlier post.

Below URL provides more information about this issue:

http://archives.postgresql.org/pgsql-bugs/2005-11/msg00307.php

[Craig Ringer]

Post by Terry Khatri
Somebody PLEASE HELP ! is Tome Lane around !

http://www.postgresql.org/support/professional_support/

[Lukasz Brodziak]

Post by Terry Khatri
Somebody PLEASE HELP ! is Tome Lane around !
http://www.postgresql.org/support/professional_support/

Hi,

Run the following SELECT on the database: select oid,* from
pg_namespace this will bring up all the schemas in the db along with
the OIDs. Check what schema name is under the OID given in error then
try to delete all the referrences to this schema name from db and run
pg_dumpall again.
Of course before doing so stop the PostgreSQL server and make a copy
of the data folder just in case.

Regards
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"

[Terry Khatri]

Hi
Okay, I have found a reference to that oid in pg_type as under :


select * from pg_type where typnamespace=74363 ;

-[ RECORD 1 ]--+-----------------

typname | city

typnamespace | 74363

typowner | 10

typlen | -1

typbyval | f

typtype | c

typcategory | C

typispreferred | f

typisdefined | t

typdelim | ,

typrelid | 74364

typelem | 0

typarray | 74365

typinput | record_in

typoutput | record_out

typreceive | record_recv

typsend | record_send

typmodin | -

typmodout | -

typanalyze | -

typalign | d

typstorage | x

typnotnull | f

typbasetype | 0

typtypmod | -1

typndims | 0

typcollation | 0

typdefaultbin |

typdefault |

typacl |

-[ RECORD 2 ]--+-----------------

typname | _city

typnamespace | 74363

typowner | 10

typlen | -1

typbyval | f

typtype | b

typcategory | A

typispreferred | f

typisdefined | t

typdelim | ,

typrelid | 0

typelem | 74366

typarray | 0

typinput | array_in

typoutput | array_out

typreceive | array_recv

typsend | array_send

typmodin | -

typmodout | -

typanalyze | array_typanalyze

typalign | d

typstorage | x

typnotnull | f

typbasetype | 0

typtypmod | -1

typndims | 0

typcollation | 0

typdefaultbin |

typdefault |

typacl |

Here is what I have in pg_namespace :

oid | nspname | nspowner | nspacl

-------+--------------------+----------+----------------------------

99 | pg_toast | 10 |

11194 | pg_temp_1 | 10 |

11195 | pg_toast_temp_1 | 10 |

11 | pg_catalog | 10 | {sns84=UC/sns84,=U/sns84}

2200 | public | 10 | {sns84=UC/sns84,=UC/sns84}

11459 | information_schema | 10 | {sns84=UC/sns84,=U/sns84}

82537 | world | 10 |

(7 rows)

What I should do next ?

Please help

Thanks

T

Post by Lukasz Brodziak

Post by Terry Khatri
Somebody PLEASE HELP ! is Tome Lane around !
http://www.postgresql.org/support/professional_support/

Hi,
Run the following SELECT on the database: select oid,* from
pg_namespace this will bring up all the schemas in the db along with
the OIDs. Check what schema name is under the OID given in error then
try to delete all the referrences to this schema name from db and run
pg_dumpall again.
Of course before doing so stop the PostgreSQL server and make a copy
of the data folder just in case.
Regards
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"

[Tom Lane]

Post by Terry Khatri
select * from pg_type where typnamespace=74363 ;

Okay, so you have a composite type named "city" (either a table's
rowtype, or a standalone composite type --- hard to tell from just this
entry) that somehow escaped deletion when the containing schema was
deleted. Do you have an idea what triggered that? A reproducible test
case would be even better.

Post by Terry Khatri
What I should do next ?

Well, you can just manually delete those rows in pg_type, if you're sure
that there's nothing else linking to them. It might be safer to try to
do it as "drop type city" first.

regards, tom lane