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:
Link Reference:
Tags: #Database #phpPgAdmin #PostgreSQL #Ubuntu Server 13.10