“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
ALTER USER postgres PASSWORD ‘newPassword’;
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 = ‘*’
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
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:
1. PostgreSQL: Getting Started by Pinal Dave
2.PostgreSQL: Introduction to SQL Queries by Pinal Dave
3. PostgreSQL: Advanced SQL Queries by Pinal Dave
4. PostgreSQL: Advanced Server Programming by Pinal Dave
5. PostgreSQL: Index Tuning and Performance Optimization by Pinal Dave
Below are some topics I am considering for my exploration next week:
- Advanced Topics In PostgreSQL
- High Availability
- Migration of SQL Server database to PostgreSQL
- Run Python Scripts in SQL Server Agent
- Welcome to Machine Learning (Just to grasp concepts)
- Data Visualization Tools (i.e. Looker)
- ETL Solutions (Stitch, FiveTran)
- Process and Transforming data/Explore data through ML (i.e. Databricks) .
- Getting Started with Kubernetes with an old buddy (Nigel)
- Stay safe and Be well