Re: proposal: CREATE DATABASE vs. (partial) CHECKPOINT

On 2014-10-27 18:57:27 -0400, Andrew Dunstan wrote:
> 
> On 10/27/2014 05:58 PM, Tomas Vondra wrote:
> >On 27.10.2014 17:24, Heikki Linnakangas wrote:
> >I'm also thinking that for wal_level=archive and large databases, this
> >won't really eliminate the checkpoint as it will likely generate enough
> >WAL to hit checkpoint_segments and trigger a checkpoint anyway. No?
> >
> >That being said, our CREATE DATABASE docs currently say this
> >
> >     Although it is possible to copy a database other than template1 by
> >     specifying its name as the template, this is not (yet) intended as
> >     a general-purpose "COPY DATABASE" facility. The principal
> >     limitation is that no other sessions can be connected to the
> >     template database while it is being copied. CREATE DATABASE will
> >     fail if any other connection exists when it starts; otherwise, new
> >     connections to the template database are locked out until CREATE
> >     DATABASE completes. See Section 21.3 for more information.
> >
> >I think that this limitation pretty much means no one should use CREATE
> >DATABASE for cloning live databases in production environment (because
> >of the locking).
> >
> >It also seems to me the "general-purpose COPY DATABASE" described in the
> >docs is what we're describing in this thread.
> >
> 
> 
> Notwithstanding what the docs say, I have seen CREATE DATABASE used plenty
> of times, and quite effectively, to clone databases. I don't think making it
> do twice the IO in the general case is going to go down well.

I think they're actually more likely to be happy that we wouldn't need
do a immediate checkpoint anymore. The performance penalty from that
likely to be much more severe than the actual IO.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services