Step-by-Step Guide

1. Update the Package Index
   Open a terminal and update the package index to ensure you have the latest information about available packages:

   sudo apt update

2. Install PostgreSQL
   Install PostgreSQL using the apt package manager:

   sudo apt install postgresql postgresql-contrib

3. Verify PostgreSQL Installation
   Check the PostgreSQL service status to ensure it is running:

   sudo systemctl status postgresql

If PostgreSQL is running, you should see an active (running) status.

4. Log In to PostgreSQL
   Switch to the postgres user and access the PostgreSQL prompt:

   sudo -i -u postgres
   psql

5. Set Up a New PostgreSQL Role
   To create a new PostgreSQL role, use the following SQL command within the PostgreSQL prompt:

   CREATE USER your_username WITH PASSWORD 'your_password';

6. Create a New Database
   To create a new database owned by the new user, use the following SQL command:

   CREATE DATABASE your_database_name OWNER your_username;

7. Grant Privileges (Optional)
   To grant all privileges on the database to the new user, use the following SQL command:

   GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;

8. Exit PostgreSQL Prompt
   Exit the PostgreSQL prompt by typing:

   \q

9. Test PostgreSQL Connection
   To test the connection, switch back to your regular user account and try connecting to the new database:

   psql -U your_username -d your_database_name

You will be prompted to enter the password for the new user. Once logged in, you can list the tables to ensure the connection is successful:

   \dt

10. Backup and Restore Databases (Optional)
    To back up a database, use the pg_dump command:
    bash pg_dump -U your_username your_database_name > your_database_name.sql
    To restore a database from a backup file, use the psql command:
    bash psql -U your_username -d your_database_name < your_database_name.sql