3 Ways to Create a Database in PostgreSQL: A Step-by-Step Guide

Creating a database is one of the foundational tasks in PostgreSQL administration.

Whether you’re setting up a database for a small application, enterprise workload, or testing environment, PostgreSQL offers flexible, secure methods to create and configure databases—via the command line (psql/createdb), graphical tools (pgAdmin), or even programmatic APIs.

This guide covers all core methods to create a PostgreSQL database, along with advanced configuration, permission management, troubleshooting, and industry best practices.

Prerequisites

Before creating a database, ensure you meet these requirements to avoid permission or access errors:

1. Superuser or Database-Creation Privileges: Only users with the CREATEDB role (or superusers like the default postgres user) can create databases. Verify your privileges with: -- Check if your user has CREATEDB privilege SELECT usename, usesuper, createdb FROM pg_user WHERE usename = 'your_username';
2. PostgreSQL Service Running: Confirm the PostgreSQL daemon is active (Linux/macOS example): sudo systemctl status postgresql # Should show "active (running)"
3. Access to a Client Tool: Choose one of these tools (we’ll focus on psql and createdb for universality):
   • psql: PostgreSQL’s official command-line interface (CLI).
   • createdb: A standalone terminal command for quick database creation.
   • pgAdmin: A graphical user interface (GUI) for beginners.
   • Client libraries (e.g., psycopg2 for Python, pgx for Go) for programmatic creation.

Core Methods to Create a PostgreSQL Database

PostgreSQL offers three primary ways to create a database, tailored to different use cases (CLI for automation, GUI for simplicity).

Method 1: Create a Database via psql (Interactive CLI)

The psql shell is the most flexible method, as it lets you run the CREATE DATABASE SQL command with full configuration options.

Step 1: Connect to PostgreSQL as a Superuser

First, log into psql with a user that has database-creation privileges (e.g., the default postgres superuser):

psql -U postgres  # Connects to the default "postgres" database as the "postgres" user

You’ll see a prompt like postgres=# (the # indicates superuser access).

Step 2: Basic CREATE DATABASE Syntax

The minimal syntax to create a database is straightforward:

-- Basic syntax: CREATE DATABASE [database_name];
CREATE DATABASE test_db;

This creates a database named test_db with default settings (e.g., UTF8 encoding, default table space, postgres as the owner).

See also: Mastering the Linux Command Line — Your Complete Free Training Guide

Step 3: Verify the Database Creation

Confirm the database exists with the \\\\l (list databases) meta-command:

postgres=# \\\\l
                                  List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | en_US   | en_US |
 template0 | postgres | UTF8     | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 test_db   | postgres | UTF8     | en_US   | en_US |
(4 rows)

Method 2: Create a Database via createdb (Terminal Command)

The createdb utility is a standalone terminal command (no need to enter psql) for quick database creation—ideal for scripts or automation.

Basic Syntax

# Core syntax: createdb -U [user] [database_name]
createdb -U postgres test_db

Key Flags for Customization

Add flags to configure the database directly from the terminal:

# Create a database with a specific owner, encoding, and connection limit
createdb -U postgres \\\\
  -O test_user \\\\          # Set owner to "test_user"
  -E UTF8 \\\\               # Set encoding to UTF8
  -l en_US.UTF-8 \\\\        # Set locale
  -c connection_limit=10 \\\\# Limit concurrent connections to 10
  test_db

Method 3: Create a Database via pgAdmin (GUI)

pgAdmin is a user-friendly GUI for PostgreSQL, perfect for beginners who prefer point-and-click workflows:

1. Launch pgAdmin and connect to your PostgreSQL server (enter the server name, port, and superuser credentials).
2. Right-click on Databases in the left sidebar → select Create → Database.
3. In the General tab:
   • Enter a Database name (e.g., test_db).
   • Select an Owner (e.g., postgres or test_user).
4. (Optional) Configure advanced settings (encoding, collation, connection limits) in the Definition tab.
5. Click Save to create the database. The new database will appear under the Databases list.

Advanced Database Configuration

The basic CREATE DATABASE command uses default settings, but you can customize critical parameters to match your application’s needs:

Full CREATE DATABASE Syntax (Advanced)

CREATE DATABASE test_db
WITH
  OWNER = test_user                -- Assign a non-superuser as the owner
  ENCODING = 'UTF8'                -- Character encoding (always use UTF8 for modern apps)
  LC_COLLATE = 'en_US.UTF-8'       -- Collation (affects string sorting)
  LC_CTYPE = 'en_US.UTF-8'         -- Character type (affects case sensitivity, character classes)
  TABLESPACE = pg_default          -- Table space (use custom tablespaces for large datasets)
  CONNECTION LIMIT = 20            -- Max concurrent connections (0 = unlimited)
  IS_TEMPLATE = FALSE;             -- Prevent the database from being used as a template

Key Advanced Parameters Explained

Parameter	Purpose	Best Practices
OWNER	Assigns a database owner (defaults to the user running the command).	Use a dedicated application user (e.g., test_user) instead of postgres.
ENCODING	Defines character encoding for stored data.	Always use UTF8 to support multilingual data (avoid legacy encodings like LATIN1).
LC_COLLATE/LC_CTYPE	Controls string sorting and character classification (e.g., a vs A).	Match your application’s locale (e.g., en_US.UTF-8 for English, zh_CN.UTF-8 for Chinese).
TABLESPACE	Specifies the disk location for the database’s data files.	Use custom tablespaces for large databases (e.g., mount a separate disk for production data).
CONNECTION LIMIT	Prevents excessive connections from overwhelming the database.	Set a reasonable limit (e.g., 20–100) for application databases; leave as 0 for admin databases.

Manage Permissions for the New Database

Creating a database is only half the work—you must grant permissions to users so they can connect and interact with it. The most critical permissions are CONNECT (to access the database) and CREATE (to create tables/schemas).

Grant Basic Permissions

-- Grant CONNECT permission (required to access the database)
GRANT CONNECT ON DATABASE test_db TO test_user;

-- Grant CREATE permission (allows creating tables/schemas in the database)
GRANT CREATE ON DATABASE test_db TO test_user;

-- Grant all privileges (for admins only—avoid in production)
GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user;

Verify Permissions

Check if a user has access to the database with PostgreSQL’s built-in permission functions:

SELECT
  has_database_privilege('test_user', 'test_db', 'CONNECT') AS can_connect,
  has_database_privilege('test_user', 'test_db', 'CREATE') AS can_create_objects;

The result will return t (true) if the permission is granted, f (false) otherwise.

Verify & Connect to the New Database

1. List All Databases

Confirm the database exists with these commands:

-- In psql (detailed view with size/permissions)
\\\\l+ test_db

-- Via SQL (query system catalog)
SELECT datname, datowner, encoding FROM pg_database WHERE datname = 'test_db';

2. Connect to the New Database

Once created, connect to the database to start creating tables/schemas:

-- In psql
\\\\c test_db  -- Shortcut for \\\\connect test_db

-- From the terminal (direct connection)
psql -U test_user -d test_db -h localhost -p 5432

You’ll see a prompt like test_db=> (the => indicates a non-superuser connection).

Common Post-Creation Operations

Rename a Database

To rename a database (ensure no active connections first):

-- In psql (superuser required)
ALTER DATABASE test_db RENAME TO production_db;

Delete a Database

Warning: Deleting a database is irreversible—always back up data first!

-- In psql (superuser/owner required)
DROP DATABASE IF EXISTS test_db;  -- "IF EXISTS" avoids errors if the database doesn’t exist

-- From the terminal
dropdb -U postgres test_db

If you get an error like database "test_db" is being accessed by other users, terminate active connections first:

-- Terminate all connections to test_db (PostgreSQL 13+)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'test_db' AND pid <> pg_backend_pid();

Troubleshooting Common Errors When Creating Databases

Error 1: ERROR: permission denied to create database

Cause: The user running the command lacks the CREATEDB privilege or superuser status. Fix: Grant the CREATEDB role to the user (as a superuser):

ALTER ROLE test_user WITH CREATEDB;

Error 2: ERROR: database "test_db" already exists

Cause: A database with the same name is already present in the PostgreSQL instance. Fix: Either rename the new database or drop the existing one (if unused):

-- Option 1: Create a database with a unique name
CREATE DATABASE test_db_v2;

-- Option 2: Drop the existing database (backup first!)
DROP DATABASE IF EXISTS test_db;

Error 3: ERROR: invalid locale name: "zh_CN.UTF8"

Cause: The specified locale (e.g., zh_CN.UTF8) is not installed on your system. Fix: Install the missing locale and retry (Linux example):

# Install Chinese locale on Debian/Ubuntu
sudo locale-gen zh_CN.UTF-8
sudo update-locale

# Verify installed locales
locale -a | grep UTF-8

Error 4: ERROR: cannot rename database because it is being accessed by other users

Cause: Active connections to the database prevent renaming/deletion. Fix: Terminate connections first (see the “Delete a Database” section above) and retry.

Best Practices for Creating PostgreSQL Databases

Follow these industry-standard practices to ensure security, scalability, and maintainability:

1. Use the Principle of Least Privilege

• Never create databases as the postgres superuser for application workloads—create a dedicated user (e.g., app_user) with CREATEDB privileges instead.
• Assign database ownership to application-specific users (not superusers) to limit accidental damage.

2. Standardize Naming Conventions

• Use lowercase names with underscores (e.g., ecommerce_db, analytics_test_db)—PostgreSQL is case-insensitive by default, but lowercase avoids quoting issues.
• Avoid special characters (e.g., spaces, hyphens) or reserved words (e.g., user, table) for database names.

3. Enforce Consistent Encoding & Locale

• Always use UTF8 encoding to support multilingual data and avoid character corruption.
• Match the database’s locale (LC_COLLATE/LC_CTYPE) to your application’s requirements (e.g., en_US.UTF-8 for English apps, zh_CN.UTF-8 for Chinese apps).

4. Plan for Scalability with Tablespaces

• For large databases (100GB+), create custom tablespaces on separate storage volumes (e.g., SSDs for high-performance workloads, HDDs for archival data): -- Create a custom tablespace first CREATE TABLESPACE fast_storage LOCATION '/mnt/ssd/postgres_data'; -- Create a database using the custom tablespace CREATE DATABASE large_db WITH TABLESPACE = fast_storage;

5. Automate Database Creation (for Dev/Prod Workflows)

• Use scripts (e.g., Bash, Python) with createdb or CREATE DATABASE to standardize database creation across environments (dev, staging, production).
• Example Bash script for automated creation: #!/bin/bash # create_test_db.sh DB_NAME="test_db" DB_USER="test_user" # Create database with custom settings createdb -U postgres -O $DB_USER -E UTF8 -l en_US.UTF-8 $DB_NAME # Grant permissions psql -U postgres -c "GRANT CONNECT, CREATE ON DATABASE $DB_NAME TO $DB_USER;" echo "Database $DB_NAME created successfully for user $DB_USER"

6. Back Up Databases Immediately

• After creating a database (especially for production), take a baseline backup with pg_dump: pg_dump -U postgres -d test_db -f test_db_baseline_backup.sql

Conclusion

Creating a database in PostgreSQL is a straightforward task, but attention to configuration, permissions, and best practices ensures your database is secure, scalable, and aligned with your application’s needs. Key takeaways:

• Use psql (for full control) or createdb (for automation) to create databases via the CLI; use pgAdmin for a GUI workflow.
• Customize databases with OWNER, ENCODING, and TABLESPACE to match your workload requirements.
• Grant minimal permissions to users (only CONNECT/CREATE as needed) and avoid superuser access for application databases.
• Troubleshoot common errors (permission denied, duplicate databases) by verifying privileges and terminating active connections.

By following this guide, you can create PostgreSQL databases that are secure, maintainable, and ready to support everything from small apps to enterprise-grade workloads.