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 🙂
