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. 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 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 rapidly get Postgres up and running on an Ubuntu 20.04 server database. Other guides can be found 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 Supported in this Tutorial

• 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

A non-root user with sudo capabilities and a rudimentary firewall should be installed 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.

sudo apt-get -y install postgresql

Included items in distribution

PostgreSQL is 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 is configured to employ ident authentication by default, which means that it associates Postgres roles with a Unix/Linux system account. If a role exists in Postgres, it can be accessed using a Unix/Linux username with the same name.

The default Postgres role is associated with a user account called postgres that was generated during the installation process. This account can be used to access Postgres 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

You’ll be returned to the postgres Linux command prompt as a result of this. 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

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.” The createdb command can be used 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 lets 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 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

We installed the latest version of Postgres from the apt repository, we also walked through how to create a user and how to work with postgres and how to exit the shell.