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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
