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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Thu, October 9, 2014 23:19, Josh Berkus wrote:

All,

[dump/restore -n bug]

Perhaps this (from five years ago) can be fixed too (esp. if only a doc-fix):

/messages/by-id/4833.156.83.1.81.1240955642.squirrel@webmail.xs4all.nl

It's not the same problem but also a failure in pick-and-choose restoring.

This stuff has been broken for a long time - I got used to it...

thanks,

Erik Rijkers

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Thu, Oct 9, 2014 at 6:19 PM, Josh Berkus <josh@agliodbs.com> wrote:

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.

I agree with this solution. Always when I restore some schema from a dump I
need to create schemas before and it's sucks.

I'm working on the 2th item [1]https://github.com/fabriziomello/postgres/tree/create_schema_during_pg_restore_schema_only together with other friend (Sebastian, in
cc) to introduce him into the PostgreSQL development process.

We'll register soon to the next commitfest.

Regards,

[1]: https://github.com/fabriziomello/postgres/tree/create_schema_during_pg_restore_schema_only
https://github.com/fabriziomello/postgres/tree/create_schema_during_pg_restore_schema_only

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL