Re: schema-only -n option in pg_restore fails

All,

Crossing this over to -hackers because it's stopped being a bug and is
now a TODO item. See below.

For those not on pgsql-bugs, I've quoted the full bug report below my
proposal.

On 10/09/2014 12:36 PM, Josh Berkus wrote:
> Summary: pg_restore -n attempts to restore objects to pg_catalog schema
> Versions Tested: 9.3.5, 9.3.0, 9.2.4

Explored this some with Andrew offlist. Turns out this is going to be a
PITA to fix, so it should go on the big pile of TODOs for when we
overhaul search_path.

Here's what's happening under the hood, pg_restore generates this SQL text:

SET search_path = schem_a, pg_catalog;
CREATE TABLE tab_a (
test text
);

Since schem_a doesn't exist, it's skipped over and pg_restore attempts
to create the objects in pg_catalog. So this is Yet Another Issue
caused by the ten meter tall tar baby which is search_path.

So, my proposal for a resolution:

1) In current versions, patch the docs to explicitly say that -n does
not create the schema, and that if the user doesn't create the schema
pg_restore will fail.

2) Patch 9.5's pg_restore to do "CREATE SCHEMA IF NOT EXISTS" when -n is
used. This will be 100% backwards-compatible with current behavior.

Discuss?

Original bug report follows.

On 10/09/2014 12:36 PM, Josh Berkus wrote:> Summary: pg_restore -n
attempts to restore objects to pg_catalog schema
> Versions Tested: 9.3.5, 9.3.0, 9.2.4
> Severity: Failure
> Description:
>
> The -n option (or --schema) for pg_restore is supposed to allow you to
> restore a single schema from a custom-format pg_dump file. Instead, it
> attempts to restore that schema's objects to the pg_catalog schema
> instead. See the test case below.
>
> What's happening here is that the user is apparently expected to create
> the schema manually before doing a -n pg_restore. However, that's not
> what the documentation says, and additionally doesn't make any sense if
> we're not giving the user the ability to restore to an alternate schema
> name (and so far we aren't). If the schema does not already exist,
> pg_restore attempts to restore to the pg_catalog schema instead, which
> fails.
>
> In other words, pg_restore -n is "just broken". Clearly few people use
> it or we'd have a bug on it before now.
>
> What should happen is that pg_restore -n should create the schema if it
> doesn't already exist. If for some reason you think that pg_restore
> shouldn't create the schema (which would be user-hostile, but at least
> consistent), then this should fail cleanly with a "schema does not
> exist" error message instead of trying to restore to pg_catalog.
>
> Test Case:
>
> 1. createdb schtest;
> 2. createdb schrestore;
> 3. psql schtest
>
> 4. create schema schem_a;
> create table schem_a.tab_a ( test text );
> create schema schem_b;
> create table schem_b.tab_b ( test text );
> create schema schem_c;
> create table schem_c.tab_c ( test text );
>
> 5. pg_dump -Fc -f /tmp/schmtest.dump schtest
> 6. pg_restore -Fc -n schem_a -d schrestore /tmp/schmtest.dump
> 7.
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 171; 1259 1191591 TABLE
> tab_a josh
> pg_restore: [archiver (db)] could not execute query: ERROR: permission
> denied to create "pg_catalog.tab_a"
> DETAIL: System catalog modifications are currently disallowed.
> Command was: CREATE TABLE tab_a (
> test text
> );
>
> pg_restore: [archiver (db)] could not execute query: ERROR: schema
> "schem_a" does not exist
> Command was: ALTER TABLE schem_a.tab_a OWNER TO josh;
>
> pg_restore: [archiver (db)] Error from TOC entry 2194; 0 1191591 TABLE
> DATA tab_a josh
> pg_restore: [archiver (db)] could not execute query: ERROR: relation
> "tab_a" does not exist
> Command was: COPY tab_a (test) FROM stdin;
>

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com