Re: Detach/attach database

Thom Brown <thom(at)linux(dot)com> writes:
> I don't know if this has been discussed before, but would it be
> feasible to introduce the ability to detach and attach databases? (if
> you're thinking "stop right there" skip to the end)

... skipping ...

> It's just a vague idea, and I'm kind of expecting responses to begin
> with "Well for a start, this couldn't possible begin to work
> because..." but that's par for the course. ;)

The main reason this doesn't work is XID management.

It's barely possible you could make it work if you first locked all
other sessions out of the DB and then froze every XID in the database,
but that's a sufficiently heavyweight operation to make it of dubious
value.

You'd also have to think of some way to ensure that page LSNs in the
database are lower than the current WAL endpoint in the receiver.

The other thing I'd be concerned about is inconsistency with the global
system catalogs in the receiving installation. Consider roles for
example: the receiver might not have the same set of roles, probably
wouldn't have the same OIDs for those roles, and definitely would be
missing the pg_shdepend entries that describe which objects in the
transported database are owned by which roles.

regards, tom lane