Re: Drop/Re-Creating database extremely slow + doesn't lose data

On Tue, May 31, 2016 at 9:49 AM, Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com>
wrote:

> Intention: to drop a database and recreate it.
> Expectation: the newly created db should be empty
> What happens: dropping is fast, creation is slow, and when I reconnect,
> all the data objects are still there.
>
​[...]​

Even weirder, created a new DB with a completely unrelated name. Again
> "create database" took long time, but then connected to it and it has all
> the data from the "dafodb".
>
> Also tried: renaming dafodb to dafodb_OLD and again "create database
> dafodb". Both contain the same data.
>
> All this on Pg 9.5.2 on 64bit Ubuntu with 3.13.0-74-generic.
>
> Any idea what's going on or how to recover?
>

​Working as designed. Database creation in PostgreSQL operates by copying
a template database and then making minor modifications as specified in the
options to CREATE DATABASE.

https://www.postgresql.org/docs/9.6/static/sql-createdatabase.html

Note that the default template is "template1"

​Typically template0 is basically empty so the behavior you are expecting
to see can be had by explicitly specifying template0 in the command.

Oddly, the notes on the aforementioned page state: "The principal
limitation is that no other sessions can be connected to the template
database while it is being copied." yet in your example you appear to be
connected to template1 when you execute the CREATE DATABASE command...

​David J.​