basic postgres sql

sudo apt-get install postgresql phppgadmin
sudo apt-get install pgadmin3

sudo su - postgres

createuser drupal
Shall the new role be a superuser? (y/n) y

psql -d postgres -U postgres

alter user postgres with password 'crazylongpassword';
or
# \password postgres

\q

edit the main PostgreSQL access control file
sudo vim /etc/postgresql/9.1/main/pg_hba.conf

# Database administrative login by Unix domain socket
local all postgres peer
local all drupal trust

restrict connections to allow only localhost
sudo vim /etc/postgresql/9.1/main/postgresql.conf

# - Connection Settings -

listen_addresses = 'localhost'


sudo service postgresql restart
* Restarting PostgreSQL 9.1 database server


Create a database

createdb -p 5432 -O drupal -U drupal -E UTF8 testingsiteone -T template0

Dropping the database

dropdb -p 5432 -U drupal testingsiteone

Dumping the database

pg_dump -p 5432 -h localhost -Fc -U drupal --no-owner testingsiteone > /tmp/testingsiteone_$(date +"%Y-%m-%d_%s").pgdump

Restoring the database

pg_restore -p 5432 -h localhost -Fc -d testingsiteone -U drupal --no-owner < /tmp/path-to-the-file.pgdump


http://www.pixelite.co.nz/article/installing-and-configuring-postgresql-91-ubuntu-1204-local-drupal-development

start server
sudo service postgresql start