How to install Postgres SQL Ubuntu

I experienced some challenges installing and connecting to Postgres SQL on my local Ubuntu 19.04 server, so I thought I would just detail the process just in case my future self, comes searching for the solution again!

Take note

I use ProxMox Virtual Environment to run my virtual server environment in my office. So if you still experience connectivity issues with PostgresSql on your LAN just check your firewall rules

Install PostgreSQL

The first step is to use terminal to install PostgreSQL , which is simple to do using the default package repositories.

Shell

Configure PostgreSQL

Once installed and if you want to be able to connect to your PostgreSQL database from our local network, which is what I needed to do.

You will need to remember to edit your configuration files, located in /etc/postgresql/10/main/

Advice

Not the version number. In my case as I installed from the default repositories it was version 10 installed, this may be different depending on which repo or version you install from.

The first file to edit is the postgresql.conf to configure which address you want PostgreSql to listen on. In my case it was on 192.168.0.24. You can make use of the wild card '*'

Simply open the file using whichever text editor of choice in my case I used nano.

Shell

Once that is complete we now need to edit the pg_hba.conf file to make sure it using our network ip address range.

Shell

Once complete we can simply restart our Postgres Service to ensure it picks ups the changes we made

Shell

Create your Postgres User

We can create the user account and Database we want to use. Using the terminal log into you Postgres server

Shell

Create the user and the database you need. In my case for the development environment I wanted to use a UserName/Password combination so I wanted to prompted to create a password. You requirements may be different.

createuser --interactive --pwprompt

The wizard will guide you through the process of creating the user. Once complete you can create the database for the associated user. In my case the username is sourcelink and the database will be sourcelink

Shell

Connect to your database.

In my case, I want to connect to my database using DataGrip – The Cross-Platform IDE for databases so I simply configured Datagrip to connect

Summary

This was a relatively simple process that took me over an hour to figure out! Hopefully it saves somebody else that pain!

Gary Woodfine

Technical Director at Denizon
Gary is Technical Director at Denizon, an independent software vendor specialising in IoT, Field Service and associated managed services,enabling customers to be efficient, productive, secure and scalable in a way which helps them address and reduce their ecological impact.

Denizon's product line successfully integrate IoT, Artificial Intelligence and Blockchain technology to enable efficient, productive, secure and scalable solutions to help organisations address increasing energy demands, ecological impact and Health & Safety concerns of their staff.

Latest posts by Gary Woodfine (see all)