PostgreSQL

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]