Re: CLONE DATABASE (with copy on write?) - Mailing list pgsql-general

"Clark C. Evans" <cce@clarkevans.com> writes:

> Hello all!
>
> 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.
>
> I was also thinking about using ZFS with PostgreSQL to do
> some sort of copy-on-write.  However, this would require me
> to spawn a whole *new* PostgreSQL instance.  In both of these
> cases, you lose your cache...
>
> So, I was wondering... could PostgreSQL grow the ability to
> "CLONE" a database by re-using existing file system blocks,
> sharing them across databases?  This would perhaps be fast,
> keep the shared memory cache relevant for both the old copy
> and the clone, and remove WAL overhead.  Then, if the block
> has to be edited, it'd be cloned in memory, and the clone
> would be flushed.
>
> I'm just imagining a world where "deploying" a new version
> of our software that makes very small catalog changes and
> tweaks a few rows would be... quick.  Quick to try, check,
> test, and even deploy on live servers.

Clark;

What you are describing can be done easily at the cluster level but
without any way to copy the live buffer cache state using any number
of storage array and/or file system solutions which implement
copy-on-write snapshots.

If being done on a shared storage array, you have the option to expose
the new snap to another host and run the clone instance there.  NOt
possible if on local storage with just FS support for snapping.

Taking the snap itself involves putting the source cluster in backup
mode same as you would  normally for making an image for later PITR.
The only difference is the snapping takes  just a few seconds.  Firing
up the clone involves recovering same also as for PITR.

Yeah; very cool if Pg itself could support such a thing at the level
of individual DB but I shudder to think what all might ve involved.

The last company I worked at had a hierarchy of snaps in some cases a
few layers deep.  We did whatever needed to the clones and then
dropped the snaps when done with them.  Physical disk use by the
clones is determined by what amount of churn the disk blocks
experience after the snapping, usually very little and thus, not much
physical storage was required.

"Thin Provisioning" is the buzzword that applies to this technology
which can be found on various EMCs, 3PARs and probably NetAp devices
and others.

HTH
> Best,
>
> Clark
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144