Quick & Dirty Guide to PostgreSQL

Introduction

Windows Setup

 

Watch out for issues when installing PG:

Linux Setup

  1. Create a directory where PostgreSQL data will live: mkdir -p /var/pgsql/data
  2. Create a user account that will be used by PGSQL to run: useradd -c "PostgreSQL account" -d /var/pgsql/data postgres
  3. Remove all unnecessary files copied by useradd: rm -f /var/pgsql/data *; rm -Rf /var/pgsql/data/* ; rm -Rf /var/pgsql/data/.*
  4. Change ownership: chown -R postgres.postgres /var/pgsql
  5. Install postgresql-7.0.3-8.i386.rpm, postgresql-7.0.3-8.i386.rpm, and postgresql-devel-7.0.3-8.i386.rpm
  6. su - postgres
  7. initdb -D /var/pgsql/data/
  8. postmaster -D /var/pgsql/data/ < /dev/null >> /var/pgsql/data/server.log 2>&1 &
  9. Add a startup script in /etc/rc.d/init.d/ so that PostgreSQL starts automatically
  10. Create a dummy database, and log to psql: createdb testdb ; psql testdb; (\q to exit)

Basic commands

Creating users

create user jdoe with password 'secret';

Changing a user's password

alter user jdoe with password 'admirer';

Assigning a user to a group

insert into pg_group (groname, grosysid, grolist) values ('posthackers', '1234', '{5443, 8261}'); grant insert on foo to group posthackers;

Deleting a user

drop user jdoe;

Creating databases

(from the OS shell) createdb testdb;

(within PostgreSQL) create database testdb;

Deleting databases

(from the OS shell) dropdb testdb (within a PostgreSQL session) drop database testdt;

Resources