Developer's Guide to PostgreSQL on Linux: Creating Databases

By Adam McQuistan in PostgreSQL  09/30/2019 Comment

Introduction

This is the third article of a series featuring a collection of useful tasks for administering PostgreSQL version 11 on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I show how to create databases using both SQL statements along with the createdb utility program.

Series Contents

• Installation
• psql interactive shell
• Creating Databases (you're here)
• Creating Accounts and Making Connections
• Changing the Default Data Directory
• Using Explain and Analyze (coming soon)
• Utilitizing Indexes (coming soon)
• Creating Backups (coming soon)
• Restoring From Backups (coming soon)

Creating Databases with SQL

Databases can be created in PostgreSQL using traditional SQL as in the following set of commands which issues a CREATE DATABASE statement in the psql shell to define a database named test.

$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \q

Creating Databases Using createdb Utility Program

PostgreSQL also provides a utility program, createdb, which can be used to create databases. To accomplish this you issue the createdb program name followed by the name of the database you are creating. Additionally, I prefer to add the --echo flag to the command which prints out the underlying SQL statements that are executed against the PostgreSQL cluster.

$ createdb test2 --echo
SELECT pg_catalog.set_config('search_path', '', false)
CREATE DATABASE test2;

Notice that this program is used from the standard Linux shell no the psql interactive PostgreSQL shell.

Now when I list the databases in the psql shell I see the new test and test2 datatabases.

$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 test      | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 test2     | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
(5 rows)

Resources for Learning More About PostgreSQL

• A great, example driven, reference book on robust management of PostgreSQL is PostgreSQL High Availability Cookbook - Second Edition
• A good companion book that is again example driven but focusing on performance is PostgreSQL High Performance Cookbook

thecodinginterface.com earns commision from sales of linked products such as the books above. This enables providing continued free tutorials and content so, thank you for supporting the authors of these resources as well as thecodinginterface.com

Conclusion

In this article I demonstrated two methods for creating database within a PostgreSQL cluster. Specifically, I gave examples of creating databases using traditional SQL statements plus using the createdb PostgreSQL utiltity program.