Notes about PostgreSQL

Windows

Running the Installer

• see http://www.postgresql.org/download/windows. OpenPetra uses PostgreSQL 9.3.

• 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.)

pgAdmin III

The installer installs a version of pgAdmin III. However, a newer version of pgAdmin III might be available here, so you might want to check whether this is the case and install a newer version than the one that got installed with the PostgreSQL installer.

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/9.3/interactive/libpq-pgpass.html

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

initdb -D "openpetra" <ENTER> 

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

postgres -D "openpetra" <ENTER> 

This will start the PostgreSQL server using the cluster 'openpetra' 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 "openpetra" 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.3'. You would check this and change this using Windows Explorer. (See this forum post for details.)

Instead of using the command line you can also use the program pgadmin3. Add there first a new server-connection (Name + Host = localhost, User: postgres, Password: the one that you entered during postgres-installation). Then in the left window-part go to "databases" and add a new one called "openpetra" with owner "postgres".

Creating a user

Open a new command window and again go to the Postgres program folder (e.g. C:\Program Files\PostgreSQL\9.3\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

Configuring OpenPetra

You must create a config file so that OpenPetra knows to use PostgreSQL. The file should be named OpenPetra.build.config and should be added to your root directory (note: OpenPetra.build is a different file that resides in the root directory). If OpenPetra.build.config already exists, then add your entries to the existing file.

The PostgreSQL config is a set of XML statements which specify the parameters to be used by OpenPetra. The file structure should be similar to the following example:

<?xml version="1.0"?>
<project name="OpenPetra-userconfig">
    <property name="PostgreSQL.Version" value="9.3" />
    <property name="PostgreSQL.exe" value="D:\Program Files\PostgreSQL\9.3\bin\psql.exe" />
    <property name="DBMS.Type" value="postgresql" />
    <property name="DBMS.DBName" value="myOpenPetraDb" />
    <property name="DBMS.UserName" value="myUserId" />
    <property name="DBMS.Password" value="myPassword" />
</project>

Notes:

• PostgreSQL.Version - Optional. Specifies the database version that is installed. Required for Windows if the version of PostgreSQL that you use deviates from the default, which is "9.3" (defined in \inc\nant\OpenPetra.tobe.migrated.xml).
• PostgreSQL.exe - Optional. Defines the path to the PostgreSQL Interactive Terminal application. This entry is not required if PostgreSQL has been installed to the default location ("Program Files" or "Program Files(x86) for 32-bit and 64-bit Windows respectively).
• DBMS.Type - Required. Identifies that the database type is PostgreSQL.
• DBMS.DBName - Optional. Specifies the name of the database. Not required if standard parameters are used.
• DBMS.UserName - Optional. Specifies the user name for the OpenPetra logon. Not required if standard parameters are used.
• DBMS.Password - Optional. Specifies the user password for the OpenPetra logon. Not required if standard parameters are used.
• See also User defined configuration parameters with OpenPetra.build.config

When Encountering an Error on OpenPetra Server Startup

If you get the following error message when starting the OpenPetra server: 'Exception occurred while establishing a connection to Database Server. ... System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.' and you are sure that the PostgreSQL DB Server's IP address and Port are correct and you are sure that the username and the password for connecting to the OpenPetra DB are right then it is most likely that the authorisation for your PC that is trying to access the PostgreSQL DB Server is missing from the PostgreSQL DB Server's pg_hba.conf file.

Solution:

• Open the file /etc/postgresql/9.3/main/pg_hba.conf (on Debian Linux) or /var/lib/pgsql/data/pg_hba.conf (on CentOS and RedHat Linux) or C:\Program Files\PostgreSQL\9.3\data\pg_hba.conf (on Windows) in a text editor and add a line like the following at the end of the file:

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

- replacing xx.yyy.zz.xx with the IP Address of the PC that you are trying to start the OpenPetra Server on.

• Restart the PostgreSQL DB Server from the command line (or if you use pgAdminIII: right-click on the PostgreSQL DB Server in the Object Browser and choose 'Reload configuration').
• Restart the OpenPetra Server. The error should be gone. If it isn't, check that the PostgreSQL DB Server's IP address and Port are correct and you are sure that the username and the password for connecting to the OpenPetra DB are right.

Additional notes regarding pg_hba.conf:

• at least in PostgreSQL 8.x, the order of the lines in that file is important. This means, I had to add the line for the petraserver user before the "host all" line.
• for local access with psql, I need the row before local all all ident sameuser

local   openpetra petraserver   md5

• if /etc/init.d/openpetra init gives this error:

psql: FATAL:  no pg_hba.conf entry for host "::1"

• solution: add to the top of pg_hba.conf:

host all all ::1/128 md5

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 recreateDatabase then please look here

Problems accessing a restored DB from OpenPetra

If you have just restored a DB from backup and then get problems that PetraServerConsole reports permission problems on every DB Table it tries to access then the issue is that the 'petraserver' user hasn't got rights to access the imported DB Tables. Do the following to rectify this:

1) Issue the following SQL

 select 'alter table ' || table_name || ' owner to petraserver;' from information_schema.tables where table_schema = 'public';

2) Select all the output rows and copy the whole output of that SQL command to the clipboard

3) Paste the clipboard content into a text editor (e.g. Notepad++)

4) Replace all occurrences of " (double quotes) with nothing

5) Copy the whole text to the clipboard

6) Paste the text from the clipboard into a new SQL window and execute the SQL.

7) Return to the first SQL window and make a new query as follows

 select 'alter table ' || sequence_name || ' owner to petraserver;' from information_schema.sequences where sequence_schema = 'public'

8) Execute this query and repeat steps 2) through 6)

The approach is taken from this Forum post (user 'rkj')!

Add developer user for test database in office

on openpetra server:

su - postgres
  psql openpetratest
    create user entwickler with password 'secret';
    grant all on all tables in schema public to entwickler;
    grant all on all sequences in schema public to entwickler;
    \q
  exit
vi /var/lib/pgsql/9.3/data/pg_hba.conf
  add line: 
    host  openpetratest entwickler  127.0.0.1/0   md5
service postgresql-9.3 restart