Starting with PostgreSQL

In this Post you will learn how I met PostgreSQL and started to learn. If you are planning PostgreSQL as your database but don’t know where to start, this blog may help you.

My background

I have used MySQL database like an external hard drive, just to save and store the data from an application. Several times I installed on CentOS/Ubuntu from the repository. Mostly I used preinstalled MySQL databases from webhosting providers. On the application layer for example WordPress, I added the connection strings and it was done for me. All tables, rows, relationships have been created automatically.

One day if you want to develop your own application, you will need to design the Database schema and you will ask; “Okey, which fields needs my application and where do I need to save it?” For the first time maybe, you will not care about the datatypes, but once your application will reach 1Tb, you will be starting to tune your database. Later on, you will ask: “Hmm, why is this data returned as 0/1 instead of a Boolean true/false? How can I follow the table changes?”. You will develop your own history table on application layer. But what happens, if someone update data directly on database (for example by your self)? You will not see the changes on history table… One problem more after you solved a problem 😀

To make the long story short: I have researched how I can solve these issues like above in the example. Looked how Enterprises developing their applications. Often you will read about Oracle and PostgreSQL. You maybe already heard about it, but you never cared like me. Once you compare this two Databases and on your first look you will see: Oracle needs license where PostgreSQL is totally Open Source…

How I met PostgreSQL
I had worked first time with PostgreSQL at “PostgreSQL tuning Workshop” organized by dbi services sa. On this workshop I learned a lot, but sometimes it was too difficult for me, because I had never used PostgreSQL before and had no deep SQL knowledge.

Getting started

Before we are getting deeper into PostgreSQL, you should know three thinks:

1. PostgreSQL is very powerful. You can customize everything, but you should be careful what you are doing.
2. By default, PostgreSQL is blocking everything. It’s not like MySQL that you can connect remotely after you opened firewall ports (often beginners fails here and gives up with learning).
3. You have no access to the database with the root user. That means, installation script will create a user named “postgres”. Only with the user ‘postgres’ are you allowed to connect to the database. Later you can create other users.

Installation Options
As PostgreSQL is open source and you see the source code, you have two options to install:

First Option: You download and install the compiled .rpm package from the repository.
Pro:

• Easy for beginners
• No compiling needed
• All-in-One Package, everything ready to use

Contra:

• For critical updates you need wait that the rpm package is updated
• Some not needed features will be installed, which takes disk spaces (PostgreSQL is just ~250 MB)😉
• Installation Paths are automatically generated

Second Option: You download the source code and compile the code by your own and install it.
Pro:

• You have the choice, where to install it
• Updates are available once the code is published
• You can add your custom settings during the installation

Contra:

• It can too difficult for beginners
• Because of security some companies doesn’t allow you to compile your own code

I warmly recommend beginners get it started with the first option, installing from the repository. But you should also try installing from the Source Code.

Differences between RPM and Source Code
Both has his own pro and cons.

Installing from the Repository
For each Linux distribution you have different compiled PostgreSQL packages. You have also different commands to install. On the PostgreSQL page there is a very good documentation about the installation:  https://www.postgresql.org/download/
Also there is a good documentation from Karsten: https://www.dbi-services.com/blog/handling-postgresql-installation-from-packages-internal-activity

Installing from the Source Code
To install it from source code you need few packages more, like gcc compiler, developer packages and so on. One important think: Once you installed from the source code, you need create your own unit files to stop/start/restart the services. Dbi services sa has build a DMK (Database Management Kit) to automatically create the unit files. https://www.dbi-services.com/offering/products/dmk-management-kit/

Allow remote connection
As I write in the above, PostgreSQL is blocking everything by default. I wrote another Blog about this. Configure PostgreSQL for remote connections

Most commands begins with backslash.
For the beginning its little bit strange and hard to work with the command line for Postgres. But remember: If you want use command’s, it always starts with a backslash. Example: If you want to switch to another database, you just type ‘c dbname‘ while the ‘c‘ is meaning ‘connect‘. In MySQL you had type ‘use dbname;‘

Learn SQL
SQL is not difficult to learn. I started learning SQL from following website: https://www.postgresqltutorial.com/

My hints for the beginners: 

• Before you install PostgreSQL, I recommend you to make snapshot’s. If you fail somewhere, you can just restore to the latest snapshot and start again.
• Do not just copy and paste! Understand the commands.
• Watch YouTube tutorials and read Blogs, it helps you a lot!

Conclusion
Every start is difficult but you should start from somewhere. 😉