Thread: How to make a copy of schema
I have a five company Postgres 8.1 database. Each company data is stored in a different schema named Company1, Company2, ..., Company5 There is also public schema which contains common data. Now customer wants to add sixth company to database. In need to add some routine to my application which can create schema copy ? How to make a copy of schema Company5 so that schema called Company6 is created ? Is there any sample code available ? Andrus
am Sun, dem 15.10.2006, um 19:10:53 +0300 mailte Andrus folgendes: > I have a five company Postgres 8.1 database. > > Each company data is stored in a different schema named Company1, Company2, > ..., Company5 > There is also public schema which contains common data. > > Now customer wants to add sixth company to database. > > In need to add some routine to my application which can create schema copy ? You can make a pg_dump with option -n Company5, replace in the dump Company5 with Company6 and restore this. Or, make the dump, rename schema Company5 to Company6 and restore then the dump. Be careful with things like sequences! Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>> In need to add some routine to my application which can create schema >> copy ? > > You can make a pg_dump with option -n Company5, replace in the dump > Company5 with Company6 and restore this. I dont have suffix like _schema in my company schema names. So "Company5" string may be used in places other than schema names so this does not work. > Or, make the dump, rename schema Company5 to Company6 and restore then > the dump. > Be careful with things like sequences! Will ALTER SCHEMA name RENAME TO newname rename schema names in used in foreign key constaints and triggers and all other places also ? Documentation does not describe this. Andrus.