How to Setup PostgreSQL Database Server and PhpPgAdmin in Ubuntu Server 13.10

This guide explains how to install and configure postgreSQL database server and PhpPgAdmin in ubuntu server 13.10. PostgreSQL is a powerful and reliable object-relational database system. It’s a great alternative for MySQL databse server. It is as easy to set up, performs better and offers far more features.

PostgreSQL running on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of community volunteers employed and supervised by companies such as Red Hat.

Installing PostgreSQL

By default PostgreSQl is available in Ubuntu repository. To install PostgreSQL in Ubuntu Server 13.10 is simple, just run the following commands:

sudo apt-get update

sudo apt-get install postgresql

You can also install postresql by adding the following PPA.

sudo apt-get install python-software-properties

sudo add-apt-repository ppa:pitti/postgresql

sudo apt-get update

sudo apt-get install postgresql

If you’re getting an error message, make sure you already install libpq-dev.The libpq-dev package is for compiling wrappers/clients against libpq.

sudo apt-get install postgresql-9.1 libpq-dev

Configuring PostgreSQL

Enabling TCP connections. By default TCP/IP connections is disabled due to which users will not be able to access remotely PostgreSQL server from another computers. to enable it edit file /etc/postgresql/9.1/main/postgresql.conf.

sudo nano /etc/postgresql/9.1/main/postgresql.conf

Change “#listen_addresses = localhost” to “listen_addresses =192.168.1.8” it allowing remote access only from specific ip address, if you want remote access from any computers in your network set “localhost to listen_addresses =’*’ “

uncomment #password_encryption = on to password_encryption = on

Save and exit ( Ctrl + O, Ctrl + X), Restart postgreSQl daemon:

sudo /etc/init.d/postgresql restart

After editing file /etc/postgresql/9.1/main/postgresql.conf. you need to setup the root password PostgreSQL. In PostgreSQL, root user is postgres which by default, does not have any password. Enter following line in terminal to set a password for the default root user postgres:

$ sudo -u postgres psql

Now psql will ask for a new password twice. Enter the new password and continue. Type ‘\q’ and hit enter to quit.

To create a database the following command can be run from the terminal:

$ sudo -u postgres createdb mydb

Installing and Configuring PhpPgAdmin

phpPgAdmin is a php-based web application that provides a GUI interface for the postgresql. It performs a similar function to phpMyAdmin, which allows users to manipulate database information in a visual program in MySQL.

Execute the following command to installing PhpPgadmin in ubuntu server:

sudo apt-get install phppgadmin

By default you can only access phppgadmin locally. If you want to access remotely from another computers change the following file:

sudo nano /etc/apache2/conf.d/phppgadmin

Comment out the line:

127.0.0.0/255.0.0.0 allow from :: 1/128

uncomment the line:

allow from all

Create a symbolic link /var/www/phppgadmin which would point to /usr/share/phppgadmin

sudo ln -s /usr/share/phppgadmin /var/www/

Restart apache2 daemon:

sudo service apache2 restart

Probably when trying to log into phppgadmin you receive the following message Login disallowed for security Reasons and this can be solved by changing a configuration variable in Archiving below

sudo nano /usr/share/phppgadmin/conf/config.inc.php

By default comes as :

$conf['extra_login_security']= true;

just switch to :

$conf['extra_login_security']=false;

Now, Access phpPgadmin using web browser from any computer in your network by typing in address bar http://host_or_ip/phppgadmin/. You should be able to login using any users you’ve setup in PostgreSQL. It will be appear like the following screenshot: