How To Install PostgreSQL on Ubuntu 22.04

In this guide, we will explain how to install PostgreSQL on Ubuntu 22.04, enabling you to set up a powerful relational database system for your projects.

PostgreSQL is one of the leading and most widely used open-source relational database management systems it is a robust and high-performance database system known for its flexibility in handling multiple data types stability, integrity, and concurrency.

Prerequisites

Make sure you have the necessary prerequisites to successfully install Postgresql on ubuntu 22.04.

• Launch the Ubuntu 22.04 instance on Neevcloud

• Access the server using SSH

Update && Upgrade the system

First, ensure your package list is up to date to avoid any issues with outdated packages

sudo apt-get update -y
sudo apt-get upgrade -y

Install PostgreSQL

To install PostgreSQL use the apt package manage and run the following command from a terminal prompt:

sudo apt install postgresql postgresql-contrib -y

Start and Enable PostgreSQL

After installation, the PostgreSQL service starts and enables

Once the PostgreSQL is installed, start the PostgreSQL server

Once the MariaDB is installed, start the MariaDB server

sudo systemctl start postgresql

Enable it to start when the system rebooted

sudo systemctl enable postgresql

To check the status

Verify that the database service is active and running. Run the following command

sudo systemctl status postgresql

To check the version

Verify the version installed by running the command:

sudo psql --version

Manage PostgreSQL service

PostgreSQL runs quietly in the background upon installation. By default, it listens on TCP port 5432. You can verify this using the ss command.

sudo ss -antpl  | grep 5432

Access PostgreSQL Prompt

When PostgreSQL is installed, a standard user account named Postgres is automatically created. The Postgres user takes on the default Postgres role as well.

PostgreSQL uses a role-based authentication system as its default. Having superuser privileges, the user 'Postgres' has complete administrative control. Enter this command to log in as the user 'Postgres'

sudo su - postgres

Then, to access the PostgreSQL prompt, type

psql

From here, you can start executing your database management tasks.

To leave the prompt, type \q, and exiting the prompt will return you to your Postgres account in your terminal. Execute the exit command to return to your normal account and leave.

\q

exit

Create a New Database

This section will cover the process of establishing a database in PostgreSQL and generating tables within it. The standard PostgreSQL installation includes three pre-existing databases: Postgres, template0, and template1.

To list the existing databases to run these commands.

\l

To create a database, run the create database database_name; command

create database example_db;

Create a New Table

In order to make a new table and add data, you must first change to the desired database where the table will be stored. Use the \c command to switch the database.

\c 

After transitioning to the database, you can begin to create tables. The syntax for a table in SQL is as follows

In this instance, we will generate a table named employees that includes six specific columns

CREATE TABLE employees (
	Person_ID serial PRIMARY KEY,
	First_Name VARCHAR ( 50 ) NOT NULL,
	Last_Name VARCHAR ( 50 ) NOT NULL,
	Email VARCHAR ( 255 ) UNIQUE NOT NULL,
           City VARCHAR ( 50 ) NOT NULL,
           age INT NOT NULL
);

To check the status of creating a table in a database to follow these \dt command

You can also use the \d table_name syntax to view the table schema.

\dt

To insert the values into the table, use the the INSERT INTO table_name command.

INSERT INTO employees 
VALUES ( 001, 'Phil', 'Shaw', '[e-mail]', 'New York', 40);

Now check the status of table records to run these SELECT * FROM table_name command

SELECT * FROM employees;