LinuxWizardry
  • Home
  • Tutorials
  • News
  • Ubuntu
  • Centos
  • Tools
No Result
View All Result
LinuxWizardry
  • Home
  • Tutorials
  • News
  • Ubuntu
  • Centos
  • Tools
No Result
View All Result
LinuxWizardry
No Result
View All Result

How to Install and Set up PostgreSQL Database on Ubuntu 22.04 LTS (Jammy Jellyfish)

How to Install and Set Up PostgreSQL Database on Ubuntu

by Rohan
May 28, 2022
in Tutorials
ShareTweetShareShare

Introduction

PostgreSQL is a free and open-source relational database management system that allows both SQL and JSON querying. With over 30 years of active development, it has gained a solid reputation for its reliability, data integrity, extensibility, ACID compliance, and robust features.

It allows you to create your own data types, define custom functions, and write different programming language codes without recompiling your database. It is compatible with all major operating systems including Windows, Linux/UNIX, Mac OS, IRIX, Solaris, etc.

This tutorial will teach you how to install and set up PostgreSQL on the Ubuntu system.

Prerequisites

For this tutorial, you will need a Ubuntu system having a non-root user with sudo privileges. The following step-by-step guide uses Ubuntu 20.04 system to demonstrate the installation and configuration of PostgreSQL on Ubuntu.

Step 1: Install PostgreSQL on Ubuntu

Before getting started, update your package information using the below command. It helps to get information about updated packages and their dependencies.

sudo apt update

Next, run this command to install the latest version of PostgreSQL along with a contrib package that provides additional tools and functionalities for PostgreSQL.

sudo apt install postgresql postgresql-contrib

Step 2: Check the PostgreSQL service

After completing the installation, the PostgreSQL service should be active by default. You can verify it using the following command.

service postgresql status

The output will be similar as shown in the image below.

check postgresql service status

Step 3: Connect to PostgreSQL Database

By default, PostgreSQL creates a user account postgres during the installation process. You have to log on to that account to connect and access the working shell of the PostgreSQL server.

sudo su - postgres

Then enter the following command to open the interactive Postgres shell.

psql

open postgresql shell on ubuntu
You can also open the Postgres shell without switching accounts. For example, this single command will launch an interactive Postgres shell as a postgres user.

sudo -u postgres psql

Similarly, PostgreSQL also creates a default database postgres during installation. The postgres user automatically connects to the database postgres when you run the psql command.

At the prompt, type the \conninfo command to check the details of the connection.

\conninfo

check connection details of postgresql
You can view a list of all databases by using the \l command.

\l

list all databases on a postgresql server
To exit the interactive interface, run the \q command.

\q

Step 4: Create a New Role

You can view a list of roles in PostgreSQL using the \du command.

\du

list all user roles on postgresql ubuntu
As you can see, there is only one role “postgres” which is the default role created during the installation process. It has superuser permissions and can create new roles and databases on the server.

The postgres user does not have any password, you have to set it yourself. For this, you have to run the below command at the prompt.

ALTER USER postgres WITH PASSWORD 'admin123';

It will set the password “admin123” for the postgres user.

set password for postgres user ubuntu
Multiple roles will provide you the flexibility of managing databases as different users.

You can create a new role using the createuser command. The --interactive flag will prompt you to enter the new role’s name and ask if it should have superuser privileges.

createuser --interactive

create user role on postgresql database
After entering the value, launch the interactive shell and run \du command to verify the new role.

list all user roles on postgresql database
As you can see, the role henry is created and has superuser attributes.

Step 5: Create a New Database

You have successfully created a new role “henry” on the server. Now, let’s create a new database for the user “henry”.

By default, the role used to log in has a database with the same name that it can access in PostgreSQL. So, if you created a new database henry, the role henry will connect to it by default.

You can create a new database using the createdb command. This command creates a new database “henry”.

createdb henry

Run the \l command to check if the new database is created.

\l

create new database on postgresql ubuntu

Step 6: Open Postgres Shell with the New Role

A system account with the same username as the role is able to log in to that role. So, if you do not have a matching user with the same name, you have to create one first.

sudo adduser henry

After creating the user, switch to the user henry using the following command.

sudo su - henry

Run the psql command to open Postgres shell as user “henry”.

psql

Then type this command to check connection details.

\conninfo

open postgresql shell with different user
As you can see, it shows that you are now connected to the database henry as user henry this time.

Step 7: Create Tables in the Database

A relational database can contain multiple related tables. A table has rows and columns. It is useful for storing structured data such as customers, products, employees, etc.

You can use \d command to view tables in a database.

view a list of tables in database postgresql
Currently, there are no tables in the database “henry”.

The CREATE TABLE statement creates a new table in the current database. The owner of the table will be the user executing the command.

The basic syntax of CREATE TABLE for creating tables is:

CREATE TABLE table_name (
column_name1 datatype (length) column_contraint,
column_name2 datatype (length) column_contraint,
column_name3 datatype (length) column_contraint,
);

It will define a new table, column names and types, max length, and column constraints.

The following example creates a new table student having columns: std_id, name, grade, and address.

CREATE TABLE students (
std_id serial PRIMARY KEY,
name VARCHAR (100) NOT NULL,
grade VARCHAR (30) NOT NULL,
address VARCHAR (255) NOT NULL
);

create table in postgresql database ubuntu
The first column in the table uses serial data type for storing student ID numbers. It stores data in a sequential integer. The column constraint is PRIMARY KEY which means it is a unique value and not null. A table can have only one primary key.

The other three columns name,  grade, and address also cannot have null values. For these columns, the maximum field length is also defined.

Run the \d or \dt command to display tables in a database.

\dt

view tables in postgresql databse
To remove tables from the database, you can use the DROP TABLE statement.

DROP TABLE students;

delete tables in postgresql database

Conclusion

You have now successfully installed and configured PostgreSQL on your Ubuntu system. You have also learned to create a new role and connect it to the PostgreSQL database. We also showed you how to create and delete tables in the database. If you have any questions, please let us know in the comment section below.

ShareTweetShareShare
Previous Post

Top 15 Linux Performance Monitoring Tools

Next Post

How to Upgrade Ubuntu 20.04 LTS to Ubuntu 22.04 LTS Jammy Jellyfish

Next Post
How to Upgrade Ubuntu 20.04 LTS to Ubuntu 22.04 LTS Jammy Jellyfish

How to Upgrade Ubuntu 20.04 LTS to Ubuntu 22.04 LTS Jammy Jellyfish

Discussion about this post

Copyright © 2022 LinuxWizardry.com

No Result
View All Result
  • #682 (no title)

Copyright © 2022 LinuxWizardry.com

Welcome Back!

Login to your account below

Forgotten Password?

Retrieve your password

Please enter your username or email address to reset your password.

Log In