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:
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)
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 🙂