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).

Installation

Steps to install PostgreSQL:

sudo apt-get install postgresql

or

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

Configuration

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 ''

(or)

ALTER USER postgres with encrypted password ''

\q

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

or

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:

pgadmin3

Access from Terminal
Run following command in terminal:

psql

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"

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
http://bdhacker.wordpress.com/2011/05/06/postgresql-phppgadmin-php5-ubuntu/
http://czetsuya-tech.blogspot.in/2012/06/how-to-setup-postgresql-in-ubuntu-1110.html
http://forums.pragprog.com/forums/202/topics/10089

Cheers :)