Notes about PostgreSQL

Windows

Running the Installer

• 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/

• You might choose to not install a Windows Service for running the PostgreSQL Server, if that option is present in the installer. (It is a personal choice whether you want to run PostgreSQL as a Service or from the Command Line.)

Initialising the DB Cluster and starting the DB Server

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

From the Postgres program folder (e.g. C:\Program Files\PostgreSQL\9.0\bin\) run the following separate commands in a command window:

initdb -D "petra" <ENTER> 

This will create a new Database Cluster called 'petra' (for details on what this is for see initdb command in the PostgreSQL Documentation).

postgres -D "petra" <ENTER> 

This will start the PostgreSQL server using the cluster 'petra' from the command line. After the command executed successfully, this command window is then running the PostgreSQL server until the PostgreSQL server is stopped and the command window can therefore not accept any further commands.

• In case you get an error message 'Execution of PostgresSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information how to properly start the server': You need to run the command in a command window that is not associated with your current windows user, as this windows user has administrative privileges. To achieve that, open the command window using the 'Run...' menu item from the start menu and enter the following: runas /user:postgres cmd. You will be asked to enter the username for the postgres user. After entering that, execute the postgres -D "petra" command in this command window. The command should not give the error message anymore. (See this forum post for details.)
• In case you get an error message 'FATAL: could not create lock file "postmaster.pid": Permission denied': ensure that the user that the PostgreSQL server is running under (usually 'postgres') has full access permissions to the folder that is above the 'data' directory, e.g. 'C:\Program Files\PostgreSQL\9.0'. You would check this and change this this using Windows Explorer. (See this forum post for details.)

Creating a user

Open a new command window and again go to the Postgres program folder (e.g. C:\Program Files\PostgreSQL\9.0\bin\). Execute the following command there:

psql -c "CREATE USER mywindowsusername WITH PASSWORD 'anypassword';" <ENTER>

• Please replace 'mywindowsusername' with the username that you log in to Windows when developing OpenPetra to avoid problems at a later stage when you will create the OpenPetra DB using the nant recreateDatabase command.
• You can choose any password you want for that PostgreSQL user, it doesn't need to be your real windows password - and in fact it shouldn't, as you will need to put it in plaintext into the OpenPetra.build.config file at a later stage.

Instead of executing the CREATE USER command you could also use the program pgAdmin. When doing that, ensure that the new user has privileges to create a new database and for creating roles.

When you got to this point you can follow the instructions here to create the OpenPetra Database in the PostgreSQL cluster.

In case you forgot your postgres-Password do fe. the following:

- Uninstall PostgreSQL
- execute the following command on the windows command line: NET USER postgres /DELETE
- Reinstall PostgreSQL

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

• If you get problems logging in to the database via nant recreatDatabase then please look here