Re: Detach/attach database

On 13 November 2011 16:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

I feared such a non-traversable terrain would prevent it being
possible. Oh well. Thanks for the explanation though.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company