PostgreSQL with PHP in Ubuntu: Step-by-Step How To!

This document is for latest version of PostgreSQL: 8.4, but it should work in any newer versions, as long as you change the commands appropriately, replacing the version mentioned with correct version 🙂

Installation

Install PostgreSQL:

sudo apt-get install postgresql

Install GUI Administration application:

sudo apt-get install pgadmin3

Install PHP based Web Administration site (like phpMyAdmin for MySQL database):

sudo apt-get install phppgadmin

I found this helpful, because it installed all dependant packages required to access postGRE database from PHP 😉

Configuration

Configure so that you can access via localhost:

gksudo gedit /etc/postgresql/8.4/main/postgresql.conf 

It witll open the file for editing, Add following line at the end of the file:

listen_addresses = 'localhost'

Save and close the file. Open another file for editing:

gksuso gedit /etc/postgresql/8.4/main/pg_hba.conf

Replace “local all all ident sameuser” with:

local   all         all                               md5

Change Password for root user

In PostGRE, root user is “postgres” which by default, does not have any password. Enter following line in terminal to set a password for it:

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';
\q

Create a new User & a new Database

sudo -u postgres createuser -d -R -P new_username
sudo -u postgres createdb -O new_username new_database_name

This will create a new user, with username “new_username” and create a new database “new_database_name” and set “new_username” it’s owner.

Configure phpPgAdmin

I assume you already installed phpPgAdmin by:

sudo apt-get install phppgadmin

Then, configure Apache:

gksudo gedit /etc/apache2/apache2.conf

Add following line at the end of the file:

Include /etc/phppgadmin/apache.conf

All done! Restart to reflect changes…

sudo /etc/init.d/apache2 restart
sudo /etc/init.d/postgresql-8.4 restart

Access phpPgAdmin

type http://localhost/phppgadmin in your browser & log in by the username you just created (new_username)

Use GUI Administration application

Run following command in terminal:

pgadmin3

Access from Terminal:

psql

References

https://help.ubuntu.com/8.04/serverguide/C/postgresql.html

http://solyaris.wordpress.com/2008/08/09/setup-postgres-in-ubuntu/

https://help.ubuntu.com/community/phpPgAdmin