Official Power Up Hosting Blog

Everything about Linux, Windows, and hosting ;)

Selvakumar
Author

I am an Online Marketer and technology lover. I like to learn new things and share that with people.

Share


Our Newsletter


Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Tags


Twitter


Official Power Up Hosting Blog

Install PostgreSQL on Ubuntu 16.04 (Learn PostgreSQL)

SelvakumarSelvakumar

In the previous tutorial, we have compared the Postgre SQL and MySQL. Also, we have seen how to install and configure MySQL on Ubuntu 16.04

But:

Even though MySQL is the choice for many people, the Postgre SQL win over the reliability competition due to its good support for concurrency.

What is PostgreSQL?

PostgreSQL is a Relational Database Management system which store, retrieves the information from the Postgresql Database.

It may seem similar to the MySQL. But, the main advantage here is concurrent access without reading locks and reliable transactions.

From Small to Large website, a lot of people were started using the PostgreSQL.

The PostgreSQL implements the structured query language to create and manage databases. You will require the below mentioned things for this tutorial.

Prerequisites

  • Ubuntu 16.04 server configured according to the initial server setup guide.

Installing PostgreSQL

You don't need to go anywhere for installing PostgreSQL. Since it is in our Ubuntu repository packages, we can install the PostgreSQL using Postgresql install apt utility.

First, update the package index

$ sudo apt-get update 

After that, install the PostgreSQL. Here we will also install one more package called -contrib.

If you need any additional packages, just install them using the install postgresql ubuntu command.

$ sudo apt-get install postgresql postgresql-contrib

Once you execute the postgresql install ubuntu command, It will ask for your permission to continue. Press Y. After that, PostgreSQL will be installed.

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
 libpq5 libsensors4 libxslt1.1 postgresql-9.5 
 postgresql-client-9.5
postgresql-client-common postgresql-common postgresql-
contrib-9.5 ssl-cert
sysstat
Suggested packages:
lm-sensors postgresql-doc locales-all postgresql-doc-9.5 libdbd-pg-perl
openssl-blacklist isag
The following NEW packages will be installed:
libpq5 libsensors4 libxslt1.1 postgresql postgresql-9.5
postgresql-client-9.5 postgresql-client-common 
postgresql-common
postgresql-contrib postgresql-contrib-9.5 ssl-cert sysstat

..................

It will take few seconds to install the PostgreSQL.

Here, we will see how the PostgreSQL works and how it can be different from other Relational Database Management Systems.

The thing you should know about PostgreSQL

Here in PostgreSQL, we will use a concept called 'role'. The role is similar to that of the user accounts in Linux/Unix operating system.

But:

The role does not have any such user and group variations. We will use this role to manage the authentication and authorization.

By default, the PostgreSQL will use the ident authentication.

You can login to the PostgreSQL with your Unix/Linux user account if it matches the role name.

There are two ways using that you can access the PostgreSQL.

One is accessing the PostgreSQL from the terminal and another method is directly logging into the PostgreSQL and doing all the operations.

Switching to PostgreSQL account.

To switch to the PostgreSQL account, use the below command.

 $ sudo -i -u postgres

After that, you have to enter into postgres prompt using the below command.

$ psql

Now, you will be logged in and you can perform all the database management operations.

To exit the PostgreSQL, use the below command.

postgres=# \q

After that, you will get back to the Ubuntu terminal.

Using Postgres account without switching

You can also run the Postgres commands using the sudo.

Here we will see an example for that. Let us login to the Postgres using the following command.

$ sudo -u postgres psql

You will be logged into the Postgres immediately and you will be taken to the Postgres command prompt.

Exiting from the Postgres is the same as the previous example.

 postgres=# \q

The above command will get back to you to the terminal.

Creating Roles in Postgres

Now, the role is just configured and you can create new roles using createrole.

Also, we will use the --interactive to define values for that role.

postgres@server:~$ createuser --interactive

If you want to do the same without switching the accounts, you can use the below command.

$ sudo -u postgres createuser --interactive

You will be prompted to answer for few requirements. Enter all of them as per your choice to create a role.

Output
Enter name of role to add: selva
Shall the new role be a superuser? (y/n) y

If you want to add more value to the roles, you can some additional flags. Here is such one flag. Just try this flag for your postgre role.

$ man createuser

Creating a New Database

In Postgres, if you create a new role, it will be tried to connect with a database with the same name as the role.

Here we have created a role called selva. Also, let us create a database with the same name as selva using the createdb.

If you are inside the postgres interface, then use the below command.

postgres@server:~$ createdb selva

To do the same job from your normal account, just follow this command.

$ sudo -u postgres createdb selva

Logging in with the Ident authentication

In order to login with ident authentication, you will need to have a Linux user account name which matches the Postgres role name.

If you don't have one, then you can create one in your Ubuntu server using the following command.

$ sudo adduser selva

After the account creation, if you want to switch to the database using the following command.

$ sudo -i -u selva
$ psql

In short form, you can also use the below command to do the same job.

$ sudo -u selva psql

After that, you will be logged in automatically. If you want to connect to any other database, just follow this command.

$ psql -d postgres

After logging in you can check your current connection information using the below command.

selva=# \conninfo

The output will be like

You are connected to database "selva" as user "selva" via socket in "/var/run/postgresql" at port "5432".

You can use this command to connect with non-default databases with non-default users.

Create and Delete tables

The main thing in database tables. The database is entirely relying on Tables to store, retrieve and manipulate the data.

Here, we are going to see how to create a basic table in the Postgres Database.

CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);

This is the structure to create a table in Postgres.

Here, you will create a table and then you will define the fields on the table.

Also, you will add field length.

Here is the exact code to create a table. This is the example code. You can create one by yourself.

CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);

Here:

equip_id serial PRIMARY KEY:

Here the equip_id is declared as a serial type so that it can increment automatically.

Also, it was declared as a primary key. So, the values will be unique and it won't be null.

You can note down one thing.

I did not add the field length for both equip_id and install_date.

They don't require the field length since the type itself manages the filed length.

To view the table use the below command.

selva=# \d

The output will be

              List of relations
Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
public | playground              | table    | selva
public | playground_equip_id_seq | sequence | selva
(2 rows)

In the above table, you can also see playground_equip_id_seq. That is of the serial type.

The count for the column will be increased automatically.

If you want to see the table without sequence, use the below command.

selva=# \dt

You will get the following output.

    List of relations
Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
public | playground | table | selva
(1 row)

Basic Operations with Table in Postgres

Here, we are going to see how to perform some basic operation.

We are going to add the swing and slide. For that, we will call the table in the query.

You can add the slide and swing by giving the name to the column and data for those columns.

The command will be like this.

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');

== You have to follow some rules in creating queries. For example, you should not quote the column name, but at the same time, you have to quote the value which you are set to the column. ==

Also, we will not setup any value for the equip_id. The main reason is that the value itself can be generated automatically.

To see the information that we have added, just use the below command.

selva=# SELECT * FROM playground;

You will get below output.

equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
    1 | slide | blue   | south     | 2014-04-28
    2 | swing | yellow | northwest | 2010-08-16
(2 rows)

You can see the equip_id has been filled automatically.

If you want to remove any row from the table, using a condition. Below is the example for that.

selva=# DELETE FROM playground WHERE type = 'slide';

Here, we have made a condition that a row should be deleted if it has 'slide' in any of their field.

selva=# SELECT * FROM playground;

The output will be like

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
    2 | swing | yellow | northwest | 2010-08-16
(1 row)

Table Manipulation

If you want to add or delete a column in the table, you can follow the below structure.

In this example, I am going to show you how to add a column in the existing table which we have created.

Here is the command for that.

sammmy=# ALTER TABLE playground ADD last_maint date;

Now, the column would be added. Next check whether it is updated in table or not.

selva=# SELECT * FROM playground;

The output will be like

equip_id | type  | color  | location  | install_date | 
last_maint 
----------+-------+--------+-----------+--------------
+------------
    2 | swing | yellow | northwest | 2010-08-16   | 
(1 row)

Deleting the column also an easier task. You can just delete the column using the below structure in the example.

selva=# ALTER TABLE playground DROP last_maint;

Updating Data in a Table

We have seen how to add and delete columns and rows in the database.

But:

We have not seen how to update a field in the table. You can do this directly by referring the field.

Here is the example for that.

selva=# UPDATE playground SET color = 'red' WHERE type = 'swing'

You can check the changes by viewing the table.

selva=# SELECT * FROM playground;

The output will be like

 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
    2 | swing | red   | northwest | 2010-08-16
(1 row)

Conclusion

Now, you have learned how to install PostgreSQL on Ubuntu 16.04.

PostgreSQL has a lot of things to explore. One article is not enough to explain the basic things from PostgreSQL.

I am going to write a series of articles for PostgreSQL. To keep yourself updated, just use signup for the tutorial.

Also, if you have any doubt in this PostgreSQL guide or difficulty in installing PostgreSQL, let me know that in the comment.

I will help you to solve the issue.

Selvakumar
Author

Selvakumar

I am an Online Marketer and technology lover. I like to learn new things and share that with people.

Comments