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

  1. pg_dump -d mydb -Fc -Z9 > mydb.bak
  2. createdb mydb_transfer [other options]
  3. pg_restore -d mydb_transfer < mydb.bak
  4. psql -d mydb_transfer -c 'alter schema XXX rename to YYY;'
  5. pg_dump -d mydb_transfer -Fc --schema=YYY > mydb-YYY.bak
  6. psql -d mydb -c 'create schema YYY;'
  7. 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.

  1. pg_dump -d mydb -Fp --schema=XXX > mydb.bak
  2. (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.

AttachmentSize
duplicate_schema.509 bytes

Syndicate

Syndicate content