Databases in PostgreSQL
Table of Contents
1. Understanding PostgreSQL Templates
In Oracle, there is typically only one database. When using the multitenant architecture, Oracle has two default containers: the root container (CDB$ROOT) and the seed pluggable database (PDB$SEED).
PostgreSQL comes with two built-in templates databases (template0 and template01) and additionally a database called “postgres”, that means three databases by default.
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
postgres=#
Template0 is the original master copy. It’s read-only and never changes. PostgreSQL protects it to keep it completely clean and empty.
Template1 is the default template that PostgreSQL uses when you create a new database. You can customize Template1 by adding extensions, settings, and other objects. When you create new databases from Template1, they automatically get all these customizations.
Why need two templetes? In a new PostgreSQL installation, template0 and template1 are identical. So why are there two template databases? The reason is that template0 serves as a clean backup copy. If template1 is modified too much or accidentally changed by adding unwanted objects, extensions, or settings, you can use template0 to create a fresh database and recover easily.
Key Points:
- When you create a database without specifying which template to use, PostgreSQL automatically uses Template1.
- Template1 can be modified with extensions and settings. Any new database created from Template1 will inherit these changes.
- Template0 never changes. It always stays clean and empty.
- If you want a brand new, completely empty database with no customizations, use Template0 instead of Template1.
When you run CREATE DATABASE mydb without specifying a template, PostgreSQL uses template1. To get a completely clean database without any customizations, explicitly use template0.
2. View Existing Templates
Before making any changes to your templates, it’s good practice to verify their current state. The following queries show you all templates in your PostgreSQL instance:
-- This query shows you all template databases with their names, template status, and access control lists (ACL).
[postgres@pgdb02 ~]$ psql
psql (17.10)
Type "help" for help.
postgres=# SELECT datname, datistemplate, datacl FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datacl
-----------+---------------+-------------------------------------
template0 | t | {=c/postgres,postgres=CTc/postgres}
template1 | t | {=c/postgres,postgres=CTc/postgres}
(2 rows)
postgres=#
postgres=# \l+ temp*
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+------------------------------------
template0 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +| 7545 kB | pg_default | unmodifiable empty database
| | | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +| 7569 kB | pg_default | default template for new databases
| | | | | | | | postgres=CTc/postgres | | |
(2 rows)
postgres=#
3. Create Databases with Template0 (Clean Slate)
When you need a completely fresh database with no existing objects or extensions, create it from template0.
-- Create a fresh database from template0
postgres=# CREATE DATABASE testdb WITH TEMPLATE template0;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | |
(4 rows)
postgres=#
postgres=# SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database WHERE datname IN ('template0','testdb') ORDER BY pg_database_size(datname) DESC;
datname | size
-----------+---------
template0 | 7702 kB
testdb | 7702 kB
(2 rows)
postgres=#
Note: Template0 is read-only by design. You cannot modify it directly. This ensures you always have a pristine copy to work from.
4. Create Databases with Template1 (Default)
Template1 is the default template used when creating new databases. Most development and testing databases are created from template1 because it can be customized to include common extensions and configurations.
-- Create a database from template1 (most common)
postgres=# CREATE DATABASE testdb02 WITH TEMPLATE template1;
CREATE DATABASE
postgres=# \l+
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | | 7774 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +| 7545 kB | pg_default | unmodifiable empty database
| | | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +| 7569 kB | pg_default | default template for new databases
| | | | | | | | postgres=CTc/postgres | | |
testdb | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | | 7545 kB | pg_default |
testdb02 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | | 7569 kB | pg_default |
(5 rows)
postgres=#
-- Create another database from template1
postgres=# CREATE DATABASE testdb03 TEMPLATE template1 ENCODING 'UTF8' OWNER postgres CONNECTION LIMIT 200;
CREATE DATABASE
postgres=# SELECT datname, datconnlimit FROM pg_database WHERE datname = 'testdb03';
datname | datconnlimit
----------+--------------
testdb03 | 200
(1 row)
postgres=#
5. Create Simple Databases
For quick testing or development, you can create databases with minimal syntax. PostgreSQL will automatically use template1 as the default:
-- Most basic creation (uses template1 by default)
postgres=# CREATE DATABASE testdb04;
CREATE DATABASE
postgres=#
-- With specific owner
postgres=# CREATE DATABASE testdb05 OWNER postgres;
CREATE DATABASE
postgres=#
6. Customize Template1
You can add extensions and objects to template1 so that all future databases inherit them automatically. Be cautious in production, as changes to template1 affect all subsequently created databases:
-- Add useful extensions to template1:
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=#
template1=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
template1=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
template1=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
template1=#
template1=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_statements | 1.11 | public | track planning and execution statistics of all SQL statements executed
pgcrypto | 1.3 | public | cryptographic functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
template1=#
-- Create database with Templete1 and verify extensions
template1=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=#
postgres=# CREATE DATABASE testdb06 TEMPLATE template1;
CREATE DATABASE
postgres=#
postgres=# \c testdb06
You are now connected to database "testdb06" as user "postgres".
testdb06=#
testdb06=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_statements | 1.11 | public | track planning and execution statistics of all SQL statements executed
pgcrypto | 1.3 | public | cryptographic functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
testdb06=#
Warning: Modifying template1 is a significant operation. Ensure you have a backup of your cluster before making changes, and test thoroughly in a non-production environment first.
7. Drop Default Database (Can we drop the “postgres” database?)
In some scenarios, you may want to remove the default postgres database. First, disconnect all connections to it:
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# drop database postgres;
ERROR: cannot drop the currently open database
postgres=#
You can not drop a database which users are currently connected to (in this case it is my own connection). So lets try to connect to template1 and then drop the “postgres” database
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=#
template1=# drop database postgres;
DROP DATABASE
template1=#
-- Recreate database "postgres" if needed
[postgres@pgdb02 ~]$ psql
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "postgres" does not exist
[postgres@pgdb02 ~]$ psql template1
psql (17.10)
Type "help" for help.
template1=# CREATE DATABASE postgres;
CREATE DATABASE
template1=#
template1=# \l postgres
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-------------------
postgres | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | |
(1 row)
template1=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
8. Create Custom Templates
Beyond template0 and template1, you can create your own custom templates for specific purposes. Custom templates are useful when different applications need different baseline configurations:
-- Create a custom template from template1
postgres=# CREATE DATABASE template2 TEMPLATE template1;
CREATE DATABASE
postgres=# SELECT datname, datistemplate, datacl FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datacl
-----------+---------------+-------------------------------------
template0 | t | {=c/postgres,postgres=CTc/postgres}
template1 | t | {=c/postgres,postgres=CTc/postgres}
template2 | f |
(3 rows)
postgres=#
-- Mark it as a template
update pg_database set datistemplate = true where datname = 'template2'; -- OR --- ALTER DATABASE template2 IS_TEMPLATE true;
postgres=# ALTER DATABASE template2 IS_TEMPLATE true;
ALTER DATABASE
postgres=#
postgres=# SELECT datname, datistemplate, datacl FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datacl
-----------+---------------+-------------------------------------
template0 | t | {=c/postgres,postgres=CTc/postgres}
template1 | t | {=c/postgres,postgres=CTc/postgres}
template2 | t |
(3 rows)
postgres=#
-- Add extensions
template2=# \c
You are now connected to database "template2" as user "postgres".
template2=#
template2=# CREATE EXTENSION "pageinspect";
CREATE EXTENSION
template2=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pageinspect | 1.12 | public | inspect the contents of database pages at a low level
pg_stat_statements | 1.11 | public | track planning and execution statistics of all SQL statements executed
pgcrypto | 1.3 | public | cryptographic functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(4 rows)
template2=#
-- Now create databases from your custom template:
template2=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# CREATE DATABASE testdb07 TEMPLATE template2 OWNER postgres;
CREATE DATABASE
postgres=#
postgres=# \c testdb07
You are now connected to database "testdb07" as user "postgres".
testdb07=#
testdb07=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pageinspect | 1.12 | public | inspect the contents of database pages at a low level
pg_stat_statements | 1.11 | public | track planning and execution statistics of all SQL statements executed
pgcrypto | 1.3 | public | cryptographic functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(4 rows)
testdb07=#
--- If you want to drop template2 db
template1=# drop database template2;
ERROR: cannot drop a template database
template1=#
template1=# SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datallowconn
-----------+---------------+--------------
template0 | t | f
template1 | t | t
template2 | t | t
(3 rows)
template1=#
template1=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# update pg_database set datistemplate = false where datname = 'template2';
UPDATE 1
postgres=#
postgres=# SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datallowconn
-----------+---------------+--------------
template0 | t | f
template1 | t | t
template2 | f | t
(3 rows)
postgres=# drop database template2;
DROP DATABASE
postgres=#
postgres=# SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datallowconn
-----------+---------------+--------------
template0 | t | f
template1 | t | t
(2 rows)
postgres=#
9. Database Properties and Management
View comprehensive information about all your databases, including their size and template status:
-- View database properties
postgres=# SELECT datname, datistemplate, datallowconn, datcollate, datctype, datacl FROM pg_database WHERE datname NOT LIKE 'pg_%' ORDER BY datname;
datname | datistemplate | datallowconn | datcollate | datctype | datacl
-----------+---------------+--------------+-------------+-------------+-------------------------------------
postgres | f | t | en_SG.UTF-8 | en_SG.UTF-8 |
template0 | t | f | en_SG.UTF-8 | en_SG.UTF-8 | {=c/postgres,postgres=CTc/postgres}
template1 | t | t | en_SG.UTF-8 | en_SG.UTF-8 | {=c/postgres,postgres=CTc/postgres}
template2 | t | t | en_SG.UTF-8 | en_SG.UTF-8 |
testdb | f | t | en_SG.UTF-8 | en_SG.UTF-8 |
testdb02 | f | t | en_SG.UTF-8 | en_SG.UTF-8 |
testdb03 | f | t | en_SG.UTF-8 | en_SG.UTF-8 |
testdb04 | f | t | en_SG.UTF-8 | en_SG.UTF-8 |
testdb05 | f | t | en_SG.UTF-8 | en_SG.UTF-8 |
testdb06 | f | t | en_SG.UTF-8 | en_SG.UTF-8 |
testdb07 | f | t | en_SG.UTF-8 | en_SG.UTF-8 |
(11 rows)
postgres=#
-- Get database size
postgres=# SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database WHERE datname NOT LIKE 'pg_%' ORDER BY pg_database_size(datname) DESC;
datname | size
-----------+---------
testdb07 | 7854 kB
template2 | 7854 kB
template1 | 7822 kB
testdb06 | 7822 kB
postgres | 7822 kB
testdb05 | 7726 kB
testdb02 | 7726 kB
testdb03 | 7726 kB
testdb04 | 7726 kB
template0 | 7702 kB
testdb | 7702 kB
(11 rows)
postgres=#
10. Complete Workflow Example
-- Step 1: Create from template0 (clean database)
postgres=# CREATE DATABASE orcl01 TEMPLATE template0 OWNER postgres;
CREATE DATABASE
postgres=#
-- Step 2: Create from template1 (with default extensions)
postgres=# CREATE DATABASE orcl02 TEMPLATE template1 OWNER postgres;
CREATE DATABASE
postgres=#
-- Step 3: Create custom template for staging
postgres=# CREATE DATABASE custom_template TEMPLATE template1 OWNER postgres;
CREATE DATABASE
postgres=# ALTER DATABASE custom_template IS_TEMPLATE true; -- OR -- update pg_database set datistemplate = true where datname = 'custom_template';
ALTER DATABASE
postgres=#
-- Step 4: Create from custom template
postgres=# CREATE DATABASE orcl03 TEMPLATE custom_template OWNER postgres;
CREATE DATABASE
postgres=#
-- Step 5: View all created databases
postgres=# SELECT datname, datistemplate, datcollate FROM pg_database WHERE datname LIKE 'orcl%' OR datname LIKE '%_template' ORDER BY datname;
datname | datistemplate | datcollate
-----------------+---------------+-------------
custom_template | t | en_SG.UTF-8
orcl01 | f | en_SG.UTF-8
orcl02 | f | en_SG.UTF-8
orcl03 | f | en_SG.UTF-8
(4 rows)
postgres=#
11. Drop Template1 and Create from Template0
Let’s go back to the datistemplate and datallowconn settings. template0 is the only database where datallowconn is set to false, which means users cannot connect to it. This is because template0 is intended to remain unchanged and should not be modified.
In a new PostgreSQL installation, template0 and template1 are identical. So why are there two template databases? The reason is that template0 serves as a clean backup copy. If template1 is modified too much or accidentally changed by adding unwanted objects, extensions, or settings, you can use template0 to create a fresh database and recover easily.
-- Verify current templates before changes
postgres=# drop database template1;
ERROR: cannot drop a template database
postgres=#
postgres=# SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datallowconn
-----------+---------------+--------------
template0 | t | f
template1 | t | t
(2 rows)
postgres=#
postgres=# update pg_database set datistemplate = false where datname = 'template1'; --- OR --- ALTER DATABASE template1 IS_TEMPLATE true;
UPDATE 1
postgres=# drop database template1;
DROP DATABASE
postgres=#
postgres=# SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datallowconn
-----------+---------------+--------------
template0 | t | f
(1 row)
postgres=#
postgres=# \l temp*
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
template0 | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(1 row)
postgres=#
----Recreate CREATE DATABASE template1
postgres=# create database template1 template template0;
CREATE DATABASE
postgres=#
postgres=# SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datallowconn
-----------+---------------+--------------
template0 | t | f
template1 | f | t
(2 rows)
postgres=# ^
postgres=# update pg_database set datistemplate = true where datname = 'template1';
UPDATE 1
postgres=#
postgres=# SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname LIKE 'template%' ORDER BY datname;
datname | datistemplate | datallowconn
-----------+---------------+--------------
template0 | t | f
template1 | t | t
(2 rows)
postgres=#
12. Can we drop “Template0” database?
Yes, we can drop by changing update pg_database set datistemplate = false where datname = 'template0'; , but Do not drop template0. There is no practical benefit, and it removes PostgreSQL's built-in clean template database. If template0 is accidentally modified or removed, recovery usually requires restoring it from another PostgreSQL instance of the same version or reinitializing the cluster. Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/