Create a PostgreSQL database

Our application needs its own database. Create it while connected as an administrator:

CREATE DATABASE notes_app OWNER notes_owner;

Don’t forget the semicolon. Without it, psql waits on a continuation prompt and nothing runs.

The OWNER clause matters. Without it, the database belongs to whoever ran the command, usually your personal admin role. We want notes_owner to own it, so ownership follows the role we created for exactly that job, not a person who might leave the project.

Behind the scenes, PostgreSQL does not create the database from nothing. It clones template1, which is why a “new” database already contains a public schema.

Verify the result before moving on:

\l notes_app

                       List of databases
   Name    |    Owner    | Encoding | ... | Access privileges
-----------+-------------+----------+-----+-------------------
 notes_app | notes_owner | UTF8     |     |

The owner column should say notes_owner. If it shows your admin role, fix it with ALTER DATABASE notes_app OWNER TO notes_owner;.

Connect to the new database

Creating a database does not move you into it. A PostgreSQL connection works inside one database, so reconnect:

\connect notes_app

\connect closes the current connection and opens another one. Confirm the result with \conninfo before creating schemas or tables. Skipping that check is how tables end up in postgres, the maintenance database, instead of the application database.

Two errors you may hit

If your role lacks the CREATEDB attribute, the server refuses:

ERROR:  permission denied to create database

That is a role-attribute problem, not a grants problem. An administrator fixes it with ALTER ROLE flavio CREATEDB; or runs the creation for you.

The second one bites people running migrations: CREATE DATABASE cannot run inside a transaction block. Migration tools often wrap every step in BEGIN ... COMMIT, and the statement fails with exactly that message. Create databases as a separate manual step, and let migrations manage what lives inside the database instead.