Introduction

PostgreSQL is a powerful open source object-relational database system.

It has a proven architecture with a reputation for reliability, data integrity, and correctness.

It runs on all major operating systems, including Linux, UNIX, and Windows.

  1. It is fully ACID-compliant, and
  2. Has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

Installation

  1. sudo apt-get install postgresql
  2. sudo apt-get install postgresql-client
  3. sudo apt-get install pgadmin3
  4. Replace the following lines with /etc/postgresql/9.1/main/pg_hba.conf

# IPv4 local connections:

host        all                 all                 127.0.0.1/32                md5

with                

# IPv4 local connections:

host        all                 all                 127.0.0.1/32                trust

  1. Test from command line
  1. psql -U postgres -h localhost
  2. this should allow you to lo

KnowHow

  1. connect to a local database
  1. psql <database_name> <user/role id>
  1. ex: psql course_player_development adaptive
  1. provide the password when prompted
  1. describe a object - ex: table, sequence
  1. \d user_assessments
  1. list all objects
  1. \d
  1. get the next value of a sequence
  1. select nextval('grader_courses_id_seq');
  1. alter a sequence to start from a number
  1. ALTER SEQUENCE geopoints_id_seq RESTART with 1692;
  1. Create role
  1. CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1';
  1. Create database
  1. CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';
  1. Connect to a database
  1. \c demodb1
  1. Execute a script file
  1. \i ~/Downloads/ume.sql
  1. Allow user to access database
  1. grant connect on database atmosphere to chat;
  1. Grant all privileges on a database
  1. grant all on database atmosphere to chat;
  1. Grant all privileges on a table
  1.  grant all on ume_user to chat;

References

  1. http://www.ibm.com/developerworks/opensource/library/os-postgresecurity/index.html