If you're looking to install and use PostgreSQL on your Ubuntu Server, this guide is for you. Here I am going to walk you through the process of installing PostgreSQL, creating a new user with full access rights, and creating and accessing a new database.

Step 1: Installing PostgreSQL

Before we begin, it's important to note that PostgreSQL is not installed by default on Ubuntu Server. To install it, we'll run the following command:

“sudo apt-get update && sudo apt-get install postgresql”

This command updates the Ubuntu package list and installs PostgreSQL. Alternatively, you can run the commands separately by running “sudo apt-get update” followed by “sudo apt-get install postgresql”.

Step 2: Checking the Status of PostgreSQL

Once PostgreSQL is installed, you can check its status by running the command “sudo systemctl status postgresql”

If PostgreSQL is running, you'll see a message indicating that the service is active.

Step 3: Connecting to PostgreSQL

Okay great! Postgres is running. By default, Postgres comes with a default user named "postgres". You can switch the user on you ubuntu terminal using the command “sudo -u postgres -i".

Now we can connect to postgres terminal using the command “psql”.

Remember, you cannot access the postgres terminal using your default user on you ubuntu server because there is not any role defined in postgres.

You can run the command “psql” to access postgres terminal with your username and you will see an connection error message

For now we will use the “postgres” user to access the Postgres terminal using the command “sudo -u postgres -i" and then “psql” command to access the Postgres terminal;

Step 4: User Management in Postgres

Okay now, lets see how many user exists in postgres. Type “\du” to list databases users

We can see that only “postgres” user is create so that was the reason we were not able to login using our existing user on ubuntu server.  If you have noticed, we logged on the psql terminal using “postgres” user without any password.

We’re going to fix by setting our own password by typing the command “\password postgres”.

Now password will prompt everything when we try to login using “postgres” user. You can also create new user in Postgres using the command “CREATE USER username WITH PASSWORD password;” and give all permissions with command “GRANT ALL PRIVILEGES ON DATABASE databasename TO username;”.

Well granting the access won’t work since we haven’t created any database yet.

Step 5: Creating a New Database

Lets first list the databases using the command “\l” to see if any databases already exists;

If you see the first row, postgres, template0, and template1 are default databases that are created automatically during the installation process. The postgres database is the default database where the postgres user can connect and create new databases.

The template0 and template1 databases are used as templates for creating new databases. The template1 database is a copy of the template0 database, and can be modified if necessary to create custom database templates.

It is recommended that you do not modify the postgres, template0, and template1 databases directly. Instead, you can create new databases using CREATE DATABASE command or by using a custom template based on template0 or template1.

Than been said, let us now create our very own database. I will create a database with name “eirajamin” using the command “CREATE DATABASE eirajamin;”

Alright, once the database is created, now it’s time to grant all privileges rights to the default user “postgres” using the command “GRANT ALL PRIVILEGES ON DATABASE eirajamin name  TO postgres;”

If you remember, that we were not able to run the command earlier for granting all privileges rights. You can now do this through this command.  Now lets see if the database is created using “\l” command

Woohoo!, we have our very first database created. Now lets connect to our new database “eirajamin” using the command “\c eirajamin”

Congratulations, you have learnt how to install Postgres on the ubuntu machine, create user, change the password, creating the database and assign privilege rights to the users.

You can exit the Postgres terminal using the command “\q” and then “exit” to go back to your ubuntu terminal.