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.

sudo apt install postgresql postgresql-contrib

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.

sudo nano /etc/postgresql/10/main/postgresql.conf

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

sudo nano /etc/postgresql/10/main/pg_hba.conf

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

sudo service postgresql restart

Create your Postgres User

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

sudo -i -u postgres

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. Your 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

createdb -O sourcelink sourcelink

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!

About
Latest Posts

Gary Woodfine

Technical Director at threenine.co.uk

Gary is Technical Director at threenine.co.uk, an independent software vendor specialising in IoT, Field Service and associated managed services,enabling customers to be efficient, productive, secure and scale-able.