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

Hi all.

Ok, Igor nailed it. That was lame on my behalf. I apparently "contaminated"
my template1 db at some point (restored into it instead of into the target
"dafodb"). A simple \d confirmed this immediately.

Apologies for the false alarm.

@DavidJohnston, I don't know why, but yes, I am doing all operations
connected from template1.

BR,
Thalis K.

On Tue, May 31, 2016 at 4:02 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.​
>