Re: Andres Freund 2014-05-17 <20140517211930.GA10899@awork2.anarazel.de>
> On 2014-05-17 23:10:42 +0200, Christoph Berg wrote:
> > > Given that there haven't been complaints in the past ten years about how
> > > you can't rename an active database, I'm OK personally with locking this
> > > down forever. But I wonder if anyone wants to argue the contrary.
> >
> > Fwiw, we ran into exactly this question yesterday during a customer
> > training session. Their plan is to provide a database with updated
> > content every few hours, so their idea was to populate db_new, rename
> > db to db_old, rename db_new to db, and eventually drop db_old from
> > cron. This fails when sessions are connected to db. Surprisingly, it
> > actually works if you do the db renaming on a master server, but let
> > the (anyway readonly) client connect to a streaming slave. (On 9.2,
> > didn't test 9.4.)
>
> Ick. That's a bug imo. It should cause a recovery conflict disconnecting
> active sessions with force...
> I'd very much discourage your users from relying on this. Independent of
> my complaint about %d this isn't something that's supported and it won't
> change unless somebody puts non-neglegible resources into it.
They want to pg_terminate_backend() the sessions on db_old anyway, but
to get that point, you need to be able to rename the db. The
alternative would be to disallow connections to the db (for which
there is no real nice way), kill existing connections, do the
renaming, and then reenable connections. That's much more effort,
though.
Fwiw, this wasn't the first time I've heard of that idea, it also
doesn't sound too far-fetched for me. I guess people usually go "damn,
I can't rename active dbs, let's try something else" instead of
complaining on the mailing lists in that case.
> > We also looked into the confused-client problem, but
> > current_database() reports the new name correctly, and hence figured
> > there shouldn't be any problem with this approach, despite it
> > obviously being slightly out of spec because of the dependency on
> > running on a SR slave.
>
> At the very least log_line_prefix's %d will log the wrong thing.
I guess if you are doing database renames, you can live with that,
especially if you are doing the renames only for the purpose of
putting an updated db in place, and then throwing away the old db
quickly.
Christoph
--
cb@df7cb.de | http://www.df7cb.de/