Re: How ugly would this be? (ALTER DATABASE)

On Fri, Oct 24, 2014 at 2:06 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> One of the things we run into quite a bit is customers who are using
> multiple databases when they should be using multiple schemas. I am sure
> other consultants run into this as well. This gets even more difficult as
> uptime requirements have become all but 100%. So my question is, what would
> this take?
>
> ALTER DATABASE foo LOCATION DATABASE bar SCHEMA baz?
>
> Where if we execute that command, database foo would move to schema baz
> within database bar?
>
> I am fully aware of what it takes on the client side but structurally within
> postgres what would it take? Is it even reasonable?

What if the database contains more than one schema?

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company