How to Install PostgreSQL on Debian 8
Index
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 :
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.
