Default database named postgres on Postgresql server
DatabasePostgresqlDatabase Problem Overview
Apparently there is a database "postgres" that is created by default on each postgresql server installation. Can anyone tell me or point me to documentation what it is used for?
Database Solutions
Solution 1 - Database
When a client application connects to a Postgres server, it must specify which database that it wants to connect to. If you don't know the name of a database (within the cluster serviced by the postmaster to which you connect), you can find a list of database names with the command:
psql -l
When you run that command, psql connects to the server and queries pg_database for a list of database names. However, since psql is a Postgres client application, it can't connect to the server without knowing the name of at least one database: Catch-22. So, psql is hard-coded to connect to a database named "postgres" when you run psql -l, but you can specify a template database in that case:
psql -l -d template1
Solution 2 - Database
It appears that it does not really have a well-defined purpose. According to the docs:
> Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the "template1" and "postgres" databases. > > [...] > > The postgres database is a default database meant for use by users, utilities and third party applications.
(Source: http://www.postgresql.org/docs/current/app-initdb.html )
Solution 3 - Database
There is also the database template0, your safety net when you screw up all others.
- postgres is your default database to connect with.
- template1 is your default for creating new databases, these are created just like template1
- template0 is usefull when template1 is corrupted (wrong settings etc.) and you don't want to spend a lot of time to fix this. Just drop template1 and create a new template1 using the database template0.
Solution 4 - Database
The comment above asked: "Is it safe to delete the postgres database if you're not using it?" - CMCDragonkai Oct 22 '16 at 10:37
From the PostgreSQL documentation
> After initialization, a database cluster will contain a database named postgres, which is meant as a default database for use by utilities, users and third party applications. The database server itself does not require the postgres database to exist, but many external utility programs assume it exists.
[Note: A database cluster is a collection of databases that is managed by a single instance of a running database server.]
Solution 5 - Database
If you are using multiple database connections when creating new databases, then all the connections cannot be done to template1 or template0.
Postgresql will throw an error if the source DB while creating new DB is accessed by other connections.
So for creating new DBs it is better to connect postgres.
