The Hidden Databases in PostgreSQL – What template0 and template1 Are Really For

---

When you install PostgreSQL, three default databases are automatically created:

• postgres

• template1

• template0

But here’s the strange part:
You can see template1 and postgres in most tools...
But template0 is nowhere to be found.

Even if you run \l in psql or check your database list in pgAdmin — template0 is invisible.

So... what’s going on?

---

🤔 Why Is template0 Hidden?

At first, I assumed it was just a system-only thing — hidden by design.
But there’s more to it.
Check this query:

SELECT datname, datistemplate, datallowconn FROM pg_database;

It’ll show you something like this:

Let’s break this down.

---

📌 What Each Database Is Really For

1. template1 – The Mother of All New Databases

Whenever you run:

CREATE DATABASE mydb;

PostgreSQL copies template1 to generate the new DB.

So if template1 includes any extensions or customizations —
every new DB you create will inherit them.

✅ Practical Tip:
Be careful when modifying template1.
Make a snapshot beforehand. Keep notes on what was changed and why.

---

2. template0 – The Clean Restore Kit

If template1 is messed up or includes unwanted changes,
you can create a fresh DB using the pristine template0:

CREATE DATABASE clean_db TEMPLATE template0;

However, by default template0 is not connectable (datallowconn = false).
PostgreSQL is saying:
"This is your last resort — don’t touch it."

✅ Practical Tip:
Never delete or modify template0.
It’s your reset button. Keep it intact.

---

3. postgres – The Default Login Door

This one’s not meant for storing data.
It's a default login target for clients like psql, pgAdmin, etc.

✅ Practical Tip:
Avoid using postgres as a real database.
If tables end up here, they might accidentally get backed up or cause conflicts.

---

🔍 Can You Connect to template0?

Yes — but don’t.

You could run:

UPDATE pg_database SET datallowconn = true WHERE datname = 'template0';

But again, there’s almost never a good reason to do this.
Leave template0 untouched. It exists to save you later.

---

✅ Summary Table

---

💡 Final Takeaway

If you're managing PostgreSQL in production,
understanding these 3 system databases is not optional — it's essential.

Especially if you're cloning environments, restoring backups, or customizing templates,
one small mistake in template1 or postgres can cost hours of debugging later.

Make them part of your operational checklist.
You’ll thank yourself later.