Why is pg_restore trying to create tables in pg_catalog?
hi,
Why is pg_restore trying to put stuff into the pg_catalog schema of all
places?
It's ignoring the schema specified in the pg_dump itself (`myschema`)
and even my search_path (`public`).
$ psql stuff_development --command "show search_path"
search_path
-------------
public
(1 row)
$ pg_restore --list stuff.pg_dump
;
; Archive created at Fri Apr 4 00:55:50 2014
; dbname: stuff_development
; TOC Entries: 14
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.3.4
; Dumped by pg_dump version: 9.3.4
;
;
; Selected TOC Entries:
;
205; 1259 95675 TABLE myschema stuff_one myuser
[...]
3312; 0 95675 TABLE DATA myschema stuff_one myuser
[...]
$ pg_restore --verbose --no-owner --no-privileges --dbname
stuff_development stuff.pg_dump
pg_restore: connecting to database for restore
pg_restore: creating TABLE stuff_one
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 205; 1259 95675 TABLE
stuff_one myuser
pg_restore: [archiver (db)] could not execute query: ERROR: permission
denied to create "pg_catalog.stuff_one"
DETAIL: System catalog modifications are currently disallowed.
Command was: CREATE TABLE stuff_one (
the_geom public.geometry
);
Thank you!
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Seamus Abshere <seamus@abshere.net> writes:
Why is pg_restore trying to put stuff into the pg_catalog schema of all
places?
Hm ... does myschema actually exist in the target database? If it
doesn't, and the dump file doesn't include a command to create it,
this is what you'll get. That's because what pg_dump emits is
SET search_path = myschema, pg_catalog;
CREATE TABLE ...
and at the moment, if myschema doesn't exist, the creation target
devolves to pg_catalog. This is not terribly elegant but I'm not
sure if there's consensus to change it.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 4/4/14, 12:58 PM, Tom Lane wrote:
Seamus Abshere <seamus@abshere.net> writes:
Why is pg_restore trying to put stuff into the pg_catalog schema of all places?
Hm ... does myschema actually exist in the target database? [...] if myschema doesn't exist, the creation target devolves to pg_catalog. This is not terribly elegant but I'm not sure if there's consensus to change it.
Tom,
You're right, myschema didn't exist (I thought I had created it
separately, etc.)
Perhaps it would be good to warn the user (at least in --verbose) if
it's auto-devolving to pg_catalog?
Thanks again,
Seamus
PS. Otherwise, if you google the error message, you get a whole bunch of
stackoverflow posts recommending you make your user a superuser so you
can write to pg_catalog, which probably isn't what the person wanted in
the first place.
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Seamus Abshere <seamus@abshere.net> writes:
On 4/4/14, 12:58 PM, Tom Lane wrote:
Hm ... does myschema actually exist in the target database? [...] if myschema doesn't exist, the creation target devolves to pg_catalog. This is not terribly elegant but I'm not sure if there's consensus to change it.
You're right, myschema didn't exist (I thought I had created it
separately, etc.)
Perhaps it would be good to warn the user (at least in --verbose) if
it's auto-devolving to pg_catalog?
Well, the real point is we don't want it to auto-devolve to pg_catalog
in the first place. I've restarted the discussion about this on
pgsql-hackers, we'll see if it goes anywhere this time:
/messages/by-id/21235.1396632839@sss.pgh.pa.us
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general