Re: CLONE DATABASE (with copy on write?)

After this discussion and the spin-off discussion on the
hacker list, I wanted to summarize my understanding.

So, what I asked for is relatively inexpensive way to make
copies of an existing database for staging, upgrade tests,
and other activities. There are two approaches to this
sort of replication (besides dump/restore).

1. "COPY DATABASE ... WITH TEMPLATE" does a disk level
copy of an existing database in the same cluster.

This approach is an order of magnitude faster than
a dump/load cycle. Not only isn't there a dump/load
and intermediate result, but write ahead log segments
are efficiently handled (Tom, thank you this correction).

This approach has three downsides: (a) users must be
booted off the system, (b) you duplicate storage,
and (c) shared memory of the cluster is split and
cache state has to be re-learned on the replica.

Simon suggested that it may be possible to find a
solution for the exclusive access requirement; a way
to quiesce sessions without disconnection.

2. Use WALS to have a hot backup of the cluster; you
setup a cluster replica and then detach it.

This approach solves the quiesce problem via
replication, so you don't have to boot users off the
system. It also doesn't muck with the shared memory
cache state of your production source database since
you're making a copy to another PostgreSQL instance.

However, it has a few disadvantages: (a) you have to
copy the entire cluster, (b) you must create and
maintain another PostgreSQL instance.

In a hackers thread, Thom proposed "detach/attach"
feature so that you could move a database from one
cluster to another. This would be particularly useful,
but it looks quite infeasible since you'd have to touch
every disk block to rewrite the transaction IDs. This
feature was requested by Yang Zhang on April 6th as well.

Best,

Clark