On 10/27/2014 07:01 PM, Andres Freund wrote:
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.
At the very least that needs to be benchmarked.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
