What is PostgreSQL?

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without reading locks.

Here’s a step by step guide to install PostgreSQL on a digital ocean machine writing a set of command lines on your terminal. This is installation should be carried out on Ubuntu 18.04 LTS, but before that let’s check if you have a previous installation of PostgreSQL, if you don’t have any installation and your machine is clean and fresh you can jump this step to the next one.

First Step: Check Old Installation

In this step you have to do three tasks to accomplish the first step of your mission

First Task: List All Postgres related packages

 $ dpkg -l | grep postgres
postgresql                            
postgresql-8.3
postgresql-client
postgresql-client-8.3
postgresql-client-common
postgresql-common
postgresql-contrib
postgresql-contrib-8.3

Second Task:Remove all the package above listed

$ sudo apt-get --purge remove postgresql postgresql-10 postgresql-9.5 postgresql-client postgresql-client-10 postgresql-client-9.5 postgresql-client-common postgresql-common postgresql-contrib postgresql-contrib-9.5
Remove Process of Postgresql

Third Task:Remove the following folders

Warning Messages

If you check the last image, you find out some warning messages during the process, so the next task is to remove these files from our system

 $ sudo rm -rf /var/lib/postgresql/
$ sudo rm -rf /var/log/postgresql/
$ sudo rm -rf /etc/postgresql/


Let’s move to the next step in our journey to learn SQL

Second Step: Installation of PostgreSQL

Ubuntu’s default repositories contain Postgres packages, so you can install these easily using the apt packaging system. If this is your first time using in apt this session, you need to refresh our local package index. You can then install the Postgres package and a -contrib package that adds some additional utilities and functionality:

$ sudo apt-get update 
Updating Command

The first screen after you introduce the command of updating the repository asks you for your password.

$ sudo apt-get install postgresql postgresql-contrib 
Installation Command

and the second screen asks you if you would like to continue, type yes!
Well done! you accomplish the first mission you have installed PostgreSQL.

The installation process created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you can log into that account. The good thing is that you can sign in to this superuser’s account with this command:

$ sudo -i -u postgres
Login Command

Check this out, first thing asks you for your password. Do you notice the change in the screen above? If you don’t, look to the screenshot again, please.
There is a change from the current user zaid to the new user postgres.

The postgres user will be able to access SQL with this command:

$ psql
PostgreSQL Console

You will be logged in and able to interact with the database management system right away.

After you log out from the PostgreSQL console, you only have the super-user configured within the database. So you need to create new roles from the command line using the create user command. Let’s generate a new user, so you can access your database in the future with the same user of your server (which is the recommended practice). So you need to create new roles from the command line using the create user command. Let’s generate a new user, so you can access your database in the future with the same user of your server (which is the recommended practice).

In our case:

create user zaid with encrypted password 'password_to_login_in_my_ server';

After creating the new user you first you type \q exit from the PostgreSQL console and then you type exit to logout from the superuser and come back to your home.

postgres=# \q
postgres@wordpress:~$ exit
Exit Process

Once you have the appropriate user, you can login into your Postgres database using this command

$ psql -U zaid -d postgres 

If you see the image below which means you did it well.

Login Process Using Different Role

Using this command you can list all the users of your Postgres database

\du
List of Users

Fantastic! Let’s move to SQL and have some fun with it typing these SQL expressions:

CREATE TABLE test_table(name TEXT, phone VARCHAR(11), address TEXT);
INSERT INTO test_table VALUES ('Zaid', '07911123456','Oxford Street');
INSERT INTO test_table VALUES ('David', '07922123456','Manchester Street');

The first line generates a new table called “test_table ” The second and the third insert some values in the table.

SELECT * FROM test_table;

The line above prints all the values to the screen from the test_table

Data From SQL Table

After this little game with SQL you exit from PostgreSQL Console again:

postgres=# \q

Step Three: Installation of pgAdmin

It is time to install and set up pgAdmin. pgAdmin is an open-source administration and development platform for PostgreSQL and its related database management systems. Written in Python and jQuery, it supports all the features found in PostgreSQL. You can use pgAdmin to do everything from connecting to your In this step you have to do three tasks to accomplish the first step of your missionPostgres database from your personal laptop, writing basic SQL queries, writing complex queries, monitoring your databases and configuring advanced database architectures.

In this tutorial, we’ll walk through the process of installing and configuring the latest version of pgAdmin, accessing pgAdmin through a web browser, and connecting it to a PostgreSQL database on your server.

In this step, you have to do three tasks to accomplish the third step of your mission.

First Task: Configuration of the connections.

As a first task, make your remote server ready to connect by typing these pieces of code. The first line is:

$ sudo ufw allow 5432

The second line is to allow incoming connection from any (or specific) IP addresses.

$ sudo su - root 
# echo "listen_addresses = '*'" >> /etc/postgresql/10/main/postgresql.conf

Note: the line above is only one line, be careful with this.

Only One Line

The third line is for enabling the applicable user to access all databases from any (or specific) IP addresses.

# echo 'host all all 0.0.0.0/0 md5' >> /etc/postgresql/10/main/pg_hba.conf

Note: this is one line of code! Only your browser breaks it into 2 lines!

Only One Line

After you added these two lines, then you restart and exit from root bash.

# /etc/init.d/postgresql restart
# exit
Restart And Exit

Second Task: Download and install the program.

Import the repository key from https://www.postgresql.org/media/keys/ACCC4CF8.asc:

$ sudo apt-get install curl ca-certificates curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - 

(You may determine the codename of your distribution by running lsb_release -c). For a shorthand version of the above, presuming you are using a supported release:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' 

Finally, update the package lists, and start installing packages:

$ sudo apt-get update sudo apt-get install pgadmin4
PgAdmin Installation

Third Task: Connecting with the server.

Once you are done, you will see this screen:

PgAdmin Window

Click the Add New Server Icon and fill in the pop up:

PgAdmin Configuration Window

GENERAL:

“Name:” anything you want (eg. “our test server“)

CONNECTION:

“Host name/address:” your remote server’s IP Address (in my case: 46.101.128.118)
“Port:” 5432
“Maintenance database:” postgres
“User name:” [your_user_name]
“Password:” your recently generated SQL password for your user

Click and save Congratulations!! You are connected to your database!

At first sight, it’s not really straightforward, but you can discover the different folders on the left side. If you right click on the name of the server (on my screenshot: “SafaCreations Test Server”), you can disconnect from your server. Or connect the same way, when you want to get back.
Notice that on our screenshot you can see the very same result that we got in the PostgreSQL Console!

CONCLUSION

You accomplish the mission my dear friend Well Done!
You have created you have installed SQL This is a fantastic first step for you towards becoming a Data Scientist!
As I have mentioned a number of times during this article, I have been creating quite a few tutorials to show you how to use SQL. All of these start from the very basics. We will start to put more articles in the next weeks. So keep in touch with us, and discover an easy way to learn SQL.

Cheers,
Zaid Alissa Almaliki