What is PostgreSQL?
PostgreSQL, also goes by the name “Postgres”, is an open-source, object-oriented relational Database Management System. It is used by many applications to store data. This database supports many cutting-edge features like reliable transactions and concurrency.
MacOS server uses PostgreSQL as its default database. It is also available for Linux, Windows, FreeBSD, and OpenBSD.
- Allows data to be stored in various formats.
- Data Integrity and Security
- Supports features like Indexing, Multi-Version Concurrency Control, sophisticated Query Planner, Table Partitioning, Just-in-Time Compilation of Expressions, and more.
- Supports various functionalities like Write-Ahead Logging, Replication, Point-in-Time Recovery, Active Standbys, Table Spaces and more.
- You can extend it to any application you use.
In this guide, you will see how you can easily install PostgreSQL on Ubuntu 20.04 with the help of a few simple commands. We will also explore different commands to create new roles/users and databases.
Let’s do this!
Step 1: Update the packages
Before initiating the PostgreSQL installation, let’s update the packages that are already installed on our system. Do this by running the command mentioned below:
sudo apt update
Step 2: Install PostgreSQL
Now, we will install the PostgreSQL package along with the contrib package which is needed to add some additional utilities and functionality. Use the following command to do that:
sudo apt install postgresql postgresql-contrib
PostgreSQL Roles and Databases
Now that we have successfully installed PostgreSQL, let’s get ourselves a little familiar with the PostgreSQL roles and databases.
Roles are meant to handle authentication and authorization of the PostgreSQL databases.
When we install PostgreSQL, by default a Postgres user is created. To switch to this user, run this command:
sudo -i -u postgres
In the next step, we will access the Postgres prompt. This prompt will let you communicate with the database management system.
To do that, run the following command:
You can exit the prompt using the command below.
After running the previous command, you will be back in the postgres Linux command prompt. To go back to your regular system, run the exit command.
One more way to enter the postgres prompt is by running the psql command directly with sudo as shown below:
sudo -u postgres psql
To quit the interactive Postgres session, you can run the command below as we previously did.
New Role Creation
Once you are logged in from your postgres account, you can make a new role by running the following command:
You can also switch directly from your normal account using sudo as shown below:
sudo -u postgres createuser --interactive
Both the methods will ask you to enter the name of the role. It will also ask you to confirm if the new role can be a superuser or not.
When you create a user in postgreSQL, its authentication system thinks that the role that you have used to log in, will have a database associated with it with the same name that it can access. Didn’t understand what I just said? Let me make it a little clear for you. This means, that the user “Tom” that we previously created, will try to connect to a database with the same name as the role.
You can create a database having the same name as the role, using the following command:
Another way to make a database is by using sudo directly from your normal account by running this:
sudo -u postgres createdb Tom
Open Postgres Prompt with a new role
To log in with ident-based authentication, you need to have a Linux user with the same name as your Postgres role and database. If you don’t have one, don’t worry, you can create it using the adduser command. You will have to do this with your non-root account using sudo privileges.
sudo adduser Tom
After that, you can connect to the database by running these two commands:
sudo -i -u Tom
Or instead, you can use a simple one-line command:
sudo -u Tom psql
Both ways, you will get access.
To interact with a different database, run the following command by mentioning the database as shown below:
psql -d postgres
To check the status of your current connection, run the conninfo command. It will provide you information of your database connection.
This is what the output will look like:
That is all, folks.
In this document, you saw how to install PostgreSQL on your Ubuntu 20.04 LTS machine. With that, you also got to see how you can enter the postgres prompt. We also explored how to create new roles and databases with the help of a few simple commands.
You can also see how to install PostgreSQL on