Re: [SPAM?]: Re: CLONE DATABASE (with copy on write?)

On Sun, Nov 13, 2011 at 3:07 PM, Clark C. Evans <cce(at)clarkevans(dot)com> wrote:

> Could their be a way to put the database in "read only" mode,
> where it rejects all attempts to change database state with an
> appropriate application level error message?  We could then
> update our application to behave appropriately while the copy
> is being performed.   Something like this could be broadly
> useful in other contexts as well, for example, having a replica
> that you brought up for reporting purposes.
>
> Even so, the CREATE DATABASE... WITH TEMPLATE still has a set of
> additional issues with it.  It ties up the hard drive with activity
> and then extra space while it duplicates data.  Further, it causes
> the shared memory cache to be split between the original and the
> replica, this causes both databases to be much slower.  Finally,
> it creates a ton of WAL traffic (perhaps we could suspend this?)

It would be possible to suspend writes to a particular database and
then copy the database without writing WAL. It's probably possible to
wait for all write transactions to complete first.

Yes, it would use up disk space and shared_buffers to cache the new db.

Allowing writes to continue while we copy is more complex.

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