Re: create database from template requires the source database to be unused

On 06/28/2012 11:11 AM, Tim Uckun wrote:
> I am sure this is intended behavior but it seems odd (and inconvenient) to me.
>
> create database tim_test_copy template tim_test
>
> ERROR:  source database "tim_test" is being accessed by other users
> DETAIL:  There are 1 other session(s) using the database.
>
> I would presume only reads are required from tim_test but apparently I
> have to resort to pg_dump and pg_restore to clone an active database.
Yes, it's an intentional limitation. As a workaround you can:

   ALTER DATABASE thedb CONNECTION LIMIT 1;

then:

SELECT pg_cancel_backend(procpid) FROM pg_stat_activity WHERE datname =
'thedb' AND procpid <> pg_backend_pid();

to terminate other active connections. Remember to put the connection
limit back after you copy the DB.



It'd be really interesting to relax that limitation to "... other
non-read-only transaction ... " and have a database that's being cloned
block or reject
DML, UPDATE, etc. There are some issues with that though:

(a) Transactions are read/write by default. Most apps don't bother to
SET TRANSACTION READ ONLY or BEGIN READ ONLY TRANSACTION . Most
non-read-only transactions will make no changes, but the database can't
know that until they complete.

(b) AFAIK even truly read-only transactions can set hint bits and
certain other system level database metadata.

(c) Because of (a) it'd be necessary to block your CREATE DATABASE ...
TEMPLATE ... until all transactions finished and sessions were idle, or
to abort all transactions and roll them back.

(d) The DB would need a flag that caused every data-modifying operation
to fail or block once the clone began, including vacuuming and other
things that happen outside a transactional context, including any
non-user-visible stuff in (b).

Most importantly, nobody's cared enough to do all that work, intensively
test it, build unit tests for it, etc.

The mechanism used with pg_start_backup(...) and pg_basebackup to allow
you to copy an active /cluster/ won't AFAIK work for a single database.
The cluster shares a single write-ahead log, and that's where all the
crash-safety is handled. Copying with pg_start_backup() basically gives
you a "crashed" cluster that's ready for quick recovery to normal
operation when started up. The shared WAL means you can't do this
_within_ a cluster; there's no concept of crash recovery of a single
database in the cluster using WAL, everything is cluster-wide.



I'd be really, /really/ excited to see Pg supporting per-database WAL at
some point. Putting aside the complexities posed by the global catalogs,
it'd allow streaming replication and point-in-time recovery (PITR) at
database granularity. It'd also allow WAL for a small-and-important DB
to be kept somewhere isolated from and faster than WAL for a
big-and-unimportant DB you don't want to affect the first one's
performance. Being able to tablespace WAL would _rock_; being able to
pg_start_backup() and copy just one DB even more so.

Don't expect that anytime ever though. Pg is built around the shared
WAL, and it'd take a truly huge amount of effort to allow per-database
WAL logging. Then there's all the shared catalog objects to worry about
- including users/groups/roles, the database list, etc.

You quickly get to the point where you get one database per logical
cluster, sharing just the same ip/port and same shared memory block.
Maybe that'd be a good thing; I don't know. It's certainly not going to
happen anytime soon, as I've seen nobody interested in pursuing
per-database WAL and it'd be a monsterously huge engineering effort anyway.

For now, you'll have to live with disconnecting sessions from your DB
before cloning it as a template.

--
Craig Ringer