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

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