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
