Installing PostgreSQL Ubuntu

Postgresql ubuntu 13.04

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:

if loging failed

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

# Database administrative login by Unix domain socket

local   all             postgres                                ident (local all postgres ident)

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     peer

# IPv4 local connections:

#host    all             all             127.0.0.1/32            md5

host     all             all             10.2.0.0/16             md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local   replication     postgres                                peer

#host    replication     postgres        127.0.0.1/32            md5

#host    replication     postgres        ::1/128                 md5

The response of staff is correct, but if you want to further automate can do:

$ sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"

Done! You saved User = postgres and password = postgres.

sudo /etc/init.d/postgresql restart

sudo /etc/init.d/apache2 start

if page not found in browser

sudo cp /etc/apache2/conf.d/phppgadmin /etc/apache2/conf-enabled/phppgadmin.conf
sudo /etc/init.d/apache2 restart