From: Chander Ganesan <chander.ganesan@gmail.com>
To: pgsql-admin@postgresql.org
Subject: pg_dump: schema with OID 2200 does not exist
Date: Wed, 09 May 2012 09:58:45 -0400
Message-ID: <4FAA7815.2000900@gmail.com> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: chander.ganesan@gmail.com
Subject: Re: pg_dump: schema with OID 2200 does not exist
In-Reply-To: <4FAA7815.2000900@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox