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

pg_dump: schema with OID 2200 does not exist

From: Chander Ganesan <chander(dot)ganesan(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: pg_dump: schema with OID 2200 does not exist
Date: 2012-05-09 13:58:45
Message-ID: 4FAA7815.2000900@gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)ermadev2:/data/dwhresponder/ERMA/logs$ pg_dump -t 
arctic.data_layer erma

pg_dump: schema with OID 2200 does not exist

(erma)chander(at)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)


Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2012-05-09 14:20:21
Subject: Re: pg_dump: schema with OID 2200 does not exist
Previous:From: GnanakumarDate: 2012-05-09 10:41:10
Subject: Re: Advice/guideline on increasing shared_buffers and kernel parameters

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