Why is there database named “postgres”?

Every so often, on irc, someone asks if they can remove postgres database, and why is it even there.

Figured I can write one blogpost to answer it once and for all, and use it as ready answer whenever the question resurfaces.

When one installs PostgreSQL on a server, usually there are three databases ready made:

  • template0
  • template1
  • postgres

template1 is used as template for when someone uses create database x. You can modify it, and any change in it will get copied to any new database.

template0 is also template, but one that you can't (easily) modify. It's used when creating database from dumps, or to restore template1 if you'd mistakenly create too much stuff in there.

But what is the purpose of “postgres"?

Well, it's used as a default database to connect to by various tools, when you don't specify database to connect to, but connection is required.

For example, if you use createdb or createuser commands – what they do is connect to PostgreSQL, which mean – connect to some database, and issue appropriate CREATE sql query.

Let's see it:

=$ createuser x

and in Pg logs I see:

2021-02-09 10:28:22.468 CET [unknown]@[unknown] 186080 [local] LOG:  connection received: host=[local]
2021-02-09 10:28:22.469 CET depesz@postgres 186080 [local] LOG:  connection authorized: user=depesz database=postgres application_name=createuser
2021-02-09 10:28:22.491 CET depesz@postgres 186080 [local] LOG:  duration: 3.531 ms  statement: SELECT pg_catalog.set_config('search_path', '', false);
2021-02-09 10:28:22.499 CET depesz@postgres 186080 [local] LOG:  duration: 7.526 ms  statement: CREATE ROLE x NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
2021-02-09 10:28:22.499 CET depesz@postgres 186080 [local] LOG:  disconnection: session time: 0:00:00.032 user=depesz database=postgres host=[local]

Interestingly – when you connect to Pg using psql, it tries to connect using username that is the same as your system account, and database that is named the same way.

Which means, that if I'm logged on my system as depesz and do have depesz database account, and depesz database, when I simply run psql in shell, I will get logged to my db using my user:

=$ psql
psql (14devel)
TYPE "help" FOR help.
 
(depesz@[LOCAL]:5140) 10:35:36 [depesz] 
$ SELECT CURRENT_USER, current_database();
 CURRENT_USER │ current_database 
──────────────┼──────────────────
 depesz       │ depesz
(1 ROW)

but if I'd just want to list databases, from shell:

=$ psql -l
                                     List OF DATABASES
      Name      │     Owner      │ Encoding │   COLLATE   │    Ctype    │ Access privileges 
────────────────┼────────────────┼──────────┼─────────────┼─────────────┼───────────────────
 depesz         │ depesz         │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ 
 depesz_explain │ depesz_explain │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ 
 depesz_paste   │ depesz_paste   │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ 
 pgdba          │ pgdba          │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ 
 postgres       │ pgdba          │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ 
 template0      │ pgdba          │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ =c/pgdba         ↵
                │                │          │             │             │ pgdba=CTc/pgdba
 template1      │ pgdba          │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ =c/pgdba         ↵
                │                │          │             │             │ pgdba=CTc/pgdba
(7 ROWS)

logs show:

2021-02-09 10:36:11.552 CET [UNKNOWN]@[UNKNOWN] 196509 [LOCAL] LOG:  connection received: host=[LOCAL]
2021-02-09 10:36:11.554 CET depesz@postgres 196509 [LOCAL] LOG:  connection authorized: USER=depesz DATABASE=postgres application_name=psql
2021-02-09 10:36:11.561 CET depesz@postgres 196509 [LOCAL] LOG:  duration: 3.537 ms  statement: SELECT d.datname AS "Name",
               pg_catalog.pg_get_userbyid(d.datdba) AS "Owner",
               pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding",
               d.datcollate AS "Collate",
               d.datctype AS "Ctype",
               pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
        FROM pg_catalog.pg_database d
        ORDER BY 1;
2021-02-09 10:36:11.568 CET depesz@postgres 196509 [LOCAL] LOG:  disconnection: SESSION TIME: 0:00:00.016 USER=depesz DATABASE=postgres host=[LOCAL]

that is – I actually connected to database postgres.

Which leaves me with two things:

  • yes, you can delete database postgres (to reclaim some disk space I assume)
  • no, it doesn't really make sense

Sure, you can delete it, and then simply always connect to some other DB when you want to use tools. But – this DB, by default, is ~ 8MB. Empty database directory (just after initdb, with no data loaded) is 40MB. So sure, you can shrink it to 32MB, but then – you will most likely want to load some data to it, and this 8MB will vanish almost immediately.

Hope this will help 🙂

8 thoughts on “Why is there database named “postgres”?”

  3. Well, the major reason why postgres database was added is (for example) not being able to create a new database when there is a connection to template1 database, which was very common before postgres was not the default database:

    ERROR: 55006: source database “template1” is being accessed by other users
    DETAIL: There is 1 other session using the database.
    LOCATION: createdb, dbcommands.c:524

    OTOH, clients like psql connect to postgres database by default, because it matches the username, only if they are run with the postgres OS user.

    -HTH

  4. @Devrim:
    well, psql connects to postgres db by default, if you run it with -l. I was logged in as “depesz”, and there is “depesz” database, and user in db, but it connected to “postgres”.

  5. this default database might also become a pain in the ass.

    we have around 50 postgres clusters containing around 80 databases.

    the postgres connection string (endpoint) is “host:port:dbname”.

    having more than one postgres cluster per host reduce this endpoint distinction to “port:dbname”.

    allowing the usage of the “postgres” default database would reduce this endpoint distinction to “port”.

    it would be very hard for anyone to get sure that the correct endpoint is used.

    that’s why we locked down the “postgres” default database (allow_connections = false).

  6. @Markus:

    Sorry, but I don’t understand your point.

    1. If you disallow conns to postgres, then you can simply drop it.
    2. You shouldn’t have application data in postgres database, so connection should always be host:port:dbname
    3. Assuming you have app data in database “x”, why is database “postgres” causing any issues?

  7. This is an absolute noob question, as this is day one of working with PostGresSQL and zero coding experience.

    But my control + A (select ALL) is not working with the query editor.

    I can’t figure it out. However, when I right click and press “Select ALL” it does highlight everything.

    Any advice would be much appreciated.

  8. @Ajay:
    Sorry, not sure what you mean. PostgreSQL doesn’t have “query editor”. It might be something in some kind of 3rd party tool, but then you should ask whoever made this tool.

Comments are closed.