Hello,
I have a schema schema1 in a postgres database A. I want to have a duplicate of this schema (model + data) in database B under the name schema2.
What are my options ?
I currently : * dump schema1 from database A * sed my way through schema renaming in the dump : schema1 becomes schema2 * restore schema2 in database B
but I am looking for a more efficient procedure. For instance, via direct file operations on postgres binary files.
Thanks for your help
Jerome Wagner
First, be aware (as others have commented) that Postgresql and Mysql have different ideas on what is a SCHEMA. In Postgresql (and in the SQL standard) a schema is just a namespace (whith a default 'public' schema) inside a database. But objects (eg tables) of different schemas in a same database have full visibilty among them ; so that, for example, a view can mix tables of different schemas, or a FK can refer to other schema. Objects in different databases, instead, are isolated (they only share users and groups), you can't join tables of different databases.
A dump-restore is the only sane way I can think of, for copying a schema from one database to another. Even so, from the above, it might not be safe/possible if the schema depends on other schemas of the database (it's like you are copying the classes of a Java package from one project to another). I would not dream on attempting a copy of the binary files.