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