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

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.

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

On 28 Jun 2012, at 5:11, 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.


For how to mark a database as a template database, check the bottom section of:
http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> 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.

I've read that postgres uses MVCC for transactions, and that it creates 
snapshots of the database for each transaction.  Couldn't the create
database command just use that snapshot?

eric

> I've read that postgres uses MVCC for transactions, and that it creates
> snapshots of the database for each transaction.  Couldn't the create
> database command just use that snapshot?

Database creation cannot be done inside a transaction (one of the few
DDL statements that can't), so no.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Alban Hertroys <haramrae@gmail.com> writes:
>> I've read that postgres uses MVCC for transactions, and that it creates
>> snapshots of the database for each transaction. �Couldn't the create
>> database command just use that snapshot?

> Database creation cannot be done inside a transaction (one of the few
> DDL statements that can't), so no.

It's a little more complicated than that.  The real answer is that
CREATE DATABASE works by doing a filesystem copy of the source database,
so if there are any concurrent changes going on, it can't get a
consistent snapshot of that database's state.

It's interesting to think about ways that that restriction might be
weakened, but I don't see any way to do it that wouldn't involve taking
some type of lock on each table in the source database --- and, at some
point, locking out the ability to create any new tables there too.
That would be messy, deadlock-prone, and probably still pretty
restrictive for transactions in the source database.  Another issue,
if the locks in question don't prohibit writes, is that as soon as
you've cloned a given table any WAL-logged actions issued against that
table would have to be duplicated for the clone in the new database;
something the transactions in the source database couldn't be expected
to know that they have to do, since after all the new database doesn't
exist yet from their perspective.

On the whole, even if it's possible at all, the work-to-payoff ratio
doesn't look very attractive.

            regards, tom lane