PostgreSQL configuration

Note: if you know postgres admin credentials but do not know the configuration details of an existing installation, this is a very handy DDL that will list all configration information:

select category, name, seting from pg_settings order by 1; 

1. Installation

1. sudo apt-get install postgresql postgresql-client postgresql-contrib (This installs the database server/client, some extra utility scripts)
2. sudo apt-get install pgadmin3 (This installs the pgAdmin GUI application for working with the database)
3. References
   1. https://help.ubuntu.com/community/PostgreSQL
   2. http://hocuspokus.net/2007/11/install-postgresql-on-ubuntu-710

2. Configuration

1. To allow localhost connections to all databases from any user without password authentication, edit your pg_hba.conf file to include these entries.
   
   1. local all all trust  (unix installs only) 
   2. host all 127.0.0.1/32 trust
   3. host all all ::1/128 trust (windows only - ipv6 section) 
2. Comment IDENT-based authentication
   
   1. Otherwise, the following error message will be popped up: "psql: FATAL:  Ident authentication failed for user "[userid]" "

3. Files

1. /etc/postgresql/8.3/main/pg_hba.conf

4. Commands

1. Server management
   
   1. sudo /etc/init.d/postgresql-8.3 stop
   2. sudo /etc/init.d/postgresql-8.3 start
   3. sudo /etc/init.d/postgresql-8.3 restart
2. Connect to a database
   
   1. psql -U postgres template1
3. Create user
   
   1. Login using postgres
   2. Run SQL: 
      
      1. create user [userid] WITH CREATEDB;
      2. GRANT USAGE ON LANGUAGE plpgsql TO tdar;
4. Create database
   
   1. Login with [userid]: psql -U [userid] template1
   2. Run SQL: create database [db1]