How to Install PostgreSQL on Debian 8

1. Introduction

PostgreSQL pronounced as “post-gress-Q-L” is a free and open source object-relational database management system. It is developed by a group of companies and some individuals. It supports almost all relational database features and offers a few unusual features that are normally absent in other RDBMS engines. Commonly supported objects include views, stored procedures, indexes, triggers and object-defined data types.

It is used by several web programming languages including PHP, and Python. These programming languages make is extremely easy to connect to a PostgreSQL database. It is also used for many content management scripts. It is very easy to store and retrieve information using SQL commands in PostgreSQL.

2. Advantages

  • It is a very powerful RDBMS system.
  • Cross Platform.
  • High stability and reliability.
  • It is highly programmable as well as extendible.
  • It has a strong third party support.
  • It supports a large number of data types.

3. Requirements

If you have a freshly installed debian 8 server with root access you are ready to install PostgreSQL 9.4.

In this article, We are going to install PostgreSQL and give you some introduction. So let’s start with the installation.

4. Installation

Here, We are going to install the PostgreSQL version. So we need to update the system with the latest updates.

Update the apt packages :


# apt-get update

You will see a message like the one below which means all the packages are been updated :

Reading package lists... Done

Now, Install PostgreSQL packages using apt-get :

# apt-get install postgresql-9.4 postgresql-client-9.4

At the end of the installation, you will get the information of configuration files, ports and further details. It will be like the one below.

Creating config file /etc/postgresql-common/createcluster.conf with new version

Creating config file /etc/logrotate.d/postgresql-common with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Setting up postgresql-9.4 (9.4.5-0+deb8u1) ...
Creating new cluster 9.4/main ...
  config /etc/postgresql/9.4/main
  data   /var/lib/postgresql/9.4/main
  locale C
  port   5432
update-alternatives: using /usr/share/postgresql/9.4/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for libc-bin (2.19-18+deb8u1) ...
Processing triggers for systemd (215-17+deb8u2) ...

Thus the installation is completed.

Now we can confirm the installation using some commands. They are given below :

# root@name-debian8:~# ps -ef | grep postgre

This will provide you an output like the one below :
Postgresql

We can also check its status by the following commands :

~# netstat -tnulp | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      17148/postgres  
tcp6       0      0 ::1:5432                :::*                    LISTEN      17148/postgres 

Thus we can confirm that the PostgreSQL is properly installed and running. Now, We can check accessing the database.

5. Accessing the Database

By default, a user and a database named postgres will be created during the installation. Also, We can only access the database by switching to that user postgres.

Switch to the PostgreSQL user by following the commands :

# su - postgres

Then get into the PostgreSQL console using the command pgsql :

postgres@name-debian8:~$ psql
psql (9.4.5)
Type "help" for help.

postgres=# \q
postgres@name-debian8:~$ exit
logout

Now, You can exit from psql console by using \q and then exit command to logout from PostgreSQL.

6. Creating New Roles and Database

A role is an user, group or both that can own database objects and have database privileges. This will adds a new role to a PostgreSQL database cluster. Let’s see how to create a role.

Log in as the user postgres :

# su - postgress

Then use the following command to create a new role :

createuser --interactive

It will be an interactive session and ask you for the name of the role and whether it should be a superuser.

postgres@name-debian8:~$ createuser --interactive
Enter name of role to add: testpsql
Shall the new role be a superuser? (y/n) y

Thus we have successfully created a role.

In the same postgres terminal. Create a database using the command below. But please note that the database name should be same as the role.

postgres@name-debian8:~$ createdb testpsql 

The new database testpsql is now created.

7. Connecting to PostgreSQL

We have created an user and database called testpsql. Now, log in as the user testpsql and connect to the database.

# su - testpsql
No directory, logging in with HOME=/
$ psql
psql (9.4.5)
Type "help" for help.

testpsql=# 

Thus we have successfully connected to the database.

8. Conclusion

Thus we have installed the PostgreSQL in debian. You can check my upcoming post to know more about the working of PostgreSQL.

Leave a Reply

Your email address will not be published. Required fields are marked *