How to Create Database in PostgreSQL

A web application starts with a database owned by its administrator account. That seems convenient until a deployment changes the wrong objects, an application bug has permission to delete tables, and nobody knows whether the latest backup can be restored.

Learning how to create database in PostgreSQL involves more than running CREATE DATABASE. Ownership, application permissions, connection security, and recovery settings determine whether that database remains manageable after the first release.

This tutorial walks through creating an order-management database, separating ownership from application access, adding tables, validating permissions, and planning monitoring and recovery for self-managed PostgreSQL or Azure Database for PostgreSQL Flexible Server.

Table of Contents

How to Create Database in PostgreSQL

1. Define the Database and Deployment Requirements

PostgreSQL separates several concepts:

A server instance manages databases and shared roles.
A database contains application objects and data.
A schema is a namespace inside a database.
A role represents an identity or a group of privileges.

Creating a database does not install PostgreSQL or provision an Azure server. A running server must already exist.

For this example, use shopdb for an order-processing application and app as its schema. Keep administrative, deployment, and runtime responsibilities separate.

Choose self-managed PostgreSQL or Flexible Server

Option	Appropriate when	Your operational responsibility
Self-managed PostgreSQL	You need operating-system control or unsupported extensions	Patching, certificates, backups, monitoring, storage, and recovery
Azure PostgreSQL Flexible Server	You prefer managed infrastructure and integrated Azure operations	Application design, permissions, networking, sizing, and recovery testing

For local installation, follow the relevant PostgreSQL installation on Windows or PostgreSQL installation on Linux guide.

For Azure, use Flexible Server for new deployments. Choose a supported PostgreSQL version and verify required extensions before provisioning.

Burstable compute suits intermittent development workloads. General Purpose is a starting point for sustained application traffic. Memory Optimized targets workloads that benefit from more memory. Validate the choice with representative tests.

Pro Tip: I separate “create the server” from “create the database” in deployment plans. They have different permissions, costs, and recovery responsibilities.

2. Prepare Networking and a Verified TLS Connection

For self-managed PostgreSQL, configure approved listening interfaces, operating-system firewall rules, and pg_hba.conf, the client authentication rules file.

Prefer narrowly scoped hostssl rules with appropriate authentication. Do not enable broad remote access merely to resolve a connection problem.

For Azure, prefer private connectivity. Flexible Server supports private access through virtual network integration and supported Private Link configurations. Choose the networking model deliberately rather than treating the two as interchangeable.

A virtual network provides private addressing and connectivity. Private DNS must resolve the database hostname correctly from application and administration networks.

If temporary public access is necessary, allow only approved source addresses. Never open the entire IPv4 range.

Connect with psql

psql is PostgreSQL’s command-line client:

psql "host=<database-hostname> port=5432 dbname=postgres user=<deployment-role> sslmode=verify-full sslrootcert=<trusted-ca-file>" \
  --set=ON_ERROR_STOP=on

The hostname identifies the server, while dbname=postgres selects an existing maintenance database. The deployment role needs database-creation and role-management permissions for the following steps.

sslmode=verify-full requires encryption, certificate-chain validation, and hostname verification. The trusted CA file must contain the appropriate trusted certificate authority. ON_ERROR_STOP stops script processing after an error.

Verify the session:

SELECT
    current_user,
    current_database();

SELECT
    ssl,
    version,
    cipher
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

The first statement confirms identity and database. The second reports session encryption; ssl should be true. That server-side result does not independently prove client hostname validation.

See connecting to a PostgreSQL database for connection fundamentals.

Pro Tip: I verify DNS, certificate trust, and role identity before creating objects. Otherwise, a successful deployment can still target the wrong server.

3. How to Create Database in PostgreSQL With an Owner Role

The simplest database command is:

CREATE DATABASE shopdb;

After running the above query, the database has been created succcessfully as shown in the screenshot below.

It creates shopdb with the executing role as owner. For an application, a dedicated non-login owner provides a clearer boundary.

Use the following sequence instead of running both creation examples:

CREATE ROLE shop_owner NOLOGIN;

CREATE ROLE shop_runtime NOLOGIN;

GRANT shop_owner TO CURRENT_USER;

CREATE DATABASE shopdb
    OWNER shop_owner
    TEMPLATE template0
    ENCODING 'UTF8';

After running the above query, the database has been created succcessfully as shown in the screenshot below.

how to create database in postgresql pgadmin

shop_owner owns application objects but cannot log in directly. shop_runtime groups application privileges.

The membership grant allows the deployment identity to assume the owner role. Execute it through an authorized administrator; do not give runtime identities this membership.

template0 provides a clean database template without site-specific additions from template1. UTF8 supports international text. Locale settings still influence sorting and comparisons; choose and test them explicitly if inherited defaults are unsuitable.

Warning: Database creation consumes storage and creates an additional object to govern and back up.

CREATE DATABASE must run outside a transaction block. Do not place it inside BEGIN, a migration transaction, or a procedural loop. PostgreSQL also requires the appropriate creation privilege and authorization to assign the specified owner.

Reconnect to shopdb using the previous psql command with dbname=shopdb.

Confirm ownership:

SELECT
    datname,
    pg_get_userbyid(datdba) AS owner
FROM pg_database
WHERE datname = 'shopdb';

After running the above query, I got the expected output as shown in the screenshot below.

how to create database in postgresql using psql

For additional discovery options, review listing PostgreSQL databases.

Pro Tip: I keep database provisioning outside transactional schema migrations. That avoids the common failure where a migration runner wraps CREATE DATABASE in a transaction.

4. Configure Database, Schema, and Future-Object Permissions

Run the following in shopdb as the authorized deployment identity:

SET ROLE shop_owner;

REVOKE ALL ON DATABASE shopdb FROM PUBLIC;
GRANT CONNECT ON DATABASE shopdb TO shop_runtime;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

CREATE SCHEMA app AUTHORIZATION shop_owner;

GRANT USAGE ON SCHEMA app TO shop_runtime;

ALTER DEFAULT PRIVILEGES
    FOR ROLE shop_owner
    IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE
    ON TABLES TO shop_runtime;

ALTER DEFAULT PRIVILEGES
    FOR ROLE shop_owner
    IN SCHEMA app
    GRANT USAGE
    ON SEQUENCES TO shop_runtime;

SET ROLE makes subsequent objects belong to shop_owner.

PUBLIC means every role. Revoking its database privileges removes default connection and temporary-object access for ordinary roles; it does not prevent owner or superuser access.

CONNECT permits database connection. Schema USAGE permits access to objects within that namespace when object-level privileges also allow it.

Default privileges apply to future objects created by the specified owner. They do not repair permissions on existing tables or objects created by another identity.

The application receives no table-creation, ownership, or deletion privileges. It receives only the operations required by this example.

Review PostgreSQL schemas when designing namespaces and object access.

Pro Tip: I create schema objects under the same owner role every time. Default privileges become unreliable when deployments switch between unrelated object owners.

5. Create Related Tables and Load Test Data

Continue as shop_owner:

CREATE TABLE app.customers
(
    customer_id bigint GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    email text NOT NULL UNIQUE,
    customer_name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE app.orders
(
    order_id bigint GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    customer_id bigint NOT NULL
        REFERENCES app.customers(customer_id),
    total_amount numeric(12, 2) NOT NULL
        CHECK (total_amount >= 0),
    status text NOT NULL DEFAULT 'new'
        CHECK (status IN ('new', 'paid', 'shipped')),
    created_at timestamptz NOT NULL DEFAULT now()
);

Identity columns generate identifiers. Primary keys enforce uniqueness, while the foreign key prevents orders from referencing nonexistent customers.

numeric(12, 2) stores exact monetary amounts. timestamptz represents an instant and displays it using the session time zone.

The email constraint enforces exact-value uniqueness; case-insensitive identity rules need an additional design decision.

Use relational columns for required relationships and searchable business fields. JSONB, PostgreSQL’s binary JSON representation, can hold flexible attributes, but it should not replace essential constraints.

Load one customer and order atomically:

WITH new_customer AS
(
    INSERT INTO app.customers (email, customer_name)
    VALUES ('alex@example.test', 'Alex Morgan')
    RETURNING customer_id
)
INSERT INTO app.orders (customer_id, total_amount)
SELECT customer_id, 149.95
FROM new_customer
RETURNING order_id, customer_id, total_amount;

RETURNING passes the generated identifier into the order insertion. The single statement succeeds or fails together.

For larger examples, see inserting multiple PostgreSQL rows.

Pro Tip: I avoid assuming identity values start at 1. Using RETURNING keeps examples correct when test databases already contain data.

6. Add a Restricted Application Login and Test It

For a password-authenticated development environment, return to the deployment identity and create a restricted login:

RESET ROLE;

CREATE ROLE shop_app
    LOGIN
    INHERIT
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION;

GRANT shop_runtime TO shop_app;

The login inherits runtime privileges but does not own objects or administer the server.

Set its password interactively rather than embedding a secret in SQL:

psql "host=<database-hostname> port=5432 dbname=shopdb user=<deployment-role> sslmode=verify-full sslrootcert=<trusted-ca-file>" \
  --command='\password shop_app'

The \password command prompts for the new password without placing it directly in the command text.

For supported Azure applications, prefer Microsoft Entra authentication and managed identities. Configure the identity as a PostgreSQL principal, then grant the runtime role membership. Azure RBAC management access does not automatically provide SQL privileges.

Keep unavoidable secrets in Azure Key Vault or an approved secret manager. Never place passwords or credential-bearing connection strings in source code, spreadsheets, shared files, or unprotected application settings.

Reconnect as the application identity and test:

SELECT current_user;

SELECT order_id, total_amount, status
FROM app.orders;

SELECT
    has_database_privilege(
        current_user, 'shopdb', 'CREATE'
    ) AS can_create_schema,
    has_table_privilege(
        current_user, 'app.orders', 'DELETE'
    ) AS can_delete_orders;

The reads should succeed, and both privilege checks should return false.

Pro Tip: I validate application access in a fresh connection. Testing everything through the deployment session can hide missing or excessive privileges.

7. How to Create Database in PostgreSQL on Azure

The SQL workflow also applies after provisioning a Flexible Server.

In the Azure portal:

Select the subscription and resource group.
Choose Flexible Server and a supported region.
Select the PostgreSQL version, compute tier, and storage.
Configure authentication and private networking.
Set backup retention and appropriate high availability.
Review costs before creating the server.

A subscription provides a billing and governance boundary. A resource group organizes related resources.

Warning: Provisioning starts billable resources. High availability adds infrastructure and cost.

For sustained production workloads, evaluate General Purpose before selecting Burstable solely for price. Consider Memory Optimized only when workload evidence supports it.

Availability zones are separate datacenter locations within a supported region. Zone-redundant high availability reduces exposure to a zone failure, but it does not replace backups or application reconnection testing.

Check regional capacity, version support, extension availability, and network prerequisites before committing to a design.

After provisioning, use the server’s actual hostname and an authorized database deployment identity. Managed administrators are not unrestricted PostgreSQL superusers; use supported service mechanisms rather than assuming operating-system access.

Pro Tip: I settle networking and recovery requirements before sizing compute. Those decisions are harder to repair than changing a development server’s capacity.

8. Tune Queries and Maintain Healthy Connections

For a customer’s recent orders, test this index:

CREATE INDEX ix_orders_customer_created
ON app.orders (customer_id, created_at DESC);

A B-tree index is an ordered access structure suitable for equality and range lookups. This index supports customer filtering and recent-order ordering.

Warning: Ordinary index creation can block writes and consumes storage. Use reviewed deployment procedures for populated production tables.

Measure the query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount, status
FROM app.orders
WHERE customer_id = 1
ORDER BY created_at DESC
LIMIT 20;

ANALYZE actually executes the statement. BUFFERS reports buffer activity. Small tables may appropriately use a sequential scan, so do not force an index based on a tiny sample.

Use PostgreSQL indexing guidance to evaluate read benefits against write overhead.

PostgreSQL uses MVCC, or multiversion concurrency control, to manage concurrent row versions. Autovacuum reclaims reusable space and maintains visibility information; ANALYZE updates planner statistics.

PgBouncer provides connection pooling, allowing application requests to reuse a controlled pool of database connections. Test transaction-pooling compatibility with session-dependent application behavior.

Pro Tip: I check connection counts and query plans before increasing memory. Oversized pools can overwhelm an otherwise correctly sized database.

9. Configure Monitoring, Backups, and Recovery Tests

Monitor CPU, storage, connection usage, long transactions, locks, and slow queries. For Flexible Server, use Azure Monitor metrics, alerts, and diagnostic logs.

Where supported, enable pg_stat_statements to summarize query execution. Consider pgaudit for sensitive activity, but scope auditing carefully to avoid excessive log volume.

A logical backup exports database objects and data:

pg_dump "host=<database-hostname> port=5432 dbname=shopdb user=<backup-role> sslmode=verify-full sslrootcert=<trusted-ca-file>" \
  --format=custom \
  --file=shopdb.backup

The custom format supports selective restoration through pg_restore. Use an authorized backup role and a compatible client version. Protect the backup because it contains application data.

Logical dumps do not automatically include cluster-wide roles. Preserve role definitions and deployment scripts separately.

For self-managed point-in-time recovery, plan base backups and continuous WAL archiving. WAL, or write-ahead logging, records changes needed for crash recovery and replay.

Flexible Server provides managed backups with a configurable 7–35-day retention window. Test point-in-time restore to a separate server, including networking, permissions, application validation, and reconnection.

Read replicas support read scaling but do not replace backups. Check geo-redundant backup availability and recovery requirements separately.

Follow a documented PostgreSQL backup process and restore into an isolated target before declaring success.

Pro Tip: I measure recovery time from application validation, not from the moment a restored server becomes available.

Frequently Asked Questions

What permissions are required to create a PostgreSQL database?

The executing role needs CREATEDB or equivalent administrative authority. Assigning another owner also requires authorization to assume that role.

Why does CREATE DATABASE fail inside a transaction?

PostgreSQL prohibits database creation inside a transaction block. Run it separately from transactional schema migrations and ensure the client is not wrapping it automatically.

Does PostgreSQL support CREATE DATABASE IF NOT EXISTS?

No. Check pg_database through deployment tooling and handle an already-existing database safely. Do not delete an existing database to make provisioning repeatable.

How do I fix permission denied for schema app?

Confirm the role has schema USAGE and the required table privileges. For generated identifiers, sequence privileges may also matter; database CONNECT alone is insufficient.

Is a successful backup enough for production readiness?

No. Restore into a separate environment and verify application behavior, permissions, and recovery time. A backup file that has never been restored is unproven protection.

You can now create a PostgreSQL database with deliberate ownership, restricted application access, related tables, and a recovery plan. Verify permissions, encrypted connectivity, and restore behavior before treating the database as production-ready. I hope you found this article helpful.