Re: pg_dump not dumping some schemas

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(dot)albe(at)wien(dot)gv(dot)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