I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema.

The dump type is "custom".

So effectively I want something like:

pg_dump -U postgres --schema "source_schema" --format "c" --create --file "source_schema.bak" my_db
pg_restore -U postgres --exit-on-error --dbname "my_db" "source_schema.bak" --destination-schema "destination_schema"

If that is possible it would be fine if I could copy (and rename) source schema before backup.


If the data is smallish, I would just do the pg_dump, restore it to a temporary just-for-this-purpose database server, rename the schema within that temporary server (ALTER SCHEMA...RENAME...), and dump it out of that temporary server to do the final restore.


the easiest thing is: dump the stuff. open the dump file in vi. fix, what has to be fixed and replay the stuff.

  • Interesting. That might be the easiest thing to do (thought I would not be able to use binary format for dump). – Nux Oct 29 '14 at 13:26
  • 1
    well, you can: pg_restore transforms a custom format dump into textual format. you can add a pipe and use set to adapt the search_path. it would lead to the desired outcome if i have understood your request correctly. pg_restore ... | sed ... | psql ... – Hans-Jürgen Schönig Oct 29 '14 at 13:31
  • On second thought that is not that easy... I would risk changing something in user data or even column names (if one of them has the same name as the schema). – Nux Oct 29 '14 at 13:46

I had to do the same thing, ended up dumping in text format and editing the dump file with a sed command.

Patterns to look for / replace :

CREATE SCHEMA [schema_name]

ALTER SCHEMA [schema_name]

SET search_path = [schema_name]

COMMENT ON SCHEMA [schema_name] IS '[comment]';

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.