CODEWITHSUNDEEP
postgresql-9.0pg-dumppostgresql-9.2
Lionel Tressens
Lionel Tressens

Reputation: 481

PostgreSQL / Dumping 9.0 and reimporting in 9.2

When dumping my database 9.0 with the aim to re import it in 9.2, I have issues with schemas.

For example, my table bar in schema foo is exported like this :

SET search_path = foo, pg_catalog;
...
CREATE TABLE bar (
   ...
);
ALTER TABLE foo.bar OWNER TO johndoe;

Importing this in 9.2 does not work as expected because the table bar is created in the schema 'public'. The ALTER TABLE statement fails with 'ERROR: relation "foo.bar" does not exist'.

I thought the 'SET search_path' directive issued by the *pg_dump* command in the dump file would be ok to set the default schema (and it works when importing in 9.0) but it doesn't work in 9.2.

Is there a way to make the pg_dump output different with explicit schema name ? Do I miss a configuration directive ?

Thanks in advance for any help !

Lionel

Upvotes: 0

Views: 217

Answers (1)

Lionel Tressens
Lionel Tressens

Reputation: 481

I replaced every element that needed an explicit schema name in the dump file and it did the job... No big deal.

Upvotes: 0

Related Questions