patternsqlMajor
What is the special database "postgres" for?
Viewed 0 times
thewhatpostgresdatabasespecialfor
Problem
I have a PostgreSQL server with several databases running on it. One of the databases is called
I have a feeling that it is some kind of a special database that is required for a normal operation of the server, but what is exactly its purpose? What kind of data is (or could be) stored inside? Does it hold the metadata about other databases (e.g. the
I googled for half an hour, but always got completely unrelated stuff, because the word
postgres, and it was there right from the beginning. I could not see any tables inside and I've never used it explicitly, but I noticed, that any user is allowed to create tables within the database (this is not what I want - I do not want any users except the administrator to create any tables anywhere).I have a feeling that it is some kind of a special database that is required for a normal operation of the server, but what is exactly its purpose? What kind of data is (or could be) stored inside? Does it hold the metadata about other databases (e.g. the
information_schema?). Can something nasty happen, if I revoke connect privilege to this database from public? Or if I even drop it?I googled for half an hour, but always got completely unrelated stuff, because the word
postgres is also used for the user postgres and the DBMS itself.Solution
There is nothing special about it. As described in PostgreSQL documentation, Creating a Database, you need to be connected to a database, in order to create another one:
Since you need to be connected to the database server in order to execute the
and in Template Databases:
Note:
The
Since you need to be connected to the database server in order to execute the
CREATE DATABASE command, the question remains how the first database at any given site can be created. The first database is always created by the initdb command when the data storage area is initialized. (See Section 17.2.) This database is called postgres. So to create the first "ordinary" database you can connect to postgres.and in Template Databases:
Note:
template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. (To delete template1, it must have pg_database.datistemplate = false.)The
postgres database is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of template1 and can be dropped and recreated if necessary.Context
StackExchange Database Administrators Q#144285, answer score: 31
Revisions (0)
No revisions yet.