PostgreSQL: Up and Running, 4th Edition

Chapter 3. Creating Databases and Installing Extensions

A Note for Early Release Readers

With Early Release ebooks, you get books in their earliest form—the authors’ raw and unedited content as they write—so you can take advantage of these technologies long before the official release of these titles.

This will be the 3rd chapter of the final book. Please note that the GitHub repo will be made active later on.

If you’d like to be actively involved in reviewing and commenting on this draft, please reach out to the editor at [e-mail].

In this chapter we’ll cover the basics of creating a database, adding various functionality you will need in a database via extensions, establishing privileges to the various objects in a database, and finally backup and restore of data.

Database Creation

Before you can create a database, you must be connected to an existing database. If you haven’t created any databases yet, the only database you will be able to connect to is the postgres database. After you have connected, the minimum SQL command to create a database is as follows:

CREATE DATABASE mydb;

This creates a new database replicated on the template1 database. The role logged-in becomes the owner of the database. Any role having CREATEDB attribute can create new databases. You can also create a database and assign ownership to a different role even if that role doesn’t have CREATEDB attribute:

CREATE DATABASE mydb OWNER leo;

Template Databases

A template database is, as the ...