How to Install PostgreSQL 9.6 on Debian 8

To Install PostgreSQL 9.6 on Debian 8

Relational databases have become an essential tool for a multitude of services which ranges from online shopping to rocket launches. PostgreSQL, an open-source relational database, is one such database which is widely used among the webmasters for data storage purposes and for production servers. This tutorial will help you to install PostgreSQL database server on Debian 8.

Installing PostgreSQL

Configuring your apt repository is very essential for installing PostgreSQL. So, create a file at /etc/apt/sources.d/ postgeresql.list by using the following command.

root@linuxhelp:~# vim /etc/apt/sources.list.d/postgeresql.list

Add the following line according to your distribution (xenial, utopic, trusty, jessie, wheezy and etc)

deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main

Now, you need to import PostgreSQL packages signing key on your system. Use the following command to import key.

root@linuxhelp:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
OK

Once it is done, update repository list as follows.

root@linuxhelp:~# apt-get update
Hit http://security.debian.org jessie/updates InRelease
Hit http://security.debian.org jessie/updates InRelease                             
Hit http://security.debian.org jessie/updates/main Sources                           
Hit http://security.debian.org jessie/updates/contrib Sources                               
Hit http://security.debian.org jessie/updates/main amd64 Packages              
Hit http://security.debian.org jessie/updates/contrib amd64 Packages                          
Hit http://security.debian.org jessie/updates/contrib Translation-en
.
.
Ign http://apt.postgresql.org xenial-pgdg/main Translation-en
Fetched 98.8 kB in 5s (17.4 kB/s)
Reading package lists... Done

After the update, install Latest PostgreSQL Server in your Ubuntu system using following command.

root@linuxhelp:~# apt-get install postgresql-9.6
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following extra packages will be installed:
  libpq5 pgdg-keyring postgresql-client-9.6 postgresql-client-common postgresql-common
  postgresql-contrib-9.6
Suggested packages:
  locales-all postgresql-doc-9.6 libdbd-pg-perl
Recommended packages:
  sysstat
The following NEW packages will be installed:
  libpq5 pgdg-keyring postgresql-9.6 postgresql-client-9.6 postgresql-client-common postgresql-common
  postgresql-contrib-9.6
0 upgraded, 7 newly installed, 0 to remove and 95 not upgraded.
Need to get 6,280 kB of archives.
After this operation, 29.1 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
.
.
Processing triggers for libc-bin (2.19-18+deb8u6) ...
Processing triggers for systemd (215-17+deb8u5) ...

After installing PostgreSQL database server by default, it creates a user ‘ postgres’ with role ‘ postgres’ . It also creates a system account with the same name ‘ postgres’ . So to connect to postgres server, login to your system as user postgres and connect database.

root@linuxhelp:~# su - postgres
postgres@linuxhelp:~$ psql
psql (9.6.2)

Type " help" for help.

postgres=# help

Now you are logged in to PostgreSQL database server. To check login info use following command from database command prompt.

postgres=# conninfo

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

postgres=# 

To disconnect from PostgreSQL database command prompt just type below command and press enter. It will return you back to debain command prompt.

postgres=# q

Your PostgreSQL installation has been completed successfully. PostgreSQL has several uses as it is designed with most of the SQL standard. It also has ACID transactions, and supports foreign keys and views.