Detach/attach database

Hi,

I don't know if this has been discussed before, but would it be
feasible to introduce the ability to detach and attach databases? (if
you're thinking "stop right there" skip to the end) What I had in
mind would be to do something like the following:

SELECT pg_detach_database('my_database')

pg_detach_database
--------------------
base/16384
(1 row)

Then this database would no longer be accessible. The system tables
would be updated to reflect the removal of this database, and some
kind of manifest file would be produced in that database directory.
The database represented by that returned directory could then be
moved elsewhere (or deleted if the user so wished, but isn't a real
benefit).

But then if another cluster were running with the same version and
architecture of the cluster we just detached our database from, we
could copy that directory to the base directory of that other cluster
(assuming the OID of the database didn't already conflict with an
existing one), then run:

SELECT pg_attach_database('16384', optional_tablespace_name_here);

Then the usual version/architecture/platform checks would happen, and
a reading of the manifest to populate system tables, checking for
conflicts, then the database brought online.

And if I haven't already asked too much, something like this could be run:

SELECT pg_start_clone('my_database');

pg_start_clone
----------------
base/16384
(1 row)

You may now be able to infer where this notion came from, when someone
asked if you can clone databases without kicking users off. However,
this isn't a schema-only copy, but naturally contains data as well.

So that function above would be like pg_start_backup() except that we
wouldn't prevent writes to other stable databases, just the candidate
one. I personally don't see how this could work with the way we
currently replay WAL files.

But the idea is that it would create the manifest file like
attach_database() except it wouldn't detach the database, and users
could continue to use the database.

Then the user could copy away the database directory elsewhere, then call:

SELECT pg_stop_clone('my_database');

And in theory the user could rename the copy of the directory and move
it back and reattach it as an identical copy (or identical at the time
of copying it).

The most obvious problems I see are related to permissions. All the
object level permissions would have to exist in the destination
database (which is fine if you're copying it to the same cluster), but
even if they do exist, I suspect the OIDs of those users would need to
be the same. Then there's extensions and collations. If either of
those are unavailable on the destination cluster, it couldn't work
either. But then some kind of error message communicating the missing
component could be returned when attempting to attach it.

Also I don't know how you could pause WAL replay for an individual database.

But use cases for this would be:
- Backing up select databases for quick re-attachment without lengthy restores
- User running out of disk space and wants to move large databases to
another server quickly
- A large static database is wanted on another server
- A copy of a large database is wanted on the same cluster

It's just a vague idea, and I'm kind of expecting responses to begin
with "Well for a start, this couldn't possible begin to work
because..." but that's par for the course. ;)

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company