PG Casts

Creating and Using a Custom Template Database

Episode #26 hosted by Mary Lee

Hey everyone, today we're going to look at how to create and use a custom template database in Postgres.

Postgres Version 11


To create a new template database, we use the normal "create database" command, passing the parameter "is_template" with a value of "true".

If we are customizing locales or encoding on our new custom template, we should make sure that we are cloning from Postgres's template0, rather than the default template1.

create database example is_template true encoding 'SQL_ASCII' template template0;

We can see our new template database with the list command.


And we can verify that it is a template by checking out the pg_database data.

select datistemplate from pg_database where datname = 'example';

To create a new database cloned from our custom template, we use the "create database" command, passing our template name to the template parameter.

create database test template example;

Using the list metacommand again, we can see that our new database has the SQL_ASCII encoding from our custom template.


Thanks for watching!