Creating a Database

PostgreSQL allows you to create databases using different methods, including the PostgreSQL interactive shell (psql), Docker (assuming PostgreSQL is running inside a container), and the command-line interface (createdb). This guide explains each method step-by-step, covering required permissions, best practices, and troubleshooting common issues.

Creating Using psql CLI

PostgreSQL is a database system that stores and manages structured data efficiently. The psql tool is an interactive command-line interface (CLI) that allows users to execute SQL commands directly on a PostgreSQL database. Follow these steps to create a database:

Connect to PostgreSQL

Open terminal on your local system, and if PostgreSQL is installed locally, connect using the following command. If not installed, install from official website:

psql -U postgres

For a remote database, use:

psql -h HOST -U USER -d DATABASE

Replace HOST with the database server address, USER with the PostgreSQL username, and DATABASE with an existing database name.

Create a New Database

Inside the psql shell, run:

CREATE DATABASE mydatabase;

The default settings will apply unless specified otherwise. To customize the encoding and collation, use:

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

Creating Database in Docker

Docker is a tool that helps run applications in isolated environments called containers. A PostgreSQL container provides a self-contained database instance that can be quickly deployed and managed. If you are running PostgreSQL inside a Docker container, follow these steps:

PullAccess Elestio Terminal

Head over to your deployed PostgreSQL service dashboard and head over to Tools > Terminal. Use the PostgreSQLcredentials Dockerprovided Imagethere to log in to your terminal.

As

Once you start,are make surein your docker is running correctly. Next, if you haven’t already downloaded the PostgreSQL image,terminal, run the following command andto ithead will take a whileover to pullthe correct directory to perform the imagenext successfully:steps

dockercd pull postgres

Start a PostgreSQL Container

Run a new PostgreSQL instance using Docker. This will run the docker image as a container using the given credentials, ports etc:

docker run --name my-postgres -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres/opt/app/

Access the PostgreSQL Container Shell

Inside

docker-compose exec postgres bash

will

Use executeEnvironment theVariables givento commandConnect via psql

Once inside the dockercontainer container:shell, if environment variables like POSTGRES_USER and POSTGRES_DB are already set in the stack, you can use them directly:

docker exec -it my-postgres psql -U admin"$POSTGRES_USER" -d "$POSTGRES_DB"

 

Or use the default one:

psql -U postgres

Create a Database

Inside the PostgreSQL shell, run following commandNow, to create a database, use the specificfollowing database:command. This command tells PostgreSQL to create a new logical database called mydatabase. By default, it inherits settings like encoding and collation from the template database (template1), unless specified otherwise.

CREATE DATABASE mydatabase;

You can quickly list the database you just created using the following command

/l

Creating Using createdb CLI 

The createdb command simplifies database creation from the terminal without using psql.

Ensure PostgreSQL is Running

Check the PostgreSQL service status, this ensureensures that the PostgreSQL instance is running on your local instance:

sudo systemctl status postgresql

If not running, start it:

sudo systemctl start postgresql

Create a Database

Now, you can create a simple database using the following command:

createdb -U postgres mydatabase

To specify encoding and collation:

createdb -U postgres --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 mydatabase

Verify Database Creation

List all databases using the following commandscommands, as it will list all the databases available under your postgres:PostgreSQL:

psql -U postgres -l

Connect to the New Database

NextNext, you can easily connect with the database using the psql command and startingstart working on it.

psql -U postgres -d mydatabase

Required Permissions for Database Creation

Creating a database requires the CREATEDB privilege. By default, the postgres user has this privilege. To grant it to another user:

ALTER USER username CREATEDB;

For restricted access, assign specific permissions:

CREATE ROLE newuser WITH LOGIN PASSWORD 'securepassword';
GRANT CONNECT ON DATABASE mydatabase TO newuser;
GRANT USAGE ON SCHEMA public TO newuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO newuser;

Best Practices for Creating Databases

• Use Meaningful Names: Choosing clear and descriptive names for databases helps in organization and maintenance. Avoid generic names like testdb or database1, as they do not indicate the database’s purpose. Instead, use names that reflect the type of data stored, such as customer_data or sales_records. Meaningful names make it easier for developers and administrators to understand the database’s function without extra documentation.
• Follow Naming Conventions: A standardized naming convention ensures consistency across projects and simplifies database management. PostgreSQL is case-sensitive, so using lowercase letters and underscores (e.g., order_details) is recommended to avoid unnecessary complexities. Avoid spaces and special characters in names, as they require additional quoting in SQL queries.
• Restrict User Permissions: Granting only the necessary permissions improves database security and reduces risks. By default, users should have the least privilege required for their tasks, such as read-only access for reporting tools. Superuser or administrative privileges should be limited to trusted users to prevent accidental or malicious changes. Using roles and groups simplifies permission management and ensures consistent access control.
• Enable Backups: Regular backups ensure data recovery in case of accidental deletions, hardware failures, or security breaches. PostgreSQL provides built-in tools like pg_dump for single-database backups and pg_basebackup for full-instance backups. Automating backups using cron jobs or scheduling them through a database management tool reduces the risk of data loss.
• Monitor Performance: Monitoring database performance helps identify bottlenecks, optimize queries, and ensure efficient resource utilization. PostgreSQL provides system views like pg_stat_activity and pg_stat_database to track query execution and database usage. Analyzing slow queries using EXPLAIN ANALYZE helps in indexing and optimization. 

SELECT datname, numbackends, xact_commit, blks_read FROM pg_stat_database;

Common Issues and Troubleshooting

Issue	Possible Cause	Solution
ERROR: permission denied to create database	User lacks CREATEDB privileges	Grant permission using ALTER USER username CREATEDB;
ERROR: database "mydatabase" already exists	Database name already taken	Use a different name or drop the existing one with DROP DATABASE mydatabase;
FATAL: database "mydatabase" does not exist	Attempting to connect to a non-existent database	Verify creation using \l
psql: could not connect to server	PostgreSQL is not running	Start PostgreSQL with sudo systemctl start postgresql
ERROR: role "username" does not exist	The specified user does not exist	Create the user with CREATE ROLE username WITH LOGIN PASSWORD 'password';