How to restore postgreSQL schema dump to schema with different name

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

I'm moving other databases (from the public schema) into a new schemas in a single database (thus a single connection can access them all). My approach was to load into the public schema then rename that.

First, if you have anything in your public schema, rename to save. I found some extensions, "hstore" specifically, needed to be recreated in the new schema. Execute these SQL command in psql or otherwise.

alter schema public rename to public_original;
create schema public;
create extension hstore; -- etc.

Backup and Restore your database to the public schema. Adjust commands for your connection settings.

pg_dump --dbname=sourcedb --schema=public \
          --format=custom --file=sourcedb.dump
pg_restore --jobs=4 --dbname=targetdb sourcedb.dump

Your database should now be in the public schema. Now issue these SQL commands to rename the schema, and rename the original public schema.

alter schema public rename to sourcedb;
alter schema public_original rename to public;

If you are moving multiple databases like I did, create a new public schema and iterate for each. Afterwards, restore the original_public schema.

As an alternative, if you have no naming collisions between the existing public relations (tables, sequences, views, etc) and the imported ones, you could import everything together and then move the new tables into the new schema.

create schema newschema;
alter table table1 set schema newschema;
...

Just remember to test commands and do a test run on a test database. Scripting this is recommended ;-)

Answer 4

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 (search and replace the whole line not to edit real user data stored in db) :

CREATE SCHEMA [schema_name]

ALTER SCHEMA [schema_name]

SET search_path = [schema_name]

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

I used in on a small DB (less than 500MB dump), this might take ages to execute on a large database as stated in the comment.