HOWTO: Duplicating schema in Postgresql
Unfortunately you can't set a target schema during a restore, you can just filter a schema
Renaming a schema does seem to be a pretty fast operation
Backup, rename elsewhere, restore
This could be the paranoid approach, at the cost of running a 2nd DB and maybe transfer a lot of data you may reduce the operations inside a transaction on the production DB and even test if you can safely load the new schema
- pg_dump -d mydb -Fc -Z9 > mydb.bak
- createdb mydb_transfer [other options]
- pg_restore -d mydb_transfer < mydb.bak
- psql -d mydb_transfer -c 'alter schema XXX rename to YYY;'
- pg_dump -d mydb_transfer -Fc --schema=YYY > mydb-YYY.bak
- psql -d mydb -c 'create schema YYY;'
- pg_restore -1 -dmydb < mydb-YYY.bak ; # single transaction
In the first step you could just backup the schema you want to rename... but I think having a full backup before such kind of change could come handy.
Backup, rename the original schema, reload
This is not for the faint-of-heart, but it saves some operations and time.
- pg_dump -d mydb -Fp --schema=XXX > mydb.bak
- (echo 'begin;'; echo 'alter schema XXX rename to YYY'; echo 'create schema XXX' authorization AAA; pg_restore --schema=XXX < mydb.bak; echo 'commit;') > psql -d mydb
Variations and caveat
According to your needs and mood you can make some variations on the above methods eg. backing up just the schema vs. the whole DB, using/not using compression... One thing you may consider is to set the owner of the new/old schema.
Script
here you can find a simple script that will automate everything. Use it at your own risk.
| Attachment | Size |
|---|---|
| duplicate_schema. | 509 bytes |
- Add new comment
- 47 reads
