39

I'm a beginner in PostgreSQL. I wonder why the \l command in psql shows databases template0 and template1.

I searched the web but unfortunately didn't find the right resources. But I did find that after removing both (template0 & template1) we can't create new databases any more.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ruth-The Glix
  • 523
  • 1
  • 4
  • 7
  • 1
    I don't know how you searched for it, this is literally the [first](https://www.postgresql.org/docs/current/static/manage-ag-templatedbs.html) and [second](https://blog.dbi-services.com/what-the-hell-are-these-template0-and-template1-databases-in-postgresql/) results in google for "template0". – 404 Jul 01 '18 at 10:44

1 Answers1

53

As the names indicate, those are template databases for creating new databases.

template1 is the one used by default. You can alter / add / remove objects there to affect every newly created DB. CREATE DATABASE basically makes a copy of it on the file level (very fast) to create a new instance.

template0 starts out being the same and should never be changed - to provide a virgin template with original settings.

Their role is described in detail in the chapter "Template Databases" in the manual.

But you can use any database of the same cluster as template with the TEMPLATE keyword - as long as there are no open connections to it. This is useful to populate new databases quickly. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228