How To Install PostgreSQL on Ubuntu 18.04

PostgreSQL is an open-source, object-relational database system that provides an implementation of the SQL querying language. It has all the standard features of traditional commercial database systems with additional enhancements in next-generation DBMS systems.

Most Linux platforms, such as Debian, Red Hat / CentOS, SUSE, and Ubuntu, have PostgreSQL integrated with their package management. It is recommended that you install PostgreSQL this way since it ensures a proper integration with the operating system, including automatic patching and other update management functionality.

This tutorial will guide you through the process n how to install PostgreSQL on Ubuntu systems. We will be using Ubuntu 18.04 version and Ubuntu 16.04.

What you will need (for Ubuntu 18.04)

An Ubuntu 18.04 Linux server
A root user account or a user account with sudo privileges
Access to the terminal window
Basic understanding of Linux commands

Method 1: Install from PostgreSQL Apt repository

Step 1: Add PostgreSQL Repository

PostgreSQL is available by default on all Ubuntu versions. However, it does not guarantee automatic updates when new releases come out. Therefore, the best way to install the software is from the PostgreSQL Apt repository.

First, enter the following commands to add the GPG repository key:

sudo apt-get install wget ca-certificates

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

Now add the PostgreSQL repository by entering:

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 2: Install PostgreSQL

Now that the PostgreSQL repository has been added to your system, it’s best to run an update on your system’s package list. This will ensure that you will install the latest PostgreSQL package.

sudo apt-get update

To install PostgreSQL, enter the following:

sudo apt-get install postgresql postgresql-contrib

The -contrib will add some additional utilities and functionalities to the PostgreSQL installation.

Method 2: Install from the Local Ubuntu repository

Step 1: Check the available PostgreSQL version

In this section, we will install PostgreSQL from the local ubuntu repository. However, remember that you will likely not get the latest package version.

First, we will update the repository:

sudo apt-get update

Now enter the following command to verify which version of PostgreSQL is available and if it will be okay for you. The following command will show all necessary information, including version and size.

apt show postgresql

Step 2: Install PostgreSQL package

To install PostgreSQL, enter the following command:

sudo apt-get install postgresql postgresql-contrib

With that, you have completed the installation of PostgreSQL on Ubuntu1 18.04. After installing from either the local repository or the PostgreSQL Apt Repository, the following steps will be used to connect to PostgreSQL successfully.

Step 3: Connect to PostgreSQL

By default, Postgres uses a concept called “roles” to handle authentication and authorization. During the installation process, a user account called Postgres was created, and you can access this by this command:

$ sudo su – postgres

Now with one simple command, you can enter the Postgres prompt:

$ Psql

This will log you into the PostgreSQL prompt, and from here, you are free to interact with the database management system immediately.

If you would like to exit the PostgreSQL prompt:

postgres-# \q

This will bring you to the Postgres Linux command prompt. If you would like to return to your regular system user, enter:

exit

Another way to connect to the Postgres prompt is to run the psql command directly. To do this, enter the following command:

$ sudo -u postgres psql

This command will log you into Postgres without going through the intermediary bash shell.

Step 4: Check connectivity

If you are connected to PostgreSQL and want to see details of the connection, use the command:

postgres-# conninfo

The output displays the database name, the account you are logged in to, the socket path, and the port number.

Step 5: Create a new role

Once you are logged into the Postgres account, you can create a new role by using this command:

postgres-# createuser –interactive

If you don’t want to switch to the Postgres account, you can do this using the sudo command:

$ sudo -u postgres createuser –interactive

No matter how you want to add the user, the run script will be the same. You will be prompted with some choices to create a user.

What you will need (for Ubuntu 16.04)

An Ubuntu 16.04 Linux server
A root user account or a user account with sudo privileges
Access to the terminal window
Basic understanding of Linux commands

Step 1: Update the system

First, ensure that all your system packages are up-to-date by running the following apt-get commands in the terminal.

sudo apt-get update

sudo apt-get upgrade

Step 2: Installing the PostgreSQL server

PostgreSQL is available in the default repositories. So enter the following command from the terminal to install it:

apt-get install postgresql postgresql-contrib phppgadmin

Step 3: Access the PostgreSQL command prompt.

After installing the PostgreSQL database server, it automatically creates a user ‘Postgres with role ‘Postgres. It also creates a system account with the same name ‘Postgres. So to connect to the Postgres server, log in to your system as user of Postgres and connect database:

su - postgres

psql

Now you are logged in to the PostgreSQL database server. To check login info, use the following command from a database command prompt:

postgres-# \conninfo

To disconnect from the PostgreSQL database command prompt, type the below command and press enter. It will return you to the Ubuntu command prompt:

postgres-# \q

Step 4: Create a new user and database

For example, let us create a new user called “idroot” with the password “idrootnet”, and a database called “idrootdb”.

sudo -u postgres createuser -D -A -P idroot

sudo -u postgres createdb -O idroot idrootdb

Step 5: Configure Apache2 for phpPgAdmin.

phpPgAdmin is a web-based administration tool for PostgreSQL. It is perfect for PostgreSQL DBAs, newbies, and hosting services. You need to configure apache for phpPgAdmin. Edit the file /etc/apache2/conf-available/phppgadmin.conf:

nano /etc/apache2/conf-available/phppgadmin.conf

Comment out the line #Require local by adding a # in front of the line and below the line allow from all so that you can access your browser.

Step 6: Configure phpPgAdmin

Next, edit the file /etc/phppgadmin/config.inc.php:

nano /etc/phppgadmin/config.inc.php

Now change the following option:

$conf[‘extra_login_security'] = true;

$conf[‘extra_login_security'] = false;

Now, we can restart Apache and phpPgAdmin so that the changes take place:

systemctl restart postgresql

systemctl restart apache2

systemctl enable postgresql

systemctl enable apache2

Step 7: Accessing phpPgAdmin.

phpPgAdmin will be available on HTTP port 80 by default. Open your favourite browser and navigate to:

http://yourdomain.com/phppgadmin

http://server-ip/phppgadmin.

If you are using a firewall, please open port 80 to enable access to the control panel. Congratulations! You have successfully installed PostgreSQL Server.

Conclusions

This article should help you set up PostgreSQL on Ubuntu 18.04 and Ubuntu 16.04. Both installations are straightforward, whether you choose to install from the PostgreSQL repository or the local Ubuntu repository.

For those looking to manage their databases on a remote server, you might find it useful to set up a Ubuntu VPS server or an RDP server for Ubuntu. These setups can greatly enhance your ability to work with PostgreSQL from different locations and environments.

If this article was of help, let us know in the comments below (we are always eager to hear your feedback).

People also read: