Installation of PostgreSQL 9.6 on Ubuntu 14.04 LTS

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a database system with robustness, extensibility and the one which complies to technical standards.

While we decided to try it out, at many points we faced small issues to get it up and running, mainly the issues were around misconfigurations being done during installation.

So, lets walk through the steps and configurations step by step, so that we can install Postgres and interact with it from an external application.

Step 1: Add Postgresql repository

To start with, first lets add the Postgres repository to the system using :

sudo add-apt-repository “deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main”

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

This will return an output : OK

Step 2: Install Postgresql 9.6

Use the following commands to install Postgres packages :

sudo apt-get update

sudo apt-get install postgresql-9.6 postgresql-contrib

Verification — Using psql shell (Optional)

Run the following command to verify if Postgres installation was successful.

sudo -u postgres psql postgres

This should take us to postgres interactive terminal :

Step 3: Create Database and User:

Run below commands through psql client to create the database and user

# CREATE DATABASE nodedb;

# CREATE USER adminuser WITH PASSWORD ‘adminpassword’;

Exit from psql client using \q, and then

Exit from postgres user using exit

Step 4: Configure PostgreSQL to allow remote connection:

i. Configuring postgresql.conf:

Uncomment listen_addresses and change its value from ‘localhost’ to ‘*’

Port number should be 5432. If not, then update go to Step 5: Restart Postgres

port = 5432 # (change requires restart)

ii. Configuring pg_hba.conf:

Update client authentication configuration

open pg_hba.conf and add following entry at the very end.

host all all 0.0.0.0/0 md5

host all all ::/0 md5

Adding wildcards , these should be replaced with tighter controls.

Step 5: Restart Postgres

Restart progress using :

sudo service postgresql restart

Verify the number of services which are running. Only one should be running

If there are two then remove postgresql-11 and postgresql-client-11

Now restart PostgreSQL. And repeat Step 3: Create Database and User

To check if configuration is done properly execute :

psql -h <ip> -U adminuser -d nodedb -p 5432

This completes Postrgres installation with which external applications, systems, virtual machine can connect .