On Tue, May 31, 2016 at 9:49 AM, Thalis Kalfigkopoulos <tkalfigo@gmail.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.
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...
