PostgreSQL installation on Linux step by step

In this PostgreSQL tutorial, we will learn about “PostgreSQL Installation on Linux” with how to solve the error when installing PostgreSQL. Additionally, we will delve into the steps to create a new user, and database, and grant privileges within PostgreSQL.

Step 1: Update the System Packages

Before installing any new software, it’s essential to update the system packages to ensure you have the latest versions and security patches. Open the terminal and run the following command:

sudo apt update

Step 2: Install PostgreSQL

You can start the installation of PostgreSQL after updating the system packages. Run the following command in the terminal:

sudo apt install postgresql

PostgreSQL and its dependencies will be downloaded and installed using this command.

Step 3: Switch to the PostgreSQL User

PostgreSQL by default creates a special user account called “postgres.” To communicate with the database system, we must switch to this user. Use the terminal to run the following command:

sudo su - postgres

Step 4: Access the PostgreSQL Prompt

The “postgres” user is now logged in, and we can get to the PostgreSQL prompt by executing the following command:

psql

When you enter the above command you get access to the PostgreSQL prompt, if you install the PostgreSQL correctly otherwise you get the error something like as shown in the below picture.

Your system’s PostgreSQL server may not be operating or may not be correctly configured, according to the error message you got.

To resolve the error start the PostgreSQL service by entering the following command in your main terminal.

sudo service postgresql start

If everything is fine, then you can proceed with Step 4: Access the PostgreSQL Prompt, if something is wrong then again you may get the error or message on your terminal something like this.

When you try to connect to PostgreSQL but no database clusters have been established or initialized on your system, the error message “No PostgreSQL clusters exist” frequently appears.

You can use the pg_createcluster command to create a new PostgreSQL cluster to fix this problem. Follow the below steps:

First, use the following command to determine which PostgreSQL versions are installed on your system:

pg_lsclusters

Assuming you have a specific PostgreSQL version installed (e.g., version 12), create a new cluster using the pg_createcluster command:

sudo pg_createcluster 12 main

With PostgreSQL version 12, a new cluster with the name “main” is created by this command. If you have a different version installed, replace out “12” with the desired version number.

Start the PostgreSQL service after the cluster has been created by executing:

sudo service postgresql start

If everything is fine, then you can proceed with Step 4: Access the PostgreSQL Prompt and enter the command.

psql

The above command launch the PostgreSQL prompt. Indicating that you have connected successfully to the recently established cluster.

Step 5: Create a New User

PostgreSQL has a role-based authentication system by default. For our application, a new user can be created. Run the following SQL query inside the PostgreSQL prompt (change “newuser” with the username you want to use):

CREATE USER newuser WITH PASSWORD 'password';

Step 6: Create a New Database

Let’s create a new database now, and then assign it to the new user. Run the following SQL query while still in the PostgreSQL prompt (change “newdb” with the name of the database you want to use):

CREATE DATABASE newdb OWNER newuser;

Step 7: Grant Privileges

We must grant the required privileges in order to enable the user to perform all operations on the newly created database. Execute the following SQL command while still in the PostgreSQL prompt:

GRANT ALL PRIVILEGES ON DATABASE newdb TO newuser;

The above command gives the user full access, including the ability to create, modify, and remove objects. Replace the “newdb” and “newuser” with your database and user name respectively.

Step 8: Exit the PostgreSQL Prompt

After executing the SQL commands, type the following to exit the PostgreSQL prompt:

\q

By executing the following command, you can get back to your default user account:

exit

You should have PostgreSQL installed on your Linux system after following these steps. The newly created user and database are now available for usage with your application.

Conclusion

In this PostgreSQL tutorial, we have discussed how to install PostgreSQL on Linux systems by solving the common error. Then discussed how to create a new user, and database and grant all privileged to the user, at the end we learn how to exit from the PostgreSQL prompt.

You may like to read the following articles:

• What is PostgreSQL [Features & Disadvantages]
• How to Connect PostgreSQL Databases on Linux