Install PostgreSQL Server-Client on Ubuntu 14.04

PostgreSQL is a powerful, open source object-relational database system. With more than 15 years of active development and a proven architecture, it has become one of the most preferred databases to store millions of records of various types. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows and has bindings for many programming languages like C/C++, Java, .Net, Perl, Python, Ruby, Tcl. It is fully ACID compliant and has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

In this blog, I am going to discuss about the necessary steps to install PostgreSQL server and client on Ubuntu 14.04 machines. The steps are totally different from MySQL, the most preferred database among web hosting community. If you want to install the older version of postgres, you can get it from ubuntu repository. But if you want to install the latest version, you need to get it from PostgreSQL Apt Repository. To install the older version from ubuntu repository, use the apt-get (or other apt-driving) command: sudo apt-get install postgresql-9.4

The repository contains many different packages including third party addons. The most common and important packages are :

• postgresql-client-9.4 – client libraries and client binaries
• postgresql-9.4 – core database server
• postgresql-contrib-9.4 – additional supplied modules
• libpq-dev – libraries and headers for C language frontend development
• postgresql-server-dev-9.4 – libraries and headers for C language backend development
• pgadmin3 – pgAdmin III graphical administration utility

If you want to install the latest version of PostgreSQL (version 9.5), you need to get it from PostgreSQL Apt Repository. This is the recommended method for the installation because this way you don’t have to worry about manual upgrade. This repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL. To install from PostgreSQL Apt Repository, do the followings:

• Create the file /etc/apt/sources.list.d/pgdg.list, and add the highlighted line for the repository : deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main . To create the file,  type the following command on the terminal –  sudo gedit /etc/apt/sources.list.d/pgdg.list . This will open an empty file pgdg.list in Gedit and then you can add the above line for repository to the file.
• Use the following lines of commands one-by-one to import the repository key from https://www.postgresql.org/media/keys/ACCC4CF8.asc, update the package lists, and start installing packages:

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-9.5 pgadmin3

This way you will install PostgreSQL server, PostgreSQL client, and pgadmin3 on your Ubuntu 14.04 machine. To check the installed components, type the command on terminal : sudo dpkg -l | grep postgres 

praveen@praveen-Lenovo-G510:~$ sudo dpkg -l | grep postgres
ii  pgdg-keyring                                          2014.1                                              all          keyring for apt.postgresql.org
ii  postgresql-9.5                                        9.5.0-1.pgdg14.04+2                                 amd64        object-relational SQL database, version 9.5 server
ii  postgresql-client                                     9.5+172.pgdg14.04+1                                 all          front-end programs for PostgreSQL (supported version)
ii  postgresql-client-9.5                                 9.5.0-1.pgdg14.04+2                                 amd64        front-end programs for PostgreSQL 9.5
ii  postgresql-client-common                              172.pgdg14.04+1                                     all          manager for multiple PostgreSQL client versions
ii  postgresql-common                                     172.pgdg14.04+1                                     all          PostgreSQL database-cluster manager
ii  postgresql-contrib-9.5                                9.5.0-1.pgdg14.04+2                                 amd64        additional facilities for PostgreSQL

By default, PostgreSQL server should be running on your machine. If not, type the command : sudo service postgresql start to start the server. To stop: sudo service postgresql stop and to restart: sudo service postgresql restart

Now to access the postgres database server for the first time, type sudo -u postgres psql postgres or sudo -u postgres psql . It will take you to the postgres prompt and then you can set password for the default user postgres using command \password postgres .

praveen@praveen-Lenovo-G510:~$ sudo -u postgres psql postgres
psql (9.5.0)
Type "help" for help.

postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=# 

Hope it is helpful. 😯 😯 😯