In this blog post , we will explain how to install PostgreSQL database on Ubuntu 18.04

What is PostgreSQL?

PostgreSQL is an open source and powerful object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.

PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extender.

How to Install PostgreSQL on Ubuntu

The PostgreSQL packages will be available in the default Ubuntu repository.

But to install the latest release of PostgreSQL , We need to add the latest repository to the system from the official PostgreSQL website.

Lets add the repository configuration to the ubuntu system.

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

Import the repository signing key to the system.

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

Update the apt packages list

sudo apt-get update

Install the latest version of PostgreSQL using the below command.

sudo apt-get install postgresql -y
Install PostgreSQL

 

Once the PostgreSQL package is installed on the ubuntu system,

We can check the status of the postgresql service by running the below command.

sudo systemctl status postgresql.service
Install PostgreSQL

We can also verify it checking the version of postgresql installed on the system.

sudo /usr/lib/postgresql/12/bin/psql --version
Install PostgreSQL

PostgreSQL 12 is the latest version available and we have installed it on the ubuntu system.

Creating Users , Databases and Granting permissions

By default , PostgreSQL creates a Role ‘postgres‘ , [Role is nothing but a user] and the database named postgres

The Role postgres , Will have admin privileges such as Creating Roles , databases , configuring replication , managing users and permissions etc.

This user is also called as Superuser

To login to postgresql shell as postgres user , Run the below commands.

sudo su - postgres
psql
Install PostgreSQL

We have now logged as postgres user into the postgres database.

To check the lists of available databases,

\l
Install PostgreSQL

To list all the available users,

\du
Install PostgreSQL

To connect to postgres database,

\c postgres
Install PostgreSQL

To list all the available tables in a database,

\dt
Install PostgreSQL

To create a Roles / Users in the PostgreSQL database,

Creating user without password,

create user username;

Create user with password,

create user username1 with password 'testpass123';
Install PostgreSQL

Create user with password expiry date. In the below example , The user wont be able to login after 12 Dec , 2020.

create user username2 with password 'testpass123' valid until '2020-12-12';
Install PostgreSQL

Lets list all the created users,

Install PostgreSQL

To drop or delete users from postgresql database,

drop user username
Install PostgreSQL

To create database ,

create database testdb;
Install PostgreSQL

To drop or delete a database,

drop database testdb;
Install PostgreSQL

To grant database access to a user,

grant ALL ON DATABASE testdb to username; 
Install PostgreSQL

As the user ‘username‘ was granted full permission to database testdb , he/she can make any changes on that particular database.

To remove / revoke database access for a user,

revoke ALL on DATABASE testdb FROM username;

Install PostgreSQL

Conclusion

We have successfully installed and learnt how to manage PostgreSQL databases and users.

Hope you find it helpful.Please check out my other articles.