pg_dump does not honor -n

I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4).

well, here's the command:


pg_dump -f file.sql -F plain --inserts -n schema database

Ok. My command finished up really nice, but, it's not dumping everything to a schema called schema (in the example above). It's loading everything into public.

EDIT:

To make the question clearer:

The dump works fine. Upon inspection, it's dumping my schema.table, for example, to public.table. I want that to restored to schema, not public.

Any tips on solving this?

Thanks

When dumping a schema with the mentioned command, as postgres user:

pg_dump -f file.sql -F plain --inserts -n schemaname database


it produces a file whose basic structure looks like:

-- various SET commands
CREATE SCHEMA schemaname;

ALTER SCHEMA schemaname OWNER TO ownername;

SET search_path = schemaname, pg_catalog;

CREATE TABLE tablename(...) ...

ALTER TABLE schemaname.tablename OWNER TO ownername;

COPY tablename (columns...) FROM stdin;
data...
\.

The fact that search_path is set to the newly created schema implies that the CREATE TABLE and COPY statements that follow will happen in that schema and not in the public schema.

Or SET search_path would have to fail, which shouldn't happen even if the creation of the schema just above in the script failed. If schemaname does not exist, the next schema tried would be pg_catalog and CREATE TABLE would refuse to write into it, and after that there's no next schema in the search path, and in particular not public.

It's hard to imagine what mishappening could cause the above script to end up creating and populating anything in the public schema.

StackExchange Database Administrators Q#100599, answer score: 5