When people start to work with PostgreSQL, especially when they are used to Oracle, some things might be very confusing. A few of the questions we usually get asked are:
- Where is the listener and how can I configure it?
- When you talk about a PostgreSQL cluster where are the other nodes?
- Why do we have these template databases and what are they used for?
- …and some others…
In this post we’ll look at the last point: Why do we have two template databases (template0 and template1) and additionally a database called “postgres”? That makes three databases by default. In Oracle we only have one, well two when you use pluggable databases (the root and pdb$seed). Why does PostgreSQL need three by default? Isn’t that only overhead? Lets see …
To begin with: Assuming these databases are really not required we can drop them, can’t we?
(postgres@[local]:5432) [postgres] > l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
Can we drop the “postgres” database?
(postgres@[local]:5432) [postgres] > drop database postgres; ERROR: cannot drop the currently open database Time: 1.052 ms
Ok, this is the first point to remember: 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@pgbox:/home/postgres/ [PG960] psql template1 psql (9.6rc1 dbi services build) Type "help" for help. (postgres@[local]:5432) [template1] > drop database postgres; DROP DATABASE Time: 489.287 ms (postgres@[local]:5432) [template1] >
Uh, our default “postgres” database is gone. Does it matter? Not really from a PostgreSQL perspective but probably all your clients (pgadmin, monitoring scripts, …) will have a problem now:
postgres@pgbox:/home/postgres/ [postgres] psql psql: FATAL: database "postgres" does not exist
The “postgres” database is meant as a default database for clients to connect to. When you administer a PostgreSQL instance which runs under the postgres operating system user the default database that is used for a connection is the same as the username => postgres. Now that this database does not exist anymore you can not longer connect if you do not provide a database name in you connection request. But we can still connect to “template1”:
postgres@pgbox:/home/postgres/ [postgres] psql template1 psql (9.6rc1 dbi services build) Type "help" for help.
Second thing to remember: The “postgres” database is meant as a default database for connections. It is not required, you can drop it but probably a lot of tools you use will need to be adjusted because they assume that the “postgres” database is there by default.
Luckily we can easy recover from that:
postgres@pgbox:/home/postgres/ [postgres] psql template1
psql (9.6rc1 dbi services build)
Type "help" for help.
(postgres@[local]:5432) [template1] > create database postgres;
CREATE DATABASE
(postgres@[local]:5432) [template1] > l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
What happened? We connected to template1 again and re-created the “postgres” database. Of course everything we added to the “postgres” database before we dropped it is not any more available. This brings us to the next question: When we create a new database what or who defines the initial contents?
Third thing to remember: When you create a new database by using the syntax “create database [DB_NAME]” you get an exact copy of template1.
Really? What happens when I modify template1? Lets add one table and one extension:
postgres@pgbox:/home/postgres/ [postgres] psql template1
psql (9.6rc1 dbi services build)
Type "help" for help.
(postgres@[local]:5432) [template1] > create table my_test_tab ( a int );
CREATE TABLE
(postgres@[local]:5432) [template1] > create extension hstore;
CREATE EXTENSION
Time: 123.722 ms
(postgres@[local]:5432) [template1] > dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------------
hstore | 1.4 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
If the above statement is true every new database created with the above syntax should contain the table and the extension, right?
(postgres@[local]:5432) [postgres] > c db_test
You are now connected to database "db_test" as user "postgres".
(postgres@[local]:5432) [db_test] > d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | my_test_tab | table | postgres
(1 row)
(postgres@[local]:5432) [db_test] > dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------------
hstore | 1.4 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
Whatever you put into template1 will be available in a new database if you use the following syntax: “create database [DB_NAME];” This can simplify your deployments a lot if you rely on pre-installed objects for e.g. monitoring or development.
Ok, I got it, but what is template0 for then? For this to understand we first take a look at pg_database, especially two columns: datallowconn and datistemplate:
(postgres@[local]:5432) [db_test] > select datname,datallowconn,datistemplate from pg_database order by 3; datname | datallowconn | datistemplate -----------+--------------+--------------- postgres | t | f db_test | t | f template1 | t | t template0 | f | t (4 rows)
When you take a look at “datallowcon” the only database that has set this to false is “template0”. Do you remember the beginning of this post when we tried to delete the “postgres” database? You can only delete a database when there are no connections. But: You can only create a database from another database if there are no connections to the source, too. Really? Why then can I create a new database when I am connected to template1 when template1 is the source for the new database?
(postgres@[local]:5432) [template1] > create database db_test_2; CREATE DATABASE
Confusing? This does not work anymore if there is another session connected to template1.
Lets try to create another new database but this time we we use db_test as the source. Yes, this is possible if you slightly adjust the syntax. But before we create the database we create a another connection to db_test:
(postgres@[local]:5432) [template1] > q postgres@pgbox:/home/postgres/ [PG960] psql db_test psql (9.6rc1 dbi services build) Type "help" for help.
In another session we try to create new database with db_test as the source:
postgres@pgbox:/home/postgres/ [PG960] psql postgres psql (9.6rc1 dbi services build) Type "help" for help. (postgres@[local]:5432) [postgres] > create database db_test_3 template db_test; ERROR: source database "db_test" is being accessed by other users DETAIL: There is 1 other session using the database.
Fourth point to remember: For creating new databases you can use whatever database you like as the source when you specify then template explicitly.
Fifth point to remember: When you want to drop a database or when you want to create a database there must be no connections to the database you either want to drop or you want to create a new database from.
Coming back to the “datistemplate” and “datallowcon” settings: template0 is the only database that has “datallowcon” set to false, why? Because template0 is meant as the default unmodifiable database. You never should make any changes there. In a brand new PostgreSQL instance template0 and template1 are exactly the same. But why do I need both of them then? Assume you messed up template1 somehow (installed too many objects, for example). Using template one you still can recover from that easily:
postgres@pgbox:/home/postgres/ [PG960] psql postgres psql (9.6rc1 dbi services build) Type "help" for help. (postgres@[local]:5432) [postgres] > update pg_database set datistemplate = false where datname = 'template1'; UPDATE 1 (postgres@[local]:5432) [postgres] > drop database template1; DROP DATABASE (postgres@[local]:5432) [postgres] > create database template1 template template0; CREATE DATABASE (postgres@[local]:5432) [postgres] > update pg_database set datistemplate = true where datname = 'template1'; UPDATE 1
What happened here? I modified template1 to not being a template anymore because you can not drop a database flagged as a template. Then I dropped and re-created the template1 database by using template0 as the template. Now my template1 is an exact copy of template0 again and all the things I messed up are gone. Another use case for this is: Image you modified template1 to include the stuff you rely on but at some point in the future you need a new database which shall be without your modifications (e.g. restoring a dump). For this you always can use template0 as a template because template0 is always clean. And this is why connections are not allowed to template0.
Of course you can create your own template database(s) by setting the “datistemplate” to true:
(postgres@[local]:5432) [postgres] > update pg_database set datistemplate = true where datname = 'db_test'; UPDATE 1 (postgres@[local]:5432) [postgres] > drop database db_test; ERROR: cannot drop a template database
What about the overhead:
(postgres@[local]:5432) [postgres] > select * from pg_size_pretty ( pg_database_size ( 'template0' )); pg_size_pretty ---------------- 7233 kB (1 row) (postgres@[local]:5432) [postgres] > select * from pg_size_pretty ( pg_database_size ( 'template1' )); pg_size_pretty ---------------- 7233 kB (1 row) (postgres@[local]:5432) [postgres] > select * from pg_size_pretty ( pg_database_size ( 'postgres' )); pg_size_pretty ---------------- 7343 kB (1 row)
Should not really be an issue on todays hardware. Hope this puts some light on these default databases.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
Ali
31.07.2023I lost both of template0 and template1 database and when I want to create template0 from postgres database it shows me an error and it says :source database "db_test" is being accessed by other users
DETAIL: There is 1 other session using the database.
please help me