Installing PostgreSQL in Ubuntu 18.04

What is PostgreSQL?

“PostgreSQL is a powerful, open source 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. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.”

This powerful system comes along with various features that aid developers to manage their databases on applications, to build them and also protect the integrity of data.

It is an open source system and can be easily downloaded for free. Apart from that, it is greatly extensible, developers can define their own customised functions, use different coding languages without needing to alter your database!

In today’s blog, let us have a look at how to install PostgreSQL11 on our Ubuntu 18.04 system.The default repositories in Ubuntu contains PostgreSQL packages.
In case, they are missing or unavailable, you can add the PostgreSQL apt repository to your Ubuntu, you can use these following commands suggested on the official website.

Step 1: Install Certificates

sudo apt-get install wget ca-certificates

Step 2: Import and Add GPG Key

You will need to import the GPG key for accessing the PostgreSQL packages.

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

Step 3: Add PostgreSQL repository to your system

This command will add the repository to your 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'

Step 4: Install PostgreSQL on Ubuntu

Once the official repository is added to our Ubuntu system, we will have to update the repository list.

sudo apt-get update

After updating, the next command installs PostgreSQL on your system.

sudo apt-get install postgresql postgresql-contrib

Working with the PostgreSQL account

After installing PostgreSQL, a default account is created with the default role. We can log into that account and start using PostgreSQL. This account has a username ‘Postgres’ and role assigned as ‘Postgres’

You can switch to the postgres account by using the following command. The password will remain the same as the login password.

sudo -i -u postgres

The postgresql client also gets installed by default when we install postgresql in the initial steps. So let’s use the psql command to connect and start accessing our Postgre databases

psql

This command will help you interact with your databases.

You can exit and disconnect from the ProgreSQL prompt by using

postgres-# \q

To create a new role on PostgreSQL

The role we previously used was the “postgres” role. To create a new user (more precisely a new role), use the command line with the createuser command. By using the –interactive flag, you can set the name for your new role. It also allows setting superuser permissions.

As you are already logged in and are using the “postgres” role, use the following commands,

createuser --interactive

If, instead, you prefer to use sudo for each command without switching from your normal account, type:

sudo -u postgres createuser --interactive

The command prompts a few choices to select from, and a role is created based on the responses submitted by the user.

# Output
Enter name of role to add: newuser 
Shall the new role be a superuser? (y/n) y 

Working with PostgreSQL service

After the PostgreSQL installation is complete, it gets listed as a service by default in Ubuntu and starts by itself. Let’s check if it has started by default.

Check PostgreSQL service status

service postgresql status

Start PostgreSQL service

If it has not started by itself, let us do that instead.

Start PostgreSQL service

service postgresql start

Restart PostgreSQL service

service postgresql restart

Stop PostgreSQL service

service postgresql stop

That’s all about the few key steps to follow while installing PostgreSQL on your Ubuntu system!