Обсуждение: pg_dump not dumping some schemas

Yun Guo wrote:
> I use this command to dump schema:
> pg_dump -s -n test polling_etl  > file.sql
> 
> However, the file.sql file includes creation of all the objects in schema test, but not the creation
> of test schema itself.
> I executed pg_dump as postgres user, the server and pg_dump are version 9.3.5. I suspect it’s missing
> some permissions, but not able to figure out what permissions are missing. Below is the permission on
> test schema:
> 
> polling_etl=# \dn+ test
>                 List of schemas
>  Name | Owner | Access privileges | Description
> ------+-------+-------------------+-------------
>  test | test  | test=UC/test     +|
>       |       | pb_writer=UC/test+|
>       |       | =U/test          +|
>       |       | postgres=U*C/test |
> 

It works on my 9.3.6 database:

$ pg_dump -p 1235 -s -n laurenz test|grep 'CREATE SCHEMA'
CREATE SCHEMA laurenz;

You don't need any permissions to dump a schema, everybody can read the information.

Could you double check with a "grep" or so that there is indeed no CREATE SCHEMA
in your dump?

Yours,
Laurenz Albe

I tried again. It¹s still not dumping CREATE SCHEMA. And weirdly it would
work for some other schemas in the same database.


-bash-4.1$ pg_dump -s -n test polling_etl | grep 'CREATE SCHEMA'
-bash-4.1$ pg_dump -s -n test2 polling_etl | grep 'CREATE SCHEMA'
CREATE SCHEMA test2;




On 5/29/15, 4:06 AM, "Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:

>Yun Guo wrote:
>> I use this command to dump schema:
>> pg_dump -s -n test polling_etl  > file.sql
>>
>> However, the file.sql file includes creation of all the objects in
>>schema test, but not the creation
>> of test schema itself.
>> I executed pg_dump as postgres user, the server and pg_dump are version
>>9.3.5. I suspect it¹s missing
>> some permissions, but not able to figure out what permissions are
>>missing. Below is the permission on
>> test schema:
>>
>> polling_etl=# \dn+ test
>>                 List of schemas
>>  Name | Owner | Access privileges | Description
>> ------+-------+-------------------+-------------
>>  test | test  | test=UC/test     +|
>>       |       | pb_writer=UC/test+|
>>       |       | =U/test          +|
>>       |       | postgres=U*C/test |
>>
>
>It works on my 9.3.6 database:
>
>$ pg_dump -p 1235 -s -n laurenz test|grep 'CREATE SCHEMA'
>CREATE SCHEMA laurenz;
>
>You don't need any permissions to dump a schema, everybody can read the
>information.
>
>Could you double check with a "grep" or so that there is indeed no CREATE
>SCHEMA
>in your dump?
>
>Yours,
>Laurenz Albe

Yun Guo wrote:
>>> I use this command to dump schema:
>>> pg_dump -s -n test polling_etl  > file.sql
>>>
>>> However, the file.sql file includes creation of all the objects in
>>> schema test, but not the creation of test schema itself.

>>> polling_etl=# \dn+ test
>>>                 List of schemas
>>>  Name | Owner | Access privileges | Description
>>> ------+-------+-------------------+-------------
>>>  test | test  | test=UC/test     +|
>>>       |       | pb_writer=UC/test+|
>>>       |       | =U/test          +|
>>>       |       | postgres=U*C/test |

> I tried again. It¹s still not dumping CREATE SCHEMA. And weirdly it would
> work for some other schemas in the same database.
> 
> -bash-4.1$ pg_dump -s -n test polling_etl | grep 'CREATE SCHEMA'
> -bash-4.1$ pg_dump -s -n test2 polling_etl | grep 'CREATE SCHEMA'
> CREATE SCHEMA test2;

That is disturbing.

Does the schema show up in a custom format dump?

pg_dump -s -n test -Fc polling_etl | pg_restore -l

Yours,
Laurenz Albe

It doesn’t show up in custom format dump either… I begin to wonder if it’s
a bug in pg_dump.


-bash-4.1$ pg_dump -s -n test -Fc polling_etl | pg_restore -l | grep SCHEMA
-bash-4.1$ pg_dump -s -n test2 -Fc polling_etl | pg_restore -l | grep
SCHEMA
7; 2615 1131042 SCHEMA - test2 pb_writer



On 6/1/15, 4:07 AM, "Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:

>Yun Guo wrote:
>>>> I use this command to dump schema:
>>>> pg_dump -s -n test polling_etl  > file.sql
>>>>
>>>> However, the file.sql file includes creation of all the objects in
>>>> schema test, but not the creation of test schema itself.
>
>>>> polling_etl=# \dn+ test
>>>>                 List of schemas
>>>>  Name | Owner | Access privileges | Description
>>>> ------+-------+-------------------+-------------
>>>>  test | test  | test=UC/test     +|
>>>>       |       | pb_writer=UC/test+|
>>>>       |       | =U/test          +|
>>>>       |       | postgres=U*C/test |
>
>> I tried again. It¹s still not dumping CREATE SCHEMA. And weirdly it
>>would
>> work for some other schemas in the same database.
>> 
>> -bash-4.1$ pg_dump -s -n test polling_etl | grep 'CREATE SCHEMA'
>> -bash-4.1$ pg_dump -s -n test2 polling_etl | grep 'CREATE SCHEMA'
>> CREATE SCHEMA test2;
>
>That is disturbing.
>
>Does the schema show up in a custom format dump?
>
>pg_dump -s -n test -Fc polling_etl | pg_restore -l
>
>Yours,
>Laurenz Albe

"Guo, Yun" <YGuo@cvent.com> writes:
> It doesn��t show up in custom format dump either�� I begin to wonder if it��s
> a bug in pg_dump.

pg_dump's handling of schemas is pretty darn simple; it's hard to see how
it could "miss" a schema like that.  It would be useful to see the output
of these commands executed in the problematic database:

SELECT oid FROM pg_catalog.pg_namespace n
    WHERE n.nspname ~ '^(test)$';
SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles
    WHERE oid = nspowner) AS rolname, nspacl FROM pg_catalog.pg_namespace;

which are pretty much everything pg_dump issues that's directly related
to dumping schemas.

            regards, tom lane

Here¹s the output:

polling_etl=# SELECT oid FROM pg_catalog.pg_namespace n
polling_etl-# WHERE n.nspname ~ '^(test)$';
  oid
-------
 17972
(1 row)
polling_etl=# SELECT tableoid, oid, nspname, (SELECT rolname FROM
pg_catalog.pg_roles
WHERE oid = nspowner) AS rolname, nspacl FROM pg_catalog.pg_namespace;
 tableoid |   oid   |      nspname       |  rolname  |
                nspacl
----------+---------+--------------------+-----------+---------------------
-----------------------------------------------------------
     2615 |      99 | pg_toast           | postgres  |
     2615 |   11215 | pg_temp_1          | postgres  |
     2615 |   11216 | pg_toast_temp_1    | postgres  |
     2615 |      11 | pg_catalog         | postgres  |
{postgres=UC/postgres,=U/postgres}
     2615 |    2200 | public             | postgres  |
{postgres=UC/postgres,=UC/postgres}
     2615 |   12348 | information_schema | postgres  |
{postgres=UC/postgres,=U/postgres}
     2615 | 1131042 | test2              | pb_writer |
{pb_writer=UC/pb_writer,test2=U/pb_writer}
     2615 |   16389 | polling_etl        | pb_writer |
{pb_writer=UC/pb_writer,polling_etl=UC/pb_writer}
     2615 |   17944 | mdev1              | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev1=U/pb_writ
er}
     2615 |   17946 | mdev2              | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev2=U/pb_writ
er}
     2615 |   17957 | mdev3              | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev3=U/pb_writ
er}
     2615 |   17970 | mdev4              | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev4=U/pb_writ
er}
     2615 |   17972 | test               | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,test=U/pb_write
r}
(13 rows)




pg_dump is missing 5 schemas: mdev1, mdev2, mdev3, mdev4, and test. It
doesn¹t look like much different between those 5 schemas and the other 2.





On 6/1/15, 9:53 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

>
>SELECT oid FROM pg_catalog.pg_namespace n
>    WHERE n.nspname ~ '^(test)$';

Yun Guo wrote:
> It doesn’t show up in custom format dump either… I begin to wonder if it’s
> a bug in pg_dump.
> 
> 
> -bash-4.1$ pg_dump -s -n test -Fc polling_etl | pg_restore -l | grep SCHEMA
> -bash-4.1$ pg_dump -s -n test2 -Fc polling_etl | pg_restore -l | grep
> SCHEMA
> 7; 2615 1131042 SCHEMA - test2 pb_writer

... or your database is corrupt.

Maybe it helps to issue the queries that pg_dump uses:

SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, nspacl FROM
pg_namespace;

SELECT oid FROM pg_catalog.pg_namespace n WHERE (n.nspname = 'test');

Yours,
Laurenz Albe

"Guo, Yun" <YGuo@cvent.com> writes:
> pg_dump is missing 5 schemas: mdev1, mdev2, mdev3, mdev4, and test. It
> doesn�t look like much different between those 5 schemas and the other 2.

Hm.  I'm baffled.  If you're handy with gdb you could try walking through
the calls of selectDumpableNamespace() and dumpNamespace() to see if they
get called at all for the problem schemas and what they do if so.

            regards, tom lane

Here¹s the output:

polling_etl=# SELECT oid FROM pg_catalog.pg_namespace n polling_etl-# WHERE n.nspname ~ '^(test)$';
  oid
-------
 17972
(1 row)
polling_etl=# SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname,
nspaclFROM pg_catalog.pg_namespace;
 
 tableoid |   oid   |      nspname       |  rolname  |
                nspacl
----------+---------+--------------------+-----------+------------------
----------+---------+--------------------+-----------+---
-----------------------------------------------------------
     2615 |      99 | pg_toast           | postgres  |
     2615 |   11215 | pg_temp_1          | postgres  |
     2615 |   11216 | pg_toast_temp_1    | postgres  |
     2615 |      11 | pg_catalog         | postgres  |
{postgres=UC/postgres,=U/postgres}
     2615 |    2200 | public             | postgres  |
{postgres=UC/postgres,=UC/postgres}
     2615 |   12348 | information_schema | postgres  |
{postgres=UC/postgres,=U/postgres}
     2615 | 1131042 | test2              | pb_writer |
{pb_writer=UC/pb_writer,test2=U/pb_writer}
     2615 |   16389 | polling_etl        | pb_writer |
{pb_writer=UC/pb_writer,polling_etl=UC/pb_writer}
     2615 |   17944 | mdev1              | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev1=U/pb_writ
er}
     2615 |   17946 | mdev2              | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev2=U/pb_writ
er}
     2615 |   17957 | mdev3              | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev3=U/pb_writ
er}
     2615 |   17970 | mdev4              | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev4=U/pb_writ
er}
     2615 |   17972 | test               | pb_writer |
{pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,test=U/pb_write
r}
(13 rows)


Is there a way to repair the database if it's corrupted?



-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] 
Sent: Monday, June 01, 2015 10:08 AM
To: Guo, Yun; pgsql-admin@postgresql.org
Subject: RE: pg_dump not dumping some schemas

Yun Guo wrote:
> It doesn’t show up in custom format dump either… I begin to wonder if 
> it’s a bug in pg_dump.
> 
> 
> -bash-4.1$ pg_dump -s -n test -Fc polling_etl | pg_restore -l | grep 
> SCHEMA -bash-4.1$ pg_dump -s -n test2 -Fc polling_etl | pg_restore -l 
> | grep SCHEMA 7; 2615 1131042 SCHEMA - test2 pb_writer

... or your database is corrupt.

Maybe it helps to issue the queries that pg_dump uses:

SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, nspacl FROM
pg_namespace;

SELECT oid FROM pg_catalog.pg_namespace n WHERE (n.nspname = 'test');

Yours,
Laurenz Albe

Thanks Tom.
I've never dealt with gdb before. But it's a great opportunity to start learning. Will keep you updated with my
findings.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, June 01, 2015 10:12 AM
To: Guo, Yun
Cc: Albe Laurenz; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_dump not dumping some schemas

"Guo, Yun" <YGuo@cvent.com> writes:
> pg_dump is missing 5 schemas: mdev1, mdev2, mdev3, mdev4, and test. It
> doesn¹t look like much different between those 5 schemas and the other 2.

Hm.  I'm baffled.  If you're handy with gdb you could try walking through the calls of selectDumpableNamespace() and
dumpNamespace()to see if they get called at all for the problem schemas and what they do if so. 

            regards, tom lane

"Guo, Yun" <YGuo@cvent.com> writes:
> Is there a way to repair the database if it's corrupted?

There's no evidence here that you've got any server-side corruption.
pg_dump should be getting back the exact same results you got
manually.

(BTW, I assume you've ruled out the possibility that pg_dump is connecting
to some other server or database than the one you're looking at
manually...)

            regards, tom lane

Yun Guo wrote:
> Here¹s the output:
> 
> polling_etl=# SELECT oid FROM pg_catalog.pg_namespace n polling_etl-# WHERE n.nspname ~ '^(test)$';
>   oid
> -------
>  17972
> (1 row)
> polling_etl=# SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
> nspowner) AS rolname, nspacl FROM pg_catalog.pg_namespace;
>  tableoid |   oid   |      nspname       |  rolname  |
>                 nspacl
> ----------+---------+--------------------+-----------+------------------
> ----------+---------+--------------------+-----------+---
> -----------------------------------------------------------
>      2615 |      99 | pg_toast           | postgres  |
>      2615 |   11215 | pg_temp_1          | postgres  |
>      2615 |   11216 | pg_toast_temp_1    | postgres  |
>      2615 |      11 | pg_catalog         | postgres  |
> {postgres=UC/postgres,=U/postgres}
>      2615 |    2200 | public             | postgres  |
> {postgres=UC/postgres,=UC/postgres}
>      2615 |   12348 | information_schema | postgres  |
> {postgres=UC/postgres,=U/postgres}
>      2615 | 1131042 | test2              | pb_writer |
> {pb_writer=UC/pb_writer,test2=U/pb_writer}
>      2615 |   16389 | polling_etl        | pb_writer |
> {pb_writer=UC/pb_writer,polling_etl=UC/pb_writer}
>      2615 |   17944 | mdev1              | pb_writer |
> {pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev1=U/pb_writ
> er}
>      2615 |   17946 | mdev2              | pb_writer |
> {pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev2=U/pb_writ
> er}
>      2615 |   17957 | mdev3              | pb_writer |
> {pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev3=U/pb_writ
> er}
>      2615 |   17970 | mdev4              | pb_writer |
> {pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,mdev4=U/pb_writ
> er}
>      2615 |   17972 | test               | pb_writer |
> {pb_writer=UC/pb_writer,=U/pb_writer,postgres=U*C/pb_writer,test=U/pb_write
> r}
> (13 rows)

That looks fine to me.

I don't understand what is going on there.

> Is there a way to repair the database if it's corrupted?

It oes not look corrupted, but if it were, a restore would help.

Yours,
Laurenz Albe

Hi Tom,

I tried gdb on selectDumpableNamespace() and dumpNamespace().
So it looks like for those problematic schemas,the dump value is 1 in
selectDumpableNamespace() which is correct, but when it goes to
dumpNamespace() the dump value becomes 0, so it get skipped.

Breakpoint 2, selectDumpableNamespace (nsinfo=0x6600c0) at pg_dump.c:1227
(gdb) p nsinfo.dobj
$17 = {objType = DO_NAMESPACE, catId = {tableoid = 2615, oid = 17972},
dumpId = 13, name = 0x660d80 "test", namespace = 0x0,
  dump = 1 '\001', ext_member = 0 '\000', dependencies = 0x0, nDeps = 0,
allocDeps = 0}



Breakpoint 1, dumpNamespace (fout=0x654290, nspinfo=0x6600c0) at
pg_dump.c:7930
7930    {
(gdb)  p nspinfo.dobj
$31 = {objType = DO_NAMESPACE, catId = {tableoid = 2615, oid = 17972},
dumpId = 13, name = 0x660d80 "test", namespace = 0x0,
  dump = 0 '\000', ext_member = 1 '\001', dependencies = 0x6e2b60, nDeps =
1, allocDeps = 16}





What could possibly change the dump value between selectDumpableNamespace
and dumpNamespace ?



On 6/1/15, 10:19 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

>"Guo, Yun" <YGuo@cvent.com> writes:
>> Is there a way to repair the database if it's corrupted?
>
>There's no evidence here that you've got any server-side corruption.
>pg_dump should be getting back the exact same results you got
>manually.
>
>(BTW, I assume you've ruled out the possibility that pg_dump is connecting
>to some other server or database than the one you're looking at
>manually...)
>
>            regards, tom lane

"Guo, Yun" <YGuo@cvent.com> writes:
> I tried gdb on selectDumpableNamespace() and dumpNamespace().
> So it looks like for those problematic schemas,the dump value is 1 in
> selectDumpableNamespace() which is correct, but when it goes to
> dumpNamespace() the dump value becomes 0, so it get skipped.

Oh really ...

> Breakpoint 1, dumpNamespace (fout=0x654290, nspinfo=0x6600c0) at
> pg_dump.c:7930
> 7930    {
> (gdb)  p nspinfo.dobj
> $31 = {objType = DO_NAMESPACE, catId = {tableoid = 2615, oid = 17972},
> dumpId = 13, name = 0x660d80 "test", namespace = 0x0,
>   dump = 0 '\000', ext_member = 1 '\001', dependencies = 0x6e2b60, nDeps = 1, allocDeps = 16}
                     ^^^^^^^^^^^^^^

There's the problem: this schema is marked as a member object of an
extension, so it's not going to get dumped.  The expectation is that it'd
be recreated during CREATE EXTENSION of whatever extension it belongs to.

            regards, tom lane

InterestingŠ I checked the pg_depend for that schema, looks like it¹s
depending on an extension.

polling_etl=# SELECT classid, objid, refclassid, refobjid
FROM pg_depend
WHERE refclassid = 'pg_extension'::regclass
AND deptype = 'e'  and objid = 17972
ORDER BY 3,4;
 classid | objid | refclassid | refobjid
---------+-------+------------+----------
    2615 | 17972 |       3079 |    16730
              ^^^^^^


polling_etl=# select * from pg_extension where oid = 16730;
 extname | extowner | extnamespace | extrelocatable | extversion |
extconfig | extcondition
---------+----------+--------------+----------------+------------+---------
--+--------------
 hstore  |       10 |        16389 | t              | 1.2        |
  |


How can I remove this dependency? Or what should I do to make it dump the
schema creation correctly?



On 6/1/15, 11:01 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

>"Guo, Yun" <YGuo@cvent.com> writes:
>> I tried gdb on selectDumpableNamespace() and dumpNamespace().
>> So it looks like for those problematic schemas,the dump value is 1 in
>> selectDumpableNamespace() which is correct, but when it goes to
>> dumpNamespace() the dump value becomes 0, so it get skipped.
>
>Oh really ...
>
>> Breakpoint 1, dumpNamespace (fout=0x654290, nspinfo=0x6600c0) at
>> pg_dump.c:7930
>> 7930    {
>> (gdb)  p nspinfo.dobj
>> $31 = {objType = DO_NAMESPACE, catId = {tableoid = 2615, oid = 17972},
>> dumpId = 13, name = 0x660d80 "test", namespace = 0x0,
>>   dump = 0 '\000', ext_member = 1 '\001', dependencies = 0x6e2b60,
>>nDeps = 1, allocDeps = 16}
>                     ^^^^^^^^^^^^^^
>
>There's the problem: this schema is marked as a member object of an
>extension, so it's not going to get dumped.  The expectation is that it'd
>be recreated during CREATE EXTENSION of whatever extension it belongs to.
>
>            regards, tom lane

Yun Guo wrote:
> InterestingŠ I checked the pg_depend for that schema, looks like it¹s
> depending on an extension.
> 
> polling_etl=# SELECT classid, objid, refclassid, refobjid
> FROM pg_depend
> WHERE refclassid = 'pg_extension'::regclass
> AND deptype = 'e'  and objid = 17972
> ORDER BY 3,4;
>  classid | objid | refclassid | refobjid
> ---------+-------+------------+----------
>     2615 | 17972 |       3079 |    16730
>               ^^^^^^
> 
> 
> polling_etl=# select * from pg_extension where oid = 16730;
>  extname | extowner | extnamespace | extrelocatable | extversion |
> extconfig | extcondition
> ---------+----------+--------------+----------------+------------+---------
> --+--------------
>  hstore  |       10 |        16389 | t              | 1.2        |
>   |
> 
> 
> How can I remove this dependency? Or what should I do to make it dump the
> schema creation correctly?

It *is* being dumped correctly.

The schema belongs to an extension, so it is created by the CREATE EXTENSION
command.  If the schema itself were also dumped, that would create problems
during restore: The schema would be create both by CREATE EXTENSION and
CREATE SCHEMA, which would result in an error.

If you are sure that it is the right thing to do, you can remove the
dependency on the extension with
   ALTER EXTENSION hstore DROP SCHEMA test;

Yours,
Laurenz Albe

Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> Yun Guo wrote:
>> polling_etl=# SELECT classid, objid, refclassid, refobjid
>> FROM pg_depend
>> WHERE refclassid = 'pg_extension'::regclass
>> AND deptype = 'e'  and objid = 17972
>> ORDER BY 3,4;
>> classid | objid | refclassid | refobjid
>> ---------+-------+------------+----------
>> 2615 | 17972 |       3079 |    16730
>>
>> polling_etl=# select * from pg_extension where oid = 16730;
>> extname | extowner | extnamespace | extrelocatable | extversion |
>> extconfig | extcondition
>> ---------+----------+--------------+----------------+------------+---------
>> --+--------------
>> hstore  |       10 |        16389 | t              | 1.2        |
>> |
>>
>> How can I remove this dependency? Or what should I do to make it dump the
>> schema creation correctly?

> It *is* being dumped correctly.

Indeed.  What's more interesting to ask is how did the hstore extension
wind up owning a schema?  That should certainly not happen as shipped.
Were you playing around with ALTER EXTENSION ADD/DROP?

            regards, tom lane

But the dumped file cannot be restored. In the dump file it doesn’t seem
to create schemas with the extension:

-bash-4.1$ pg_dump -s polling_etl | grep EXTENSION
-- Name: hstore; Type: EXTENSION; Schema: -; Owner:
CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA polling_etl;
-- Name: EXTENSION hstore; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION hstore IS 'data type for storing sets of (key, value)
pairs';
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:



So that when it tries to restore any objects in the schema it would error
out because the schema isn’t created.

-bash-4.1$ pg_restore -e -hhq-pgpsbk-001 -Udba -dpostgres /tmp/polling.dump
Password:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 178; 1259 18600 TABLE
test mdev1
pg_restore: [archiver (db)] could not execute query: ERROR:  permission
denied to create "pg_catalog.test"
DETAIL:  System catalog modifications are currently disallowed.
    Command was: CREATE TABLE test (
    i integer
);

I have no idea how hstore becomes owner of those schemas. Maybe other team
members did that but I can’t think of reason why would that be necessary.
I tried "ALTER EXTENSION hstore DROP SCHEMA” as Laurenz suggested which
does fix the problem of dumping schema.

Thank you so much for your help guys!

Yun

On 6/2/15, 10:02 AM, "Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:

>Yun Guo wrote:
>> InterestingŠ I checked the pg_depend for that schema, looks like it¹s
>> depending on an extension.
>> 
>> polling_etl=# SELECT classid, objid, refclassid, refobjid
>> FROM pg_depend
>> WHERE refclassid = 'pg_extension'::regclass
>> AND deptype = 'e'  and objid = 17972
>> ORDER BY 3,4;
>>  classid | objid | refclassid | refobjid
>> ---------+-------+------------+----------
>>     2615 | 17972 |       3079 |    16730
>>               ^^^^^^
>> 
>> 
>> polling_etl=# select * from pg_extension where oid = 16730;
>>  extname | extowner | extnamespace | extrelocatable | extversion |
>> extconfig | extcondition
>> 
>>---------+----------+--------------+----------------+------------+-------
>>--
>> --+--------------
>>  hstore  |       10 |        16389 | t              | 1.2        |
>>   |
>> 
>> 
>> How can I remove this dependency? Or what should I do to make it dump
>>the
>> schema creation correctly?
>
>It *is* being dumped correctly.
>
>The schema belongs to an extension, so it is created by the CREATE
>EXTENSION
>command.  If the schema itself were also dumped, that would create
>problems
>during restore: The schema would be create both by CREATE EXTENSION and
>CREATE SCHEMA, which would result in an error.
>
>If you are sure that it is the right thing to do, you can remove the
>dependency on the extension with
>   ALTER EXTENSION hstore DROP SCHEMA test;
>
>Yours,
>Laurenz Albe