From: Guo, Yun <YGuo@cvent.com>
To: Albe Laurenz <laurenz.albe@wien.gv.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: pg_dump not dumping some schemas
Date: Tue, 2 Jun 2015 15:03:15 +0000
Message-ID: <D1933252.37B45%yguo@cvent.com> (raw)
In-Reply-To: <A737B7A37273E048B164557ADEF4A58B3661B848@ntex2010i.host.magwien.gv.at>
References: <D18CC6E9.36A0E%yguo@cvent.com>
<A737B7A37273E048B164557ADEF4A58B36617951@ntex2010i.host.magwien.gv.at>
<D18DE04B.36C72%yguo@cvent.com>
<A737B7A37273E048B164557ADEF4A58B36618A43@ntex2010i.host.magwien.gv.at>
<D191D463.3774F%yguo@cvent.com>
<A737B7A37273E048B164557ADEF4A58B36618F30@ntex2010i.host.magwien.gv.at>
<2DB6C5478F692C4C983B3129CC1AEB14CA65E8C8@HQ08HQMX002.cvent.net>
<17196.1433168399@sss.pgh.pa.us>
<D1928D1D.378F4%yguo@cvent.com>
<11998.1433214088@sss.pgh.pa.us>
<D19329C5.37A5E%yguo@cvent.com>
<A737B7A37273E048B164557ADEF4A58B3661B848@ntex2010i.host.magwien.gv.at>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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: YGuo@cvent.com, laurenz.albe@wien.gv.at, tgl@sss.pgh.pa.us
Subject: Re: pg_dump not dumping some schemas
In-Reply-To: <D1933252.37B45%yguo@cvent.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