Re: remapping schema names with pg_restore?

Hi Bob,

As noted, there's no direct support in pg_dump, psql or pg_restore to
change the schema name during a dump/restore process. But it's fairly
straightforward to export using "plain" format then modify the .sql file.
This Bash script does the basics:

rename_schema () {

# Change search path so by default everything will go into the
specified schema
perl -pi -e "s/SET search_path = $2, pg_catalog/SET search_path =
$3, pg_catalog, $2;/" "$1"

# Change 'ALTER FUNCTION foo.' to 'ALTER FUNCTION bar.'
perl -pi -e 's/^([A-Z]+ [A-Z]+) '$2'\./$1 '$3'./' "$1"

# Change the final GRANT ALL ON SCHEMA foo TO PUBLIC
perl -pi -e 's/SCHEMA '$2'/SCHEMA '$3'/' "$1"
}

Usage:

pg_dump --format plain --schema=foo --file dump.sql MYDB
rename_schema dump.sql foo bar
psql -d MYDB -c 'CREATE SCHEMA bar;'
psql -d MYDB -f dumpsql

Regards,
Pavanteja,
9841380956

On Wed, Feb 26, 2020, 4:49 AM S.Bob <sbob(at)quadratum-braccas(dot)com> wrote:

> Hi All;
>
>
> Is it possible to re-map / rename schemas when doing a schema based
> restore from a custom formatted dump file. I have a dump I created like
> this:
>
>
> *pg_dump -Fc --schema=prod_test prod_db > prod_db.dmp*
>
> I want to restore it into another cluster, into a db named integ_db but I
> want the schema in the restored db to be named integ_test instead of
> prod_test
>
>
> Is there a way to do this at pg_restore time? without loading into a
> schema named prod_test and renaming the schema after the restore?
>
>
> Thanks in advance
>
>
>
>