Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 89BDC9E8852 for ; Wed, 9 May 2012 10:59:02 -0300 (ADT) Received: from mail-qa0-f49.google.com ([209.85.216.49]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SS7Pw-0003UD-S6 for pgsql-admin@postgresql.org; Wed, 09 May 2012 13:59:02 +0000 Received: by qabj40 with SMTP id j40so503314qab.1 for ; Wed, 09 May 2012 06:58:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject :content-type:content-transfer-encoding; bh=UNyQt3OI3gauV+E3myq6DYXoV+hVTjV3ekJwOXv2Mhw=; b=VmMTEJiP4PgYbqFyOpIq6amhRVhT4dUBYHnhgmTxzpZy356oOhnrFzu1NwOEa5wbML iSDF1kvx5r4L+OnsdM/QxR+WxzttUumRXsEaRswfYtXQaQQanLlTxQiDvPDsG52uS35O 1HkhzxmU1ReZtE/S/yHcjtqi8bYvltimGnujrRP+KiRLItHB9Ms6dq0pwtr53MhE5eZQ yvwV19uMS/f3aMYisJJVPCLpmLIqZgbelVV0FLOy8ZhWI0kTdKS9wcD8lER99pHBU3C6 aaVMJIYsdikCHXUZda4Zt8xefODCKrR6ak6tL8vVCfWs7nxe3z6g3Lo1yuRU2SH08FRx xYvw== Received: by 10.224.116.5 with SMTP id k5mr5305245qaq.19.1336571928005; Wed, 09 May 2012 06:58:48 -0700 (PDT) Received: from Chander-Ganesans-MacBook-Pro-2.local (cpe-071-070-226-197.nc.res.rr.com. [71.70.226.197]) by mx.google.com with ESMTPS id df8sm10324366qab.6.2012.05.09.06.58.46 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 09 May 2012 06:58:46 -0700 (PDT) Message-ID: <4FAA7815.2000900@gmail.com> Date: Wed, 09 May 2012 09:58:45 -0400 From: Chander Ganesan User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7; rv:12.0) Gecko/20120428 Thunderbird/12.0.1 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: pg_dump: schema with OID 2200 does not exist Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201205/46 X-Sequence-Number: 39535 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)