Thread: 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
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
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 devolvesto 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
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 devolvesto 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: http://www.postgresql.org/message-id/flat/21235.1396632839@sss.pgh.pa.us regards, tom lane