How to Create a Database in PostgreSQL

Abdul Wahab Junaid

3 years ago

Once PostgreSQL is installed and running, the very first real task is creating a database. Everything in PostgreSQL — tables, views, functions, indexes — lives inside a database, so getting this step right is the foundation for everything that follows. In this guide, I’ll cover every practical way to create a database in PostgreSQL, explain the parameters you can set along the way, and go through the common issues people run into.

Understanding Databases in PostgreSQL

PostgreSQL is a bit different from some other database systems in how it organizes things. A single PostgreSQL server (called a “cluster” in Postgres terminology, though it has nothing to do with multiple physical machines) can host many separate databases. Each database is fully isolated from the others — you can’t directly query across databases the way you can across schemas within the same database.

When PostgreSQL is installed, it automatically creates a few default databases:

postgres — a default database meant mainly for administrative connections
template0 and template1 — template databases used as the basis for new databases

That last point matters more than it seems. Every new database you create is actually a copy of a template database, usually template1. This is why you can customize template1 if you want every future database to start with certain extensions or settings already in place.

Method 1: Creating a Database with `CREATE DATABASE`

The most direct way to create a database is through SQL, using the CREATE DATABASE statement inside psql or any SQL client connected to PostgreSQL.

Connect to PostgreSQL first:

sudo -u postgres psql

Then run:

CREATE DATABASE mydatabase;

That’s it — a fully functional, empty database named mydatabase now exists. Confirm it with:

\l

This lists all databases on the server, and you should see mydatabase in the output alongside the defaults.

Method 2: Creating a Database with `createdb` (Command Line)

PostgreSQL also ships with a command-line utility, createdb, that wraps the CREATE DATABASE SQL command so you don’t need to open psql first.

createdb -U postgres mydatabase

The -U flag specifies which PostgreSQL user runs the command. If you’re already logged in as the postgres system user, you can drop the flag:

sudo -u postgres createdb mydatabase

This is convenient for scripts, automation, and quick one-off database creation without launching an interactive session.

Full Syntax of `CREATE DATABASE`

The basic form is simple, but CREATE DATABASE supports several optional parameters worth knowing:

CREATE DATABASE database_name
    [ WITH ]
    [ OWNER [=] user_name ]
    [ TEMPLATE [=] template ]
    [ ENCODING [=] encoding ]
    [ LC_COLLATE [=] lc_collate ]
    [ LC_CTYPE [=] lc_ctype ]
    [ TABLESPACE [=] tablespace_name ]
    [ CONNECTION LIMIT [=] connlimit ]
    [ IS_TEMPLATE [=] istemplate ];

Let’s go through what each of these actually does.

OWNER

Assigns a specific PostgreSQL role as the database owner instead of defaulting to whichever role ran the CREATE DATABASE command.

CREATE DATABASE myapp_db OWNER myapp_user;

This matters for permissions — the owner has full control over the database by default, including the ability to drop it.

TEMPLATE

Specifies which template database to copy. Defaults to template1.

CREATE DATABASE myapp_db TEMPLATE template0;

Using template0 instead of template1 is sometimes necessary — for example, when you need to specify a different encoding or locale than what template1 currently has, since template1 may have been customized with objects that conflict with your new settings.

ENCODING

Sets the character encoding for the database. UTF8 is almost always the right choice for modern applications.

CREATE DATABASE myapp_db ENCODING 'UTF8';

LC_COLLATE and LC_CTYPE

These control locale-specific behavior — how strings are sorted (LC_COLLATE) and how character classification works, like what counts as uppercase or whitespace (LC_CTYPE).

CREATE DATABASE myapp_db
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

Note that TEMPLATE template0 is required here in most cases, because template1 typically already has a fixed locale that can’t be overridden without starting from the more minimal template0.

TABLESPACE

Specifies where on disk the database’s files should be stored, useful if you’ve set up multiple tablespaces across different physical drives for performance or storage management reasons.

CREATE DATABASE myapp_db TABLESPACE fast_ssd;

CONNECTION LIMIT

Caps the number of concurrent connections allowed to this specific database. -1 (the default) means unlimited, bounded only by the server’s global connection limit.

CREATE DATABASE myapp_db CONNECTION LIMIT 50;

This is a handy safeguard for multi-tenant servers where you don’t want one database’s application to exhaust every available connection slot.

IS_TEMPLATE

Marks the database as a template itself, meaning it can be used as the basis for future CREATE DATABASE commands, and only superusers (or roles with CREATEDB privilege in some configurations) can connect to it unless this flag is set appropriately.

CREATE DATABASE myapp_template IS_TEMPLATE true;

Practical Examples

Basic database for a small project

CREATE DATABASE blog;

Database with a specific owner (common for application databases)

CREATE ROLE blog_user WITH LOGIN PASSWORD 'strongpassword123';
CREATE DATABASE blog OWNER blog_user;

Database with explicit UTF8 encoding and locale

CREATE DATABASE inventory
    WITH ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

Database with a connection limit for a shared staging server

CREATE DATABASE staging_app CONNECTION LIMIT 20;

Listing Databases

To see everything that currently exists on your server:

\l

Or for more detail using standard SQL:

SELECT datname, datcollate, datctype, datconnlimit
FROM pg_database;

From the command line, without entering psql interactively:

psql -U postgres -l

Connecting to a Database After Creating It

Creating a database doesn’t automatically connect you to it. In psql, switch databases with:

\c mydatabase

You’ll see confirmation like:

You are now connected to database "mydatabase" as user "postgres".

From the command line directly:

psql -U postgres -d mydatabase

Checking Database Size and Details

Once you have a few databases running, it’s useful to check how large each one is:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

This gives a human-readable size (like 24 MB instead of raw bytes), sorted from largest to smallest — handy for spotting a database that’s grown unexpectedly.

Common Use Cases

Separate databases per application. Rather than cramming multiple unrelated applications into one database with different schemas, many teams give each application its own database entirely, which simplifies backups, permissions, and isolation.

Separate databases per environment. It’s common to have myapp_dev, myapp_test, and myapp_production as distinct databases, sometimes even on the same server during early development, to avoid test data ever touching production.

Template databases for consistent setup. If you frequently spin up new databases that all need the same extensions (like pgcrypto or uuid-ossp) pre-installed, customizing template1 once saves repeating that setup every time.

Troubleshooting Common Errors

ERROR: permission denied to create database. The role you’re connected as doesn’t have the CREATEDB privilege. Grant it from a superuser session:

ALTER ROLE myuser CREATEDB;

ERROR: database "mydatabase" already exists. Straightforward — the name is taken. Check existing databases with \l and pick a different name, or drop the existing one first if it’s safe to do so.

ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII). This happens when your chosen encoding conflicts with the template you’re copying from. Switch to TEMPLATE template0, which is more permissive about encoding changes.

Locale-related errors during creation. If LC_COLLATE or LC_CTYPE values you specify aren’t available on your system, you’ll get an error. Check what’s installed with:

locale -a

And install additional locales through your OS package manager if needed (e.g., sudo apt install locales combined with sudo locale-gen en_US.UTF-8 on Debian-based systems).

Best Practices

Always use UTF8 encoding unless you have a very specific reason not to — it avoids a whole category of character-handling headaches later.
Give application databases a dedicated owner role rather than having everything owned by the postgres superuser.
Set connection limits on shared or multi-tenant servers to prevent one database from starving the others of connections.
Use consistent, predictable naming conventions across environments (appname_dev, appname_staging, appname_prod) to avoid confusion.
Document any custom template databases you set up, since their existence isn’t always obvious to teammates.

Customizing template1 for Future Databases

Since every new database (unless you specify TEMPLATE template0) is copied from template1, customizing it once can save repetitive setup work. For example, if every database you create needs the pgcrypto and uuid-ossp extensions available:

\c template1
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

From that point forward, any database created with the default template automatically has these extensions ready to use. This is a small trick, but it’s genuinely useful in environments where you’re spinning up many similar databases — for multi-tenant applications, for instance, where each tenant might get their own database.

Be cautious with this approach, though — anything added to template1 becomes standard in every future database, including ones where it might not be needed. In more controlled environments, it’s often cleaner to create a dedicated custom template (marked with IS_TEMPLATE true) rather than modifying template1 directly, so the default behavior stays predictable.

Renaming a Database

Sometimes a database needs a new name — a project rebrand, a naming convention change, or simply fixing an earlier typo. This is done with ALTER DATABASE, not CREATE DATABASE:

ALTER DATABASE old_name RENAME TO new_name;

As with dropping a database, you can’t rename a database you’re currently connected to — switch to a different database first:

\c postgres
ALTER DATABASE myapp_old RENAME TO myapp_new;

Also note that any existing connections from applications pointing at the old name will need to be updated to use the new one, since the rename doesn’t create an alias — the old name simply stops existing.

Changing a Database’s Owner

If responsibility for a database needs to shift to a different role — say, moving from a personal development account to a dedicated application user — use:

ALTER DATABASE myapp_db OWNER TO new_owner_role;

This changes who has full administrative control over the database itself, though it doesn’t automatically change ownership of every table and object inside it — those may need to be reassigned separately with REASSIGN OWNED BY.

Setting Database-Level Configuration Parameters

Individual databases can override certain server-wide configuration settings just for connections to that specific database, using ALTER DATABASE ... SET:

ALTER DATABASE myapp_db SET statement_timeout = '30s';

This is useful when different databases on the same server have genuinely different needs — for example, an analytics database that regularly runs long queries might need a longer statement_timeout than a transactional application database where a 30-second query almost certainly indicates a problem.

To see the current database-level overrides in place:

SELECT datname, datconfig FROM pg_database WHERE datname = 'myapp_db';

Comparing Databases vs. Schemas

New PostgreSQL users sometimes wonder whether they should create separate databases or separate schemas within a single database for logically distinct pieces of data. It’s worth understanding the trade-off clearly.

Separate databases provide complete isolation — no cross-database queries are possible without extensions like postgres_fdw or dblink, connections are fully separate, and backups can be managed independently per database. This is the right choice when the data truly belongs to unrelated systems, or when strict isolation (like separating different clients’ data entirely) is a requirement.

Separate schemas within one database allow you to logically group tables (e.g., sales.orders, inventory.products) while still being able to join across them directly in a single query, share a connection pool, and manage everything under one backup strategy. This is usually the better choice for different modules of the same application.

As a rule of thumb: if you’ll never need to query across the datasets in a single SQL statement, and isolation matters more than convenience, separate databases make sense. If the data is conceptually part of the same system and you’ll frequently need to join across it, schemas are usually the better fit.

Creating a Database from a Backup

Sometimes “creating a database” really means recreating one from an existing backup — moving data to a new server, setting up a staging copy of production, or restoring after an incident. The general pattern:

createdb -U postgres new_database
pg_restore -U postgres -d new_database backup_file.dump

Or for a plain SQL dump:

createdb -U postgres new_database
psql -U postgres -d new_database -f backup_file.sql

This is a common part of setting up a staging environment that mirrors production data (often with sensitive data scrubbed or anonymized afterward), or when migrating a database between servers or cloud providers entirely.

Creating a Database via GUI Tools

While this guide focuses on SQL and the command line, it’s worth knowing that graphical tools like pgAdmin, DBeaver, and TablePlus all support creating databases through a form-based interface, which under the hood simply runs the same CREATE DATABASE SQL statement covered here. These tools can be genuinely helpful for visually browsing database structure once things exist, but understanding the underlying SQL matters for automation, scripting, and situations where a GUI isn’t available (like a remote server accessed only via SSH).

Setting Up Roles Before Creating a Database

In many real projects, creating the application’s dedicated role happens right alongside creating its database, since the two are closely related:

CREATE ROLE myapp_user WITH LOGIN PASSWORD 'a_strong_password_here' CREATEDB;
CREATE DATABASE myapp_db OWNER myapp_user;

From there, it’s worth explicitly granting the specific privileges the application actually needs, rather than relying on broad default access:

GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

Note that as of PostgreSQL 15, the default behavior around who can create objects in the public schema changed — new databases no longer grant CREATE on the public schema to all users by default, which is a security improvement but occasionally surprises people upgrading from older versions who expect the previous permissive default. If your application role needs to create tables, you may need to grant that explicitly:

GRANT CREATE ON SCHEMA public TO myapp_user;

Multi-Tenant Patterns Using Databases

For applications serving multiple distinct customers or tenants, one architectural approach is giving each tenant their own database, created programmatically as new customers sign up:

CREATE DATABASE tenant_acme_corp OWNER app_service_role CONNECTION LIMIT 20;

This provides strong data isolation between tenants and makes per-tenant backup, restore, or deletion straightforward. The trade-off is operational overhead — managing schema migrations across potentially hundreds or thousands of individual databases requires solid tooling and automation, and it doesn’t scale as cleanly as a shared-database, shared-schema, or shared-database-separate-schema approach for very large numbers of tenants. Which pattern makes sense depends heavily on your expected scale, isolation requirements, and how much operational complexity your team can comfortably manage.

Frequently Asked Questions

Can I change a database’s encoding after creation? No — encoding is fixed at creation time and can’t be altered afterward. To change it, you need to create a new database with the desired encoding and migrate the data over, typically using pg_dump and pg_restore, or by exporting and re-importing the data.

What’s the difference between template0 and template1? template1 is the default template and can be customized (as shown above) — any changes you make to it appear in future databases. template0 is a pristine, unmodified template meant to stay untouched, and is mainly used as a base when you need to specify settings (like encoding or locale) that would conflict with whatever customizations exist in template1.

How many databases can one PostgreSQL server hold? There’s no hard built-in limit on the number of databases, though practical limits come from disk space, filesystem constraints on the number of files, and the overhead of managing many databases operationally. Most real-world deployments run anywhere from a handful to a few hundred databases per server comfortably.

Do I need superuser privileges to create a database? No — any role granted the CREATEDB privilege can create databases, without needing full superuser access. This is the recommended approach for giving developers or applications the ability to create databases without granting them broader administrative control over the server.

Wrapping Up

Creating a database in PostgreSQL takes one line of SQL, but the optional parameters around encoding, locale, ownership, and connection limits give you real control over how that database behaves from day one. Getting these settings right at creation time — especially encoding and locale — saves you from painful migrations later, since some of these properties can’t be changed after the fact without recreating the database entirely.

With a database in place, the next logical step is defining the tables that will actually hold your data, which is exactly what I cover next in this series.