Re: Drop/Re-Creating database extremely slow + doesn't lose data - Mailing list pgsql-general
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.johnston@gmail.com> wrote:
Intention: to drop a database and recreate it.Expectation: the newly created db should be emptyWhat 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.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.
pgsql-general by date: