Postgresql

Week of April 24th

Posted on by Mark Shay

“We’ll search for tomorrow on every shore..”

Last week, after our leisurely cruise had docked, it wouldn’t be too long before we would set an “open course for this week’s virgin sea“. As I was preparing my coordinates,  “I look to the sea, reflections in the waves spark my memory” which led me down a familiar path. As some of you might know, I have spent a majority of my career working with Relational Databases (in particular Microsoft SQL Server). 

Over the years MS SQL Server has become one of the most popular RDBMS but with each new release and awesome new features added to SQL Server in some cases it become highly restrictive from a licensing standpoint with a very high TCO especially if the database was large in size, or it was accessed by a many clients. With enterprise licensing skyrocketing this of course opened the door for open-source the RDBMS movement. 

The leader in this category has been MySQL. However, after being acquired by the Oracle many have been dissuaded to use this database for new projects. Not to mention, the original creator of MySQL left after acquisition and subsequently forked the code and developed MariaDB which has had received a lukewarm response in the industry but the real little blue elephant or ” слоник” in the room was clearly PostgreSQL.  

Both PostgreSQL and MySQL launched around same time but not until recent years has PostgreSQL really taken off. But it was always sort of lurking in the grasslands.  Today, PostgreSQL has now emerged as one the leaders not only in the open-source world but for all relational databases. So after “a gathering of angels appeared above my head..They sang to me this song of hope, and this is what they said…”  

Ok, Where to start? 

Well, the basics..

First, I need a Postgres Environment to work with.. For this exercise, I wanted to avoid any additional charges in the cloud so I needed to developed my own Prem solution.  


Here are the steps I took:

  • Install Oracle VirtualBox on the Mac book
  • Download Ubuntu 18.x.x
  • Mount ISO and Install Ubuntu
  • Change System Memory to higher value
  • Change display settings Memory to higher value
  • After Ubuntu install -> Power down
  • In Virtual Box Click Tools -> Network -> Create NIC
  • Under Ubuntu Image ->
  • Create a 2nd virtual NIC
  • Host-only Adapter

Get SSH working:

sudo apt update

sudo apt install openssh-server

sudo systemctl status ssh

sudo ufw allow ssh

Install PostgreSQL (Server) on Ubuntu:

sudo su –

apt-get install postgresql postgresql-contrib

update-rc.d postgresql enable

service postgresql start

Verify  local connectionc(On Server):

sudo -u postgres psql -c “SELECT version();”

sudo su – postgres

Change Postgres Password from Blank to something meaningful

psql 

ALTER USER postgres PASSWORD ‘newPassword’;

\q

exit

Open up FW port to allow Postgress traffic

ufw allow 5432/tcp

Enable remote access to PostgreSQL server

  • Edit postgresql.conf

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

Change  from  listen_addresses = ‘localhost’ to listen_addresses = ‘*’

—Restart postgres

sudo service postgresql restart

—Verify Postgres listening on 5432

ss -nlt | grep 5432

  • Editing the pg_hba.conf file.

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

host    all             all              0.0.0.0/0                   md5

host    all             all              ::/0                            md5

—Restart postgres

sudo service postgresql restart

(On Mac Client)

Download pgAdmin 4 for MAC (Client)

  • Launched Browser
  • Created Postgres connection

Next, I downloaded and restored the sample Database – http://bit.ly/pagilia-dl and I was ready to take on some learning.  

For my eduction on Postgres I turned to reliable source on Pluralsight. To no other than the SQLAuthority himself who produced a series of great courses! Below was my syllabus for the week:

Monday:       

1. PostgreSQL: Getting Started by Pinal Dave

Tuesday:
 2.PostgreSQL: Introduction to SQL Queries by Pinal Dave


Wednesday:
        3. PostgreSQL: Advanced SQL Queries by Pinal Dave

Thursday:
        4. PostgreSQL: Advanced Server Programming by Pinal Dave


Friday:
        5. PostgreSQL: Index Tuning and Performance Optimization by Pinal Dave

Below are some topics I am considering for my exploration next week:

  • Stay safe and Be well

—MCS 

Leave a Reply