How To Install PostgreSQL Database Server On Ubuntu 18.04?

Introduction

PostgreSQL (also known as Postgres) is a powerful open-source, Object-Relational Database-Management System (ORDBMS) that is free to use, adjust, and distribute. It’s an advanced database system that packs a punch in terms of innovative features with strong emphasis on standard compliance and extensibility.

For these reasons, PostgreSQL can handle a range of workloads and allow users to create simple or multifaceted web applications without a struggle.

This tutorial will help you install PostgreSQL database system on your Ubuntu 18.04 server and perform the basic database management tasks.

Prerequisites

To install PostgreSQL on Ubuntu 18.04, you must:

  • Be logged into your Ubuntu 18.04 server as a user with sudo permissions.

Step 1 Installing PostgreSQL

Ubuntu 18.04 default repository comes with PostgreSQL packages. For this reason, we’ll install the packages using the apt command system. Since we are using the apt command for the first time in this session, we’ll refresh the package index, before executing any command. Once, the package index is refreshed we’ll install PostgreSQL together with the -contrib package:

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib

Step 2 Verifying Postgres Installation

The PostgreSQL database server should start automatically once the installation is completed. However, we need to verify our installation. Let's use the psql; a reliable command line utility that permits us to use the PostgreSQL database server. Run the command below to verify the installation:

$ sudo -u postgres psql -c "SELECTversion();"

This will give you an output similar to the one below:

PostgreSQL 10.5 (ubuntu 10.50.18.4) onx86_64-pc-linux-gnu, compiledbygcc (ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit

That is it! You have installed Postgres on your Ubuntu 18.04 server. Next, we’ll explore the unique quality of this database management system.

Step 3 Using Postgres Databases And Roles

By default, PostgreSQL utilizes “roles” concept to process authorization and authentication. Once, it's installed this database system is configured to leverage the ident authentication, which associates PostgreSQL roles with the corresponding Linux/Unix system account. If a role is present within PostgreSQL, a Linux/Unix username with a matching name can easily sign in as that role.

Our installation created an account titled Postgres which is linked to the default PostgreSQL role. You can easily login into that username account to utilize PostgreSQL. There multiple ways of using this account to get permission to use Postgres.

Switching The PostgreSQL Account

Now, execute the command below to change to the default Postgres account.

$ sudo -i -u postgres

Then, run the command below to log into the PostgreSQL prompt.

$ psql

You can now use the database system management. In case, you want to exit the Postgres prompt, run the command below:

postgres=# q

This will take you back to the database management system Linux command prompt.

Accessing PostgreSQL database server Prompt without changing accounts

If you want to execute commands directly with sudo, run the command below to log into Postgres without an intermediary bash shell:

$ sudo -u postgres psql

To exit the Postgres session, run the command below:

postgres=# q

Creating New Role

By now, you have a Postgre role created within your database. You can now proceed and create another role using the creatrole command. Log in as a Postgres account and execute the command below to create another role:

postgres@server: createuser --interactive

The --interactive flag in the command prompts you to enter a name for the role created and requests you to grant or deny the role superuser permissions.

Alternatively, you can use sudo to fashion a new role:

$ sudo -u postgres createuser --interactive

This will give you an output with some questions, like the one below

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

Type Y and press ENTER to grant the new user superuser privileges. Alternatively, you can type Y and press ENTER to deny the user superuser permissions.

Creating New Database

To fashion a new database follow the steps below:

  • If you are logged into the PostgreSQL account as Postgres account, execute the command below to create a database:
postgre@server:$ createdb HA
  • On the other hand, if you prefer to use sudo, run the command below to fashion a new database;
$ sudo -u postgres createdb HA

Using The New Role To Open The PostgreSQL prompt

PostgreSQL database management system gives the option to use ident-based authentication to log into the Postgres prompt. Here, you require a user with a name similar to the Postgres database and role.

If your Linux username doesn't match that of the Postgres database and role, you can use adduser to create one. To achieve this, you must be logged in to your Linux server as a user with sudo privileges.

Execute the command below, to create a user:

$ sudo adduser HA

This will automatically create the new account. Once it's created, switch over to connect to your database:

$ sudo -i -u HA
$ psql

Alternatively, you can run the command below to achieve this inline:

$ sudo -u HA psql

If all the components are properly configured, you will be logged in automatically.

Additionally, you can connect to a specific database by specifying the database name in the command below:

$ psql -d postgres

Once you log in, run the command below to examine the current connection information:

HA =# conninfo

This will give you an output similar to the one below:

You are connected to database "HA"as user "HA" via socket in"/var/run/postgresql" at port "5432".

Conclusion

That’s it! You have successfully installed PostgreSQL on Ubuntu 18.04 and learned how to perform some of the most basic tasks. You can explore further PostgreSQL management tasks such as creating/deleting tables, adding/deleting columns, and more.

Check out these top 3 Linux hosting services

Was this article helpful?