PostgreSQL Create Database Options and Settings

Problem

One of the first actions a DBA needs to perform on a new RDBMS instance is to create a database. Let’s explore how this can be done in PostgreSQL, including the syntax, particularities, and differences with other RDBMS.

Solution

This tip will show all the peculiarities, features, and different syntaxes of the CREATE DATABASE command in PostgreSQL.

PostgreSQL CREATE DATABASE

The basic syntax of the CREATE DATABASE command is fairly simple and straightforward: Type CREATE DATABASE and the name of the database, and you’re done. However, to do so, you must have the required privileges as a superuser or have the CREATEDB permissions.

A quick digression on superuser: In PostgreSQL, it is a role quite similar to the SA of SQL Server, and the default created at PostgreSQL setup is the superuser postgres. We will return to this topic when I write a more in-depth article about ROLES. Anyway, this topic was briefly touched on in this two-part tip:

Now, let’s return to the CREATE DATABASE statement and its options for a new database.

Collation

First, we can specify the role of the database owner. If not specified, it is, by default, the one that we are using for the connection. I’ll return to this later.

As is common with many other RDBMS, we can then define the collation of the database using the LOCALE option. This collation can be explicitly specified. If not, the default one of the template database will be used. However, only the collations with the libcprovider, directly obtained from the operating system, can be used with this option.

Let’s look at all available collations in the PostgreSQL cluster, querying the catalog pg_collation:

-- MSSQLTips.com
 
select collname, colliculocale, collversion, collcollate, collctype, collprovider
from pg_collation;

At this point, we can use the collation option of the CREATE DATABASE statement. One option is with the collprovider = ‘c’ (OS collations) or the second option is to specify both locale_provider and icu_locale. To use the icu options select one marked with ‘i’.

Let’s see an example of both for a new Postgres database:

create database test_collation template template0 locale='C.UTF-8';

create database test_collation2 template template0 locale_provider='ICU' icu_locale 'de-AT';

Note: In both cases, since we are using a collation different from the default, we have to specify template0 instead of the default, which is template1. We’ll dive into templates in the next paragraph. In the second case, setting the locale_provider and icu_locale overrides the locale option. Let’s take a look at the pgAdmin list of databases:

And check the collation querying the pg_database catalog with the following command:

-- MSSQLTips.com
 
SELECT datname, datcollate AS collation, datlocprovider
FROM pg_database
where datname like 'test%';

The default collation is still used in the test_collation2 database, but the locale provider is ICU. As mentioned before, the locale_provider and icu_locale override the locale parameter even though it is specified in the database collation.

Using ICU collations instead of operating system collations has an advantage in that they are independent from operating systems and their changes, thus avoiding possible data corruption.

Templates

We briefly introduced templates in the previous paragraph, but there is much to say about them. When creating a database, we always use a template, as in SQL Server with a model database. In PostgreSQL, we have two, as we saw before, template0 and template1, which are automatically created when we initialize the cluster. (Remember that in PostgreSQL, an instance is called a cluster.) As we have seen, template1 is the default, and template0 is used when you need to specify some not default options for database creation. But there is more to it. We can create our own template databases and even create a database copying from another one, albeit with some limitations.

So, suppose we need a database that should be cloned for test purposes. We have a couple of options using templates. One option is that we can make a new template database using the parameter is_template in the CREATE DATABASE command:

create database template_test with is_template=true allow_connections=false;

Notice that I also specified that the allow_connections option is false; otherwise, we would have an error when cloning the database (more on that soon).

If we check the list of the databases from pgAdmin, we see that the template that we just created is not present:

Now, we can create a database using this template:

create database test_template with template template_test;

Now check the list:

We can now drop the database we just created to show the other method (create a template database using an existing one).

drop database test_template;

First of all, we need to look at the pg_database table, in particular at some columns with the following SQL command:

-- MSSQLTips.com
 
select oid, datname, datistemplate, datallowconn
from pg_database

As you notice, both template0 and template1 have the column datistemplate set to true. So, we can just set this column value to true and, just for security, the datallowconn one to false.

Let’s update those columns on the test_collation2 database using the oid and see:

-- MSSQLTips.com
 
update pg_database
set datallowconn=false, datistemplate=true
where oid=68121;

Let’s look again at pg_database:

If we check the list of the databases present in this cluster with pgAdmin:

We see that now test_collation2 is no longer listed.

Finally, we can create another database using this new template that is now test_collation2:

create database test_template with template test_collation2;

We can check the list of databases in pgAdmin and also query again the pg_database catalog:

-- MSSQLTips.com
 
SELECT datname, datcollate AS collation, datlocprovider, datistemplate
FROM pg_database
where datname like 'test%';

In this case, we can see that the database test_template has been created with the same collation as test_template and that this one is not a template.

We can also create a database by cloning another without a template database. In this case, we must ensure that the database from which we copy has no active connections opened and that the user doing that is a superuser or the owner of the database. Instead, with templates, any account with the create database privilege can clone them.

Let’s see an example. Open a connection with psql on the database Apache:

Issue this simple query:

Repeat this query every second using the psql command watch:

This will simulate a continuous active session on the database Apache:

Now, in another query editor in pgAdmin:

create database test_apache with template "Apache";

As we can see, we cannot use this database as a template because there are sessions connected to it, so we stop our load in psql and clean up all the connected sessions:

Check into the pgAdmin dashboard; there is still an idle connection:

We can kill the connection directly from the dashboard:

Now we can issue the command again:

create database test_apache with template "Apache";

That now works.

Tablespaces

Another important point in the CREATE DATABASE command is the possibility of specifying a tablespace on which the database files will be stored. As the PostgreSQL documentation says: “Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation.”

Essentially, a tablespace in PostgreSQL is a directory containing data files. These data files are the storage for database objects: tables, sequences, indexes, and materialized views. In PostgreSQL, each such object has its own data file (or more if it’s big).

This is similar to the concept of file groups in SQL Server, where database objects that are heavily used can be stored on specific fast disks and other less used objects on lower-tier storage.

Pay attention to the special disclaimer for Oracle DBAs: The concept of PostgreSQL Tablespaces is completely different from Oracle! In fact, PostgreSQL uses the operating system’s filesystem for storage, while Oracle implements its own file system. In PostgreSQL, a tablespace is a way to tell the RDBMS where the physical files of database objects are stored. It is not a part of the file system; it is only a link to where the objects are.

So, as a first step, we can create a new tablespace, different from the default tablespace pg_default, that is automatically created when we initialize a PostgreSQL instance, and that is used if we do not indicate it in the CREATE object command (obviously, we need to be superuser or have the CREATE TABLESPACE privilege to do so):

create tablespace test_tablespace location '/var/lib/postgresql/test';

We can now query the pg_tablespace catalog to see the tablespaces present in our environment:

-- MSSQLTips.com
 
select *
from pg_tablespace;

Now that we have defined a new tablespace, we can use it in order to create a database on it:

create database test_tablespace with tablespace=test_tablespace;

We can now query the pg_database catalog joined with pg_tablespaces:

-- MSSQLTips.com
 
select datname, spcname
from pg_database
inner JOIN pg_tablespace t ON dattablespace = t.oid;

And we can see that database test_tablespace resides on the tablespace with the same name.

Owner

Last but not least, we need to talk about the owner of the database. If this is not specified in the CREATE DATABASE command, the owner will be the user/role login with which we are connected to PostgreSQL. Otherwise, we can define a different owner for the database.

Let’s take a look with an example using an existing role created from a previous tip:

create database test_owner with owner="Finance";

Let’s check the pg_database catalog joining it with pg_roles one:

-- MSSQLTips.com
 
select dat.oid, datname, datdba, rolname
from pg_database dat
inner join pg_roles rol on datdba=rol.oid;

We can see that the database that we just created has the owner (datdba column in pg_database) set to role Finance.

Next Steps

In this tip, we have reviewed the CREATE DATABASE command in PostgreSQL, checking some of the most important options and parameters and looking at some catalog table queries that can help check database objects in PostgreSQL.

As always, the first source of information on PostgreSQL is the official documentation: CREATE DATABASE.

Here are some links to other tips on CREATE DATABASE in SQL Server:

Resources

Community

Subscribe