How to install PostgreSQL on Ubuntu

PostgreSQL (sometimes known as Postgres) is a relational database management system that implements the SQL querying language. It complies with industry standards and has numerous advanced features such as dependable transactions and concurrency without read locks. The PostgreSQL project began in 1986 at the University of California’s Berkeley Computer Science Department. The project was previously known as POSTGRES, after the Ingres database, which was also created at Berkeley. The POSTGRES project’s purpose was to add the bare minimum of capabilities required to enable multiple data types. The POSTGRES project was renamed to PostgreSQL in 1996 to emphasize its SQL capabilities. Installing PostgreSQL is crucial to building dynamic apps and websites.

PostgreSQL is now usually referred to as Postgres. The PostgreSQL Global Development Group, a resolute community of contributors, has consistently provided the open-source and free database project since then. PostgreSQL was initially created with the intention of running on UNIX-like platforms. PostgreSQL was later developed to work on a variety of systems, including Windows, macOS, and Solaris. From installing PostgreSQL to creating a new user and database, this guide shows how to install PostgreSQL on Ubuntu 20.04 server database. You can find other guides here: How to Install Apache OpenOffice on Ubuntu /, How to install Apache Tomcat on Ubuntu/, How to install and configure Tripwire on Ubuntu/and How to install Node.js on Ubuntu/ also How to Install and Configure Nagios on Ubuntu/ and How to Install LAMP Stack on Ubuntu 18.04

Ubuntu Versions That Supports PostgreSQL Installation

You can install PostgreSQL on the following Ubuntu versions:

• impish (21.10)
• hirsute (21.04)
• focal (20.04)
• bionic (18.04)

Advantage: Postgres is free and open source. 
Reduce your expenses. PostgreSQL is a true open-source solution, hence there are no license payments
Community-driven various businesses and individuals have contributed to the project, which has fueled innovation. Bugs are quickly fixed because of the large community.
Security Because of the easy extensibility, there are numerous features for better security: nevertheless, if you use the appropriate ones (TDE, Data Masking), you obtain a very safe database that protects your most significant asset - your data.
Scalable PostgreSQL database can expand to meet your needs. There are a variety of technological options for scaling PostgreSQL. As a result, your PostgreSQL database will be able to scale up to meet your needs.

From installing PostgreSQL to creating a new user and database, this guide shows how to rapidly get Postgres up and running on an Ubuntu 20.04 server database

Prerequisites for Installing PostgreSQL on Ubuntu

Install rudimentary firewall and non-root user with sudo capabilities on your server

We used version 20.04 of Ubuntu, but the steps should work for other versions listed above. We shall be using the apt repository and the steps are as shown below

Step1: create a file repo

 http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Step2: Import the repository signing key

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Step3: update the package list

sudo apt update 

Step4: Install the latest version of PostgreSQL on Ubuntu

To install PostgreSQL, run the command below:

sudo apt-get -y install postgresql

Included items in distribution

PostgreSQL comes pre-installed on Ubuntu. Use apt-get (or another apt-driving command) to install PostgreSQL on Ubuntu:

Many distinct The most frequent and relevant packages are:

Step 5: Using PostgreSQL Roles and Databases

Postgres employ ident authentication by default, which means that it associates Postgres roles with a Unix/Linux system account. If a role exists in Postgres, you can access it using a Unix/Linux username with the same name.

The default Postgres role correlates with a user account called postgres, generated during the installation process. You can access Postgres with this account in a few different ways. Switching to the postgres account on your server is one option: perform the following command:

sudo -i -u postgres

you can access postgres prompt with the command :

psql

Run the following commands to exit the PostgreSQL prompt:

\q

This redirects you to the postgres Linux command prompt. Furthermore, run the exit command to return to your regular system user:

exit

This will log you into Postgres without having to go through the bash shell.

You can leave the interactive Postgres session once more by typing the command:

\q

Step 6: Creating a New Role

 You can create a new role by running the following command:

createuser --interactive

If you’d rather use sudo for each command instead of switching to a different account, run:

sudo -u postgres createuser --interactive

Step 7: Creating a New Database After Installing PostgreSQL

When you install PostgreSQL, it activates a default database named PostgreSQL. A conclusion reached by default by the Postgres authentication system is that each role used to log in has access to a database with the same name.

This implies that if the user you created in the previous section is named Raphael, the role will automatically attempt to connect to a database named “Raphael.” You can use the createdb command to construct the necessary database.

You would type something like this if you were logged in as the postgres account:

createdb  Raphael

Possible createdb error

Solution

sudo -u postgres -i

After this let’s retry to create the database

root@ubuntu:/home/rdgmh# sudo -u postgres -i
postgres@ubuntu:~$ createdb Raphael
postgres@ubuntu:~$ 

The error did not show up anymore

Step 5 : Opening a Postgres Prompt with the New Role

You’ll need a Linux user with the same name as your Postgres role and database to log in with ident based authentication.

You can also use the adduser command to create a new Linux user if you don’t already have one. You’ll need to do this from a non-root account with sudo permissions (i.e., not as the postgres user):

sudo adduser postgresuser

You can switch over to this new account and connect to the database by running the following commands:

sudo -i -u postgresuser
psql

If you wish your user to connect to a separate database, you can accomplish so by naming it something like this:

psql -d postgres

Summary

In conclusion, we installed the latest version of PostgreSQL from the apt repository. Furthermore, we also walked you through how to create a user, work with postgres and exit the shell.