On 24 Oct 2014 20:28, "Robert Haas" <robertmh...@gmail.com> wrote: > > You could perhaps try to create a command that would move a schema > between two databases in the same cluster. It's fraught with > practical difficulties because a single backend can't be connected to > both databases at the same time, so how exactly do you make the > required catalog changes all in a single transaction? But if you > imagine that you have an infinite pool of top-notch PostgreSQL talent > with unbounded time to work on this problem and no other, I bet > somebody could engineer a solution.
I think the bigger problem is the dependent objects. Things like data types which might exist in both databases but with different oids. If you simplify the problem to only handle tables and indexes and only if they only use system types and other objects then it seems doable but that creates a lot of pitfalls for users. I would do it in three steps more like pg_upgrade. 1) copy the schema to the new database and note the new oids and relfilenodes. 2) copy or move the data files over. 3) drop the old schema. Each of those can be done in separate transactions or even backends -- the intermediate states just have some empty tables in one of the schemas. It's not clear to me what state the databases should be in during step 2 though. A simple lock would not be sufficient. Perhaps there needs to be something like indisvalid for tables. That might be handy for pg_bulkload as well.
