snippetsqlMinor
How to restore postgreSQL schema dump to schema with different name
Viewed 0 times
postgresqldumpwithschemadifferentnamehowrestore
Problem
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:
If that is possible it would be fine if I could copy (and rename) source schema before backup.
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.
Solution
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.Context
StackExchange Database Administrators Q#77958, answer score: 7
Revisions (0)
No revisions yet.