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 -
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
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
We can also verify it checking the version of postgresql installed on the system.
sudo /usr/lib/postgresql/12/bin/psql --version
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
We have now logged as postgres user into the postgres database.
To check the lists of available databases,
\l
To list all the available users,
\du
To connect to postgres database,
\c postgres
To list all the available tables in a database,
\dt
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';
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';
Lets list all the created users,
To drop or delete users from postgresql database,
drop user username
To create database ,
create database testdb;
To drop or delete a database,
drop database testdb;
To grant database access to a user,
grant ALL ON DATABASE testdb to username;
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;
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.