How to set up “POSTGRESQL” in ubuntu 20.04 , 22.04 or 22.10

Hey linux knights,

today I am going set up postgresql for my database in my project .If you know that postgres is open source relational database management system (RDBMS) that uses and extends the SQL language to store and manage Data. It was open source developed at the University of California,Berkely. It is used by many companies and organizations .Now it is maintained by PostgreSQL Global Development Group. PostgreSQL provides many features, such as support for ACID (Atomicity, Consistency, Isolation, Durability) transactions, advanced data types, extensibility through custom functions and procedures, and the ability to scale horizontally by clustering multiple servers together. It is known for its stability, robustness, and performance, and is widely used in production environments by both small and large organizations.

Now we are going to step forward to install .

if you do not have installed “nala” then I will recommend you to install it first.

first we have to update our system core packages by below commands. open your gnome terminal by pressing Ctrl+Alt+T and type these commands.

sudo nala update
sudo nala upgrade

Add Official Repository

you can donwload postgres from official repository , you will get more fequent update than official ubuntu repository . First, you should install prerequisite software packages that will be used to download and install software certificates for a secure SSL connection.

sudo apt install wget ca-certificates

the, we have to get a certificates, add it to api key management . utility and and create new configuration file with an official postgresSQL repository address inside .

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Then step forward to install postgresql

Postgresql install

sudo nala update 
sudo nala upgrade 

now we are going to do actual postgres install .This will install the latest PostgreSQL version along with the newest extensions and additions that are not yet officially part of the PostgreSQL core.

sudo apt install postgresql postgresql-contrib

Check Postgresql Status

Now we are going to check out pqsql status wheater it’s active or not .

service postgresql status

You find a output like this with green active word .

Start Using PostgreSQL via command line

when we install postgresql by default it create a admin user for us called “postgres” also this craete a default database called “postgres” .When you start psql by default pgsql connects us to default database called “postgres” .

for starting postgresql in ubuntu just type thsi command on your gnome terminal

sudo -u postgres psql

you will get a output like this .

if you wants to know about our connection info just type “conninfo”

postgres=# conninfo

you will get a output like this

we are now connected to database “postgres” as user “postgres”.

If we want to see a list of all the databases that are available on a server, use l command.

postgres=# l

And to see a list of all the users with their privileges use du command.

Since the default “postgres” user does not have a password, you should set it yourself.

As I am not a new user or fresh installer so do not have to do this again .

to change password

postgres=# password postgres

then just type new password then enter

Create new database

You are now connected to your database server through psql command line tool with full access rights, so it’s time to create a new database.

postgres=# CREATE DATABASE star;

After the new “star” database is created, connect to it.

postgres=# c star

Now you are ready to start creating tables where your data will be stored. Let’s create your first table with a primary key, and three client attributes.

postgres=# CREATE TABLE clients (id SERIAL PRIMARY KEY, first_name VARCHAR, last_name VARCHAR, role VARCHAR);

You may double check that your new table is created successfully by typing a dt command.

Let’s now insert the first row into your newly created “clients” table.

INSERT INTO clients (first_name, last_name, role) VALUES ('John', 'Smith', 'CEO');

And query the table to get all its rows.

SELECT * FROM clients;

As you can see, John Smith has been successfully added to the “clients” table of the “star” database.