4

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

  • What permissions does the user, that you create the dump with, have on the schema? – dezso May 4 '15 at 22:22
  • I suspect you've misunderstood what -n does. It should be restricting the dump to only objects in the schema schema, so objects in public and other schemas are not dumped. – Craig Ringer May 5 '15 at 5:02
  • 1
    @CraigRinger: It does just that. It restricted the dump to schema. What I maybe could not explain, is that I expect the schema to restored in it's original place. Everything is just going to public, instead. – George Silva May 5 '15 at 13:17
4

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.

  • In non--n contexts (e.g., -t - single-table), the SET search_path = schemaname, pg_catalog; is something I really cannot fathom. It basically guarantees that if the schema doesn't exist in the target DB, all the table creation logic will fail, so why not just test for the existence of the schema in the target DB, and fail (or create schema) if it doesn't exist? Sometimes folks want to replicate a table to a different schema - in my work I reckon it's 75% of the use-cases, and it's a pain having to FART-edit the schema-stuff (it's the only thing I use FART for instead of awk/grep). – GT. Jul 15 '16 at 5:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.