"Clark C. Evans" <cce@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 305.321.1144 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general