0

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.

Improve this question
1
  • 2
    Template1 is used as a template for any new db you create. I use Postgis a lot, so I put all the Postgis functions in template1, so any new database will immediately be spatially enabled. Have a look in template1 and see what schemas, tables, etc, you have. Commented Aug 7, 2014 at 16:42

1 Answer 1

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Perfect, that was the clue i was looking for. One little caveat I have to point out, it didn't allow me to drop template1 at first (you're not normally allowed to drop template databases). The way around that was to open the database "pg_database", and in the record for "template1" set the value for "datistemplate" to FALSE. After recreating it, i set the "datistemplate" for the new record to true, and that fixed the issue. Thanks a lot for the help.
Yes, thanks for pointing out, that was in the box at the bottom in the docs. Will update my answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.