Re: CLONE DATABASE (with copy on write?)

On Sat, Nov 12, 2011 at 9:40 PM, Clark C. Evans <cce(at)clarkevans(dot)com> wrote:

> Our company has some headaches in our application development
> and deployment process.  The chief problem is, "creating stages",
> which to this audience is, cloning a database efficiently,
> making and testing a few changes, perhaps recording the
> differences between databases, and then dropping the database.
>
> I'm eternally grateful for someone who pointed out that we
> should be using "CREATE DATABASE ... WITH TEMPLATE".  However,
> this has two big disadvantages.  First, it only works if you
> can kick the users off the clone.  Secondly, it still takes
> time, uses disk space, etc.  We have some big databases.

An interesting proposal. Thanks for taking the time to raise this.

The existing situation is that you need to either

1) quiesce the database so it can be copied locally

2) take a hot backup to create a clone on another server

(1) currently involves disconnection. Would a command to quiesce
sessions without disconnection be useful? We could get sessions to
sleep until woken after the copy. With large databases we would still
need to copy while sessions sleep to ensure a consistent database
after the copy.

Is (2) a problem for you? In what way?

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