Re: pg_dump not dumping some schemas

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