Steps to install PostgreSQL, phpPgAdmin with PHP and managing PostgreSQL over Ubuntu 11.10


Here, I’m attempting to explain how to install PostgreSQL & phpPgAdmin briefly and an attempt to perform database dump and restore; which should be a basic functionality but is not present in pgadmin.

Here, I’m presuming that you already have a Linux machine with Ubuntu(likely v11.10) and installing the latest version of PostgreSQL(v9.1.1).


Steps to install PostgreSQL:

sudo apt-get install postgresql


sudo apt-get install postgresql-9.1

Steps to install GUI Administration application:

sudo apt-get install pgadmin3

Steps to install PHP based Web Administration site (like phpMyAdmin for MySQL database):

sudo apt-get install phppgadmin


Steps to configure PostgreSQL to access it via localhost:

Edit the file postgresql.conf as follows:

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

Un-comment the following line or by add it at the end of the file:

listen_addresses = 'localhost'

Save and close the file.

Edit the file pg_hba.conf as follows:

sudo vi /etc/postgresql/9.1/main/pg_hba.conf

Replace local all all ident sameuser with:

local   all         all                               md5

Changing root users password

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
ALtER USER postgres password ''


ALTER USER postgres with encrypted password ''

Creating a new User & a new Database

sudo -u postgres createuser -d -R -P new_username
// Enter Password for the created new user.
// Set the user permissions as admin or normal user
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” as itโ€™s owner.

Configure phpPgAdmin

Assuming that we have already installed phpPgAdmin by:

sudo apt-get install phppgadmin

Then, configure Apache:

sudo vi /etc/apache2/apache2.conf

Add following line at the end of the file:

# Include Phppgadmin
Include /etc/phppgadmin/apache.conf

Restart apache2 and postgresql to reflect changes

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


sudo service apache2 restart
sudo service postgresql restart

Access phpPgAdmin

via http://localhost/phppgadmin we can access phpPgAdmin in our browser. We can log-in by various users we created like(new_username).

Use GUI Administration application

Run following command in terminal:


Access from Terminal
Run following command in terminal:


Performing Dump or Restore of PostgreSQL database

Before performing the ff commands, you should log in as postgres first:

sudo su postgres

Creating Dumps:

//dump the roles
pg_dumpall -g -U postgres > roles.sql

Dump the schema

pg_dump -Fp -s -v -f schema.sql -U postgres 

Dump the data

pg_dump -Fc -v -f data.dump -U postgres 

How to restore?

psql -f roles.sql
psql -f schema.sql
pg_restore -a -d -Fc data.dump

Installing Extensions

sudo apt-get install postgresql-contrib postgresql-contrib-9.1

Downloading the postgresql-contrib packages will give the ability to use the following five commands:

psql  -c "CREATE EXTENSION tablefunc"
psql -c "CREATE EXTENSION fuzzystrmatch"
psql -c "CREATE EXTENSION pg_trgm"
psql -c "CREATE EXTENSION cube"
psql -c "CREATE EXTENSION dict_xsyn"


Cheers ๐Ÿ™‚

Leave a comment