FW: pg_dump: schema with OID 2200 does not exist - Mailing list pgsql-admin

> Date: Wed, 9 May 2012 09:58:45 -0400
> From: chander.ganesan@gmail.com
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] pg_dump: schema with OID 2200 does not exist
>
> Hi All,
>
> I'm running into a weird issue with PostgreSQL 9.1.3 and PostGIS 2.0
> when trying to dump a table - no matter what table I try to dump in this
> database, I find that I get the same error, as evidenced below (scroll
> down for relevant data/error output.)
>
> Any ideas as to what might be the root cause of it ?
>
> The error, and some output from relevant queries is below. In this
> case, those things that are "supposed" to live in 2200 seem to be some
> PostGIS related tables and views - which were moved after installation
> (IIRC) using the alter extension statement (moved into the PostGIS
> schema.) Though the original public schema still exists...
>
> I've got another database (also with postgis 2.0 installed) where it's
> not possible for some reason to dump the postgis.spatial_ref_sys table
> (dumps return nothing, though I can query the table directly.)
>
> Thanks
>
> Chander
>
> (erma)chander@ermadev2:/data/dwhresponder/ERMA/logs$ pg_dump -t
> arctic.data_layer erma
>
> pg_dump: schema with OID 2200 does not exist
>
> (erma)chander@ermadev2:/data/dwhresponder/ERMA/logs$ psql erma
>
> psql (9.1.3)
>
> Type "help" for help.
>
> erma=# \pset pager
>
> erma=# select oid,* from pg_namespace ;
>
> oid | nspname | nspowner |
>
> nspacl
>
> ---------+--------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 11125 | pg_toast_temp_1 | 10 | {postgres=UC/postgres}
>
> 11 | pg_catalog | 10 |
> {postgres=UC/postgres,=U/postgres}
>
> 11394 | information_schema | 10 |
> {postgres=UC/postgres,=U/postgres}
>
> 99 | pg_toast | 10 | {postgres=UC/postgres}
>
> 19061 | postgis | 16384 |
> {chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}
>
> 11124 | pg_temp_1 | 10 | {postgres=UC/postgres}
>
> 6887848 | pacific | 16384 |
> {chander=UC/chander,erma_pacific=UC/chander}
>
> 7163349 | gulfofmexico | 16384 |
> {chander=UC/chander,erma_gomex=UC/chander,erma_gulfofmexico=UC/chander}
>
> 6292634 | arctic | 16384 |
> {chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=UC/chander}
>
> 6834227 | newengland | 16384 |
> {chander=UC/chander,erma_newengland=UC/chander}
>
> 7114095 | southwest | 16384 |
> {chander=UC/chander,erma_southwest=UC/chander}
>
> 5973178 | public | 16384 |
> {chander=UC/chander,=U/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}
>
> 6982864 | northwest | 16384 |
> {chander=UC/chander,erma_northwest=UC/chander}
>
> 6708470 | caribbean | 16384 |
> {chander=UC/chander,erma_caribbean=UC/chander}
>
> 6795143 | fireresponse | 16384 |
> {chander=UC/chander,erma_fireresponse=UC/chander}
>
> 18916 | topology | 16384 |
> {chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}
>
> 6785026 | atlantic | 16384 |
> {chander=UC/chander,erma_atlantic=UC/chander}
>
> (17 rows)
>
> erma=# SELECT tableoid, oid, typname, typnamespace, (SELECT rolname FROM
> pg_catalog.pg_roles WHERE oid = typowner) AS rolname, typinput::oid AS
> typinput, typoutput::oid AS typoutput, typelem, typrelid, CASE WHEN
> typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class WHERE
> oid = typrelid) END AS typrelkind, typtype, typisdefined, typname[0] =
> '_' AND typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid =
> pg_type.typelem) = oid AS isarray FROM pg_type where typnamespace=2200
> ;
> tableoid | oid | typname | typnamespace | rolname |
> typinput | typoutput | typelem | typrelid | typrelkind | typtype |
> typisdefined | isarray
> ----------+-------+--------------------+--------------+---------+----------+-----------+---------+----------+------------+---------+--------------+---------
> 1247 | 18045 | spatial_ref_sys | 2200 | chander |
> 2290 | 2291 | 0 | 18043 | r | c | t
> | f
> 1247 | 18044 | _spatial_ref_sys | 2200 | chander |
> 750 | 751 | 18045 | 0 | | b | t
> | t
> 1247 | 18351 | geography_columns | 2200 | chander |
> 2290 | 2291 | 0 | 18349 | v | c | t
> | f
> 1247 | 18350 | _geography_columns | 2200 | chander |
> 750 | 751 | 18351 | 0 | | b | t
> | t
> 1247 | 18455 | geometry_columns | 2200 | chander |
> 2290 | 2291 | 0 | 18453 | v | c | t
> | f
> 1247 | 18454 | _geometry_columns | 2200 | chander |
> 750 | 751 | 18455 | 0 | | b | t
> | t
> 1247 | 18897 | raster_columns | 2200 | chander |
> 2290 | 2291 | 0 | 18895 | v | c | t
> | f
> 1247 | 18896 | _raster_columns | 2200 | chander |
> 750 | 751 | 18897 | 0 | | b | t
> | t
> 1247 | 18906 | raster_overviews | 2200 | chander |
> 2290 | 2291 | 0 | 18904 | v | c | t
> | f
> 1247 | 18905 | _raster_overviews | 2200 | chander |
> 750 | 751 | 18906 | 0 | | b | t
> | t
> (10 rows)
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin