Notes about PostgreSQL

Windows

• see http://www.postgresql.org/download/windows for current version
• eg. http://wwwmaster.postgresql.org/download/mirrors-ftp/binary/v8.3.7/win32/postgresql-8.3.7-1.zip

• see also http://jacqueschirag.wordpress.com/2007/08/15/installing-postgresql-on-windows-vista/

• don't install as a service

initdb
"postgres" -D "petra"
psql 
 CREATE USER tom WITH PASSWORD 'myPassword';

You might want to set the environment variables before creating the database:
set PGUSER=postgres
set PGPASSWORD=myPassword

or alternatively, write the file pgpass.conf; see http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html

nant recreateDatabase
nant resetDatabase

Linux

• if you get this error message when starting the OpenPetra server:
  • no pg_hba.conf entry for host "xx.yyy.zz.xx", user "petraserver", database "openpetra", SSL off
  • solution: add to file /etc/postgresql/8.3/main/pg_hba.conf:

host   openpetra  petraserver   xx.yyy.zz.xx/0   md5

• • and restart postgresql

Some notes

• drop database but tables are still there when creating the database again: [1]; reason: tables are in template1 db, and that is used as a template for creating the new database
• drop all tables in db: DROP SCHEMA public CASCADE; [2]
  • you should create a new schema public again (especially if this is the template1 db): CREATE SCHEMA public;

• Hide notice during nant recreateDatabase: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "..." for table "..."
  • http://archives.postgresql.org/pgsql-sql/2009-06/msg00006.php: alter role bubba set log_min_messages=error;
  • pitfalls: need to set this as user postgres, otherwise no permission to change role. Need to use double quotes "MyCapitalizedUsername" otherwise the role is not known