Postgresql – Is the official term “template database” or “maintenance database”

pgadminpostgresql

pgAdmin has a field for the "maintenance database". The PostgreSQL manual talks about template databases.

What is the correct term? Why are they not consistent? This is even more confusing since PostgreSQL has "recently" changed the name from "template1" or "template0" into "postgres", suggesting that they didn't like the term "template database", yet continue to use it in the manual… while pgAdmin uses "maintenance database".

And I only vaguely understand the purpose for this to exist. I just know that whenever I have attempted to code a script which does the initial installation of a database, there is nothing for it to "connect to", so that's when I need to use the "maintenance database" when connecting, only to immediately CREATE DATABASE…

Best Answer

You must be an old-timer. The postgres database has been around since version 8.1.

There are three notions:

  1. A template database is a database whose purpose is to be used as a template in CREATE DATABASE: the template is essentially copied.

    While in principle any database can be used as template, template databases typically have datistemplate set to TRUE.

    Initially PostgreSQL comes with two templates: template1 (which you can modify if you want) and template0 (which remains unmodified).

  2. A default database called postgres. The purpose of this database is for administrative connections, typically to execute the CREATE DATABASE SQL statement.

    Before 8.1, when this database was introduced, template1 did double duty for that purpose. The 8.1 release notes describe that well:

    In prior releases, template1 was used both as a default connection for utilities like createuser, and as a template for new databases. This caused CREATE DATABASE to sometimes fail, because a new database cannot be created if anyone else is in the template database. With this change, the default connection database is now postgres, meaning it is much less likely someone will be using template1 during CREATE DATABASE.

  3. The maintenance database is a term unknown in PostgreSQL, it exists exclusively in pgAdmin and is a frequent source of confusion.

    In that text field you enter the name of the database to which you want to connect. Just forget the “maintenance” part.

    The designers of pgAdmin probably intended people to enter postgres there, assuming that the tool is used by a DBA who has access to that database. Then you can click on other databases of the same cluster, and pgAdmin will open an additional database connection to those databases.

Related Question