A Software Engineer's Tips and Tricks #2: Template Databases in PostgreSQL

Hey there! We're back for our second edition of Tips and Tricks. As we said in our first post on Drizzle ORM, our new Tips and Tricks mini blog series is going to share some helpful insights and cool tech that we've stumbled upon while working on technical stuff.

Today, we're going to talk about the template databases of PostgreSQL. Remember, these posts will be super short reads. If you don’t like the topic of one of the posts, no problem! Just skip it and check out the next one.

But if you do find something you like, please share it, tweet it, or tell your friends about it. And more importantly, don’t forget to check out the "further reading" links we’ll include at the bottom of each post for more info to dive deeper into the topic.

Sound good? Keep an eye on our blog, and let’s see where this goes!

Template Databases in PostgreSQL

Have you ever noticed the template0 and template1 databases in PostgreSQL?

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
(3 rows)

These databases are template databases, used as the basis for new databases. In other words, when you create a new database, PostgreSQL simply makes a copy of template1.

Helpful for Creating Extensions Automatically

Let’s say you want to create a new users table, and have a id of type uuid with a default value.

create table users(id uuid default uuid_generate_v4(), email varchar);

By default, executing this statement will fail because you don’t have the extension uuid-ossp installed:

ERROR:  function uuid_generate_v4() does not exist
LINE 1: create table users(id uuid default uuid_generate_v4(), email...
                                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The fix here is simple. Create the extension!

create extension "uuid-ossp";

Then, you can create the table:

# create table users(id uuid default uuid_generate_v4(), email varchar);
CREATE TABLE

If you often create new databases, you would have to run this CREATE EXTENSION every time. To avoid doing it manually, you can also add the extension to the template1 database:

\c template1
CREATE EXTENSION "uuid-ossp";

Now, all the newly created databases will have the uuid-ossp extension by default, because CREATE DATABASE does nothing more than copy template1 to the new database.

Great for Facilitating Quick, Local Backups

During development, you might want to backup your database locally and restore it just after. For example, you might want to run you-super-script.sh that could destroy all your precious data.

One way to create a backup of your database locally is to run pg_dump and pg_restore. This works well, but it can take some time, and there’s also a simpler solution: use template databases!

You can simply create a new backup database using the database you want to backup as a template. By default, CREATE DATABASE xxx creates xxx by copying template1. If you specify TEMPLATE 'yyy', it copies yyy instead!

CREATE DATABASE backup TEMPLATE 'mydb';

When you are done with your-super-script.sh, you can simply recreate your database from the backup previously made:

\c postgres
DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE 'backup';

It wouldn’t be wise to use this method to backup your production databases, since mydb and backup are both stored on the same physical hard drive. But for testing purposes, this handy method can help you to create and restore databases rapidly.

Further reading

• PostgreSQL template databases: https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
• Backup and restore a PostgreSQL database: https://www.postgresql.org/docs/current/backup.html
• Serverless PostgreSQL databases on Koyeb: https://www.koyeb.com/docs/databases

Blazing-Fast Deployments

Run your serverless Postgres databases next to your serverless Apps and Services on Koyeb.

Deploy Now

SIGTERM

That’s it for today! We hope you enjoyed today's tips and tricks. If you have any feedback or suggestions for future posts, feel free to reach out! You can find us on Twitter (or X) at @gokoyeb, LinkedIn, or the Koyeb Community.