Q:

postgresql "createdb" and "CREATE DATABASE" yield a non-empty database. what the fork?

First of all, I apologize if this question turns out to be painfully obvious, I'm not that postgres-savvy beyond the basics. I use postgresql as a database backend for quite a few django projects that I'm working on, and that's always worked just fine for me. Recently, I set up postgresql on a new machine, and at one point a co worker tried setting up a new project on that machine. Unfortunately, it's too late to go back into the bash history to figure out what he did, and he won't be available for a while to ask him about it. The issue i'm having now is...
I regularly reset postgres databases by simply using a dropdb/createdb command. I've noticed that whenever I run the dropdb command, the database does disappear, but when I run the createdb command next, the resulting database is not empty. It contains tables, and those tables do contain data (which appears to be dummy data from the other project). I realise that i'm a bit of a postgres noob, but is this in some way related to template features in postgres? I don't specify anything like that on the command line, and I'm seeing the exact same results if I drop/create from the psql console.
By the way, I can still wipe the db by dropping and recreating the "public" schema in the database. I'll be glad to add any info necessary to help figure this out, but to be honest I haven't a clue what to look for at this point. Any help would be much appreciated.

A:

Summarizing from the docs template0 is essentially a clean, virgin system database, whereas template1 serves as a blue print for any new database created with the createdb command or create database from a psql prompt (there is no effective difference).
It is probable that you have some tables lurking in template1, which is why they keep reappearing on createdb. You can solve this by dropping template1 and recreating it from template0.
createdb -T template0 template1 

The template1 database can be extremely useful. I use Postgis a lot, so I have all of the functions and tables related to that installed in template1, so any new database I create is immediately spatially enabled.
EDIT. As noted in docs, but worth emphasizing, to delete tempate1 you need to have pg_database.datistemplate = false set.

