CODEWITHSUNDEEP
postgresql
Jerome WAGNER
Jerome WAGNER

Reputation: 22412

how to move a postgres schema via file operations?

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

Upvotes: 1

Views: 632

Answers (1)

leonbloy
leonbloy

Reputation: 75896

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 inside a database, which you can use to qualify object names (analogous to directories and files; and there is a 'public' schema whichs is used as default for unqualified names). Schemas, then, are related to organization of names, not isolation: as long as we are inside a database, objects (tables, views...) from different schemas are mutually visible; so that, for example, a view can mix tables of different schemas, or a FK can refer to other schema. On the contrary, objects in different databases 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.

Upvotes: 1

Related Questions