11.3. Creating Databases

Why Creating Databases Matters

In PostgreSQL, every application usually gets its own database. Before you can store rows in tables or run queries, you need a database to connect to. As a backend developer, you will often:

Create a new database for a new project.
Connect your application to the right database.
Clone databases for testing or development.
Drop databases that are no longer needed.

This chapter focuses on practical, day‑to‑day database management tasks in PostgreSQL: creating, listing, connecting to, renaming, and removing databases, both from the command line and inside PostgreSQL.

Key idea:
In PostgreSQL, you always connect to a database inside a PostgreSQL server instance. You never connect to “PostgreSQL in general,” you connect to a specific database like postgres, template1, my_app_db, and so on.

You will not learn SQL querying here in depth, that is covered in the SQL section. Here we focus on how to manage databases themselves.

System Databases: What You Already Have

When you install PostgreSQL, it usually comes with some default databases:

Database	Purpose
`postgres`	Default “utility” database, safe to use for admin tasks.
`template1`	Template used when you create a new database.
`template0`	Clean, read‑only template with no local changes.

You normally connect to postgres when you create new databases.

Example: using psql (PostgreSQL interactive shell) from your terminal:

bash

psql -U postgres -d postgres

Here:

-U postgres is the user (role) name.
-d postgres is the database name.

If your user and database have the same name, you can often just run:

bash

psql

on your local machine.

Listing Databases

Before creating a new database, you often want to see what already exists.

Using `psql` meta commands

Inside psql, run:

sql

\l

sql

\l+

\l lists databases. \l+ shows more details like size and description.

Example output (simplified):

text

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
 my_app_db | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Using SQL

You can also query PostgreSQL’s system catalog:

sql

SELECT datname
FROM pg_database
ORDER BY datname;

This is less common for manual work, more useful in scripts or tools.

Creating a Database with SQL

The main SQL command for creating a database is CREATE DATABASE.

Basic syntax:

sql

CREATE DATABASE database_name;

Example:

sql

CREATE DATABASE my_app_db;

This creates a new database named my_app_db with default settings. You will usually run this command while connected to the postgres database as a superuser or a user that has CREATEDB privilege.

Rule:
You cannot create a database inside your new database.
You must be connected to some other database (commonly postgres) to run CREATE DATABASE.

Choosing Owner, Encoding, and Template

For real projects, you often want more control: who owns the database, which character encoding to use, and which template database to copy.

Setting the database owner

The owner of a database controls it. You can specify it:

sql

CREATE DATABASE my_app_db
    OWNER myapp_user;

This means myapp_user will be able to create tables and manage objects inside my_app_db (permissions can be refined, but that belongs to the Permissions chapter).

If you do not specify OWNER, PostgreSQL uses the role that runs the command.

Setting the encoding

Encoding defines how text is stored. Modern applications almost always use UTF‑8.

sql

CREATE DATABASE my_app_db
    WITH ENCODING 'UTF8';

If your cluster is already initialized with UTF‑8 (very common), you usually do not need to set this manually.

Using templates

When you create a database, PostgreSQL actually copies another database called a template.

template1 is the default template.
template0 is a “clean” template with no local modifications.

Syntax:

sql

CREATE DATABASE my_app_db
    TEMPLATE template1;

sql

CREATE DATABASE my_app_db
    TEMPLATE template0;

You might use template0 when:

You want a very clean database.
You need different encoding or locale settings.

Example with multiple options:

sql

CREATE DATABASE my_app_db
    WITH OWNER = myapp_user
         ENCODING = 'UTF8'
         TEMPLATE = template1;

These options can appear in any order after WITH.

Creating Databases with `createdb` (Command Line)

PostgreSQL also provides a convenience command line tool called createdb. It is a wrapper around CREATE DATABASE.

Basic usage:

bash

createdb my_app_db

This:

Connects to a default database (usually postgres).
Runs CREATE DATABASE my_app_db; as your operating system user, if mapped to a PostgreSQL role.

Common options:

Option	Meaning	Example
`-U username`	PostgreSQL user to connect as	`createdb -U postgres my_app_db`
`-O owner`	Set database owner	`createdb -O myapp_user my_app_db`
`-E encoding`	Set encoding	`createdb -E UTF8 my_app_db`
`-T template`	Template database to copy	`createdb -T template0 my_app_db`
`-h host`	Hostname, if DB runs on another machine	`createdb -h db.example.com my_app_db`
`-p port`	Port number (default 5432)	`createdb -p 5433 my_app_db`

Example combining options:

bash

createdb -U postgres \
         -O myapp_user \
         -E UTF8 \
         -T template1 \
         my_app_db

You will often call this from deployment scripts or CI pipelines.

Common Options and Settings When Creating a Database

While CREATE DATABASE has many options, for backend applications you will mainly care about:

Option	Use case
`OWNER`	Assign database to application role.
`TEMPLATE`	Choose db to copy from, typically `template1`.
`ENCODING`	Usually `'UTF8'` for modern apps.
`LC_COLLATE`	Sort order of text, rarely changed manually.
`LC_CTYPE`	Character classification, rarely changed.
`CONNECTION LIMIT`	Maximum concurrent connections to this database.

Example with connection limit:

sql

CREATE DATABASE my_app_db
    WITH OWNER = myapp_user
         ENCODING = 'UTF8'
         CONNECTION LIMIT = 50;

CONNECTION LIMIT = -1 means no limit (default).

As a beginner, you will rarely change collate or ctype. Those are usually set when you create the PostgreSQL cluster, not individual databases.

Connecting to a Specific Database

Once you create a database, you need to connect to it, both in psql and from your backend application.

Using `psql`

From your terminal:

bash

psql -U myapp_user -d my_app_db

If you already connected to psql (for example to postgres), you can switch databases with:

sql

\c my_app_db

or, to specify user as well:

sql

\c my_app_db myapp_user

This is very common during development. For example:

Connect to postgres as admin.
Run CREATE DATABASE my_app_db OWNER myapp_user;
Run \c my_app_db to jump into your new database.
Start creating tables.

Typical connection string for applications

Applications like FastAPI usually connect using a connection string.

Example PostgreSQL URL:

text

postgresql://myapp_user:mysecretpassword@localhost:5432/my_app_db

Parts:

postgresql:// is the scheme.
myapp_user is the username.
mysecretpassword is the password.
localhost is host.
5432 is port.
my_app_db is the database name.

Your backend will need this connection string in a configuration file or environment variable.

Renaming a Database

Sometimes the name of a project changes or you want to standardize names. You can rename a database with:

sql

ALTER DATABASE old_name RENAME TO new_name;

Example:

sql

ALTER DATABASE my_app_db RENAME TO my_app_db_prod;

Important rules for renaming:

You cannot be connected to the database you are renaming.
No one else can be connected to it either.

So you must:

Connect to a different database, for example postgres.
Make sure there are no active connections to old_name.
Run ALTER DATABASE.

In psql, if you are connected to postgres:

sql

SELECT pid, datname, usename, client_addr
FROM pg_stat_activity
WHERE datname = 'my_app_db';

Terminate active sessions if needed (admin task, do this carefully):

sql

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'my_app_db'
  AND pid <> pg_backend_pid();

Then rename:

sql

ALTER DATABASE my_app_db RENAME TO my_app_db_prod;

After renaming, remember to update your application connection strings.

Dropping (Deleting) Databases Safely

When a database is no longer needed, you can remove it with DROP DATABASE.

Syntax:

sql

DROP DATABASE database_name;

Example:

sql

DROP DATABASE my_app_db_test;

This permanently deletes:

All tables.
All data.
All other objects in that database.

There is no built in “undo” or “trash” bin.

Never drop a database unless you are absolutely sure and you have backups if the data matters.

Common rules and errors

You cannot drop a database you are connected to.
You cannot drop a database while others are connected to it.
You must have sufficient privileges, usually be owner or superuser.

If you get:

text

ERROR:  database "my_app_db_test" is being accessed by other users

then:

Connect to another database (e.g. postgres).
Inspect connections:

sql

   SELECT pid, usename, client_addr
   FROM pg_stat_activity
   WHERE datname = 'my_app_db_test';

Terminate them carefully:

sql

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE datname = 'my_app_db_test'
     AND pid <> pg_backend_pid();

Run DROP DATABASE my_app_db_test; again.

Typical Workflow for a New Project

Here is a simple, realistic sequence you might follow when starting a Python backend project with PostgreSQL.

Step 1: Create a PostgreSQL role for your app

You will do this in the “Users and Roles” chapter in detail, but a simple example:

sql

CREATE ROLE myapp_user WITH LOGIN PASSWORD 'mysecretpassword';

Step 2: Create the application database

Connected to postgres as an admin:

sql

CREATE DATABASE my_app_db
    WITH OWNER = myapp_user
         ENCODING = 'UTF8';

Or using command line:

bash

createdb -U postgres -O myapp_user -E UTF8 my_app_db

Step 3: Connect and verify

bash

psql -U myapp_user -d my_app_db

Inside psql:

sql

SELECT current_database(), current_user;

You should see:

text

 current_database | current_user
------------------+-------------
 my_app_db        | myapp_user

Now you can start creating tables and schemas, which is covered later in the course.

Step 4: Configure your backend application

In your application configuration, set something like:

text

DATABASE_URL=postgresql://myapp_user:mysecretpassword@localhost:5432/my_app_db

Your ORM (for example SQLAlchemy) or database driver will use this string.

Using Separate Databases for Development, Testing, and Production

A common backend pattern is to have multiple databases per project:

Environment	Typical DB name	Purpose
Development	`my_app_db_dev`	Local work on your machine.
Testing	`my_app_db_test`	Automated tests, can be wiped often.
Production	`my_app_db_prod`	Real user data, very important.

You can create them in the same way, just with different names:

sql

CREATE DATABASE my_app_db_dev  OWNER myapp_dev_user  ENCODING 'UTF8';
CREATE DATABASE my_app_db_test OWNER myapp_test_user ENCODING 'UTF8';
CREATE DATABASE my_app_db_prod OWNER myapp_prod_user ENCODING 'UTF8';

Each environment uses its own connection string, for example:

Development: postgresql://myapp_dev_user:devpass@localhost:5432/my_app_db_dev
Testing: postgresql://myapp_test_user:testpass@localhost:5432/my_app_db_test
Production: postgresql://myapp_prod_user:prodpass@db.example.com:5432/my_app_db_prod

This separation makes it much safer, because experiments on your dev database do not touch real user data.

Summary

In this chapter you learned how to:

List existing databases with \l in psql.
Create a new database with CREATE DATABASE and createdb.
Control important options like OWNER, ENCODING, and TEMPLATE.
Connect to a specific database with psql and with connection strings.
Rename databases safely using ALTER DATABASE.
Drop databases with DROP DATABASE, after ensuring no active connections.
Use different databases for development, testing, and production.

These are fundamental skills for any backend developer who uses PostgreSQL. In later chapters, you will learn how to create tables, define schemas, and connect these databases to your Python backend applications.

11.3. Creating Databases

Why Creating Databases Matters

System Databases: What You Already Have

Listing Databases

Using `psql` meta commands

Using SQL

Creating a Database with SQL

Choosing Owner, Encoding, and Template

Setting the database owner

Setting the encoding

Using templates

Creating Databases with `createdb` (Command Line)

Common Options and Settings When Creating a Database

Connecting to a Specific Database

Using `psql`

Typical connection string for applications

Renaming a Database

Dropping (Deleting) Databases Safely

Common rules and errors

Typical Workflow for a New Project

Step 1: Create a PostgreSQL role for your app

Step 2: Create the application database

Step 3: Connect and verify

Step 4: Configure your backend application

Using Separate Databases for Development, Testing, and Production

Summary

Comments

Where to Move