Create new Database in Postgres

Pre-requisites

• You have PostgreSQL installed in your system.

• You have root privileges

Logging into psql as root

Open your terminal and Enter the following commands -
sudo -i -u postgres
Enter your password and you should login into something like this -

Enter psql and press enter. You have entered the psql terminal.

Creating the database

First, you need to have a user with which you will login to the database.
CREATE USER new_username WITH ENCRYPTED PASSWORD 'your_secure_password';
Once the user is created, we will create the database -
CREATE DATABASE database_name;
Now we will grant the required permissions -
GRANT ALL PRIVILEGES ON DATABASE database_name TO new_username;

Press \q to exit the psql terminal and exit the postgres session.

Verify login

Run the below command to verify whether you are able to login to the database -
psql -U test_user my_blog_db_test
We get an error when we run this -
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "test_user"

Fix

This error arises because postgres is set to use peer authentication by default. We need to change this to md5 in /etc/postgresql/14/main/pg_hba.conf

• Open /etc/postgresql/14/main/pg_hba.conf in the editor of your choice.

• Search for local all postgres peer in the file

• Add this line below the postgres config - local all test_user md5

• Restart the postgres service. sudo systemctl restart postgresql

Login to postgres - psql -U test_user my_blog_db_test and enter the password when prompted.

Congratulations! You have successfully created your database.