Database Configuration

]project-open[ has been developed primarily using PostgreSQL   as the database back end for information storage and retrieval.  We are looking for companies who are looking to sponsor an effort to support Oracle, contact us for more information.

There are three different options available for the coupling relationship that links ]project-open[ to PostgreSQL.

1. Run PostgreSQL on a Windows Machine using CygWin . The ]project-open[ installer, by default, installs a PostgreSQL database as part ­of the CygWin Unix environment. This configuration is convenient because everything works “out of the box”.  However, the performance of PostgreSQL on CygWin is not as optimal as the other options.


2. Native PostgreSQL.  The “native” version of PostgreSQL (starting with version 8.0) on Windows performs considerably better then the CygWin version. We highly recommend this option for any productive use of ]project-open[.   
­

3. Run PostgreSQL on a separate database server computer, possibly even with a different operating system such as Linux or Solaris and then link ]project-open[ to this server.

­

• PostgreSQL Security Configuration
• PostgreSQL "Vacuum" Maintenance
• PostgreSQL Database Backup

­ 

PostgreSQL Security Configuration

PostgreSQL security configurations are controlled by a config file found at:

• Linux - "var/postgresql/data/pg_hba.conf"
  
• Windows - "C:\ProjectOpen\ cygwin\var\postgresql\data\pg_hba.conf"
  

The default configuration ]project-open[ uses is:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

local   all         all                                             trust

­In this configuration, the database will allow full access to all data for all local users of the server computer while blocking the access for anybody not working locally on the computer.  This setup is very convenient for our ]project-open[ demo server where we cannot predict the name of the local users.  However since you will more than likely be able to know every user involved and using ]project-open[ (your list of employees and customers) you may want to change these settings for a productive installation.

Please see the PostgreSQL documentation  for more details.

PostgreSQL "Vacuum" Maintenance

“Vacuuming” is the PostgreSQL name for performing database maintenance. Periodic maintenance is important for the overall performance of PostgreSQL, which can degrade considerably otherwise.  The default ]project-open[ Windows installation does not include a periodic scheduling of the “vacuum” command.  You can either:

1. Manually execute the CygWin “vacuumdb” command periodically:
2. You can configure a Windows “Scheduled Task” to execute “ProjectOpen-vacuum.bat” every day
3. You can configure the CygWin “cron” scheduler to execute the command.
   

On Linux systems configure the "Crontabs" daemon to execute the periodic maintenance.

# Full PostgreSQL vacuum every night
20 3 * * 0 su - postgres -c "/usr/bin/vacuumdb -a -f" >> /var/log/pg_vacuumdb.log 2>&1

PostgreSQL Database Backup

 Within ]project-open[ you can manually perform a backup using the Backup and Restore admin page.  However we recommend setting an automatic database backup process.  In Windows 

1. Manually execute the CygWin “pg_dump” command periodically:
2. You can configure a Windows “Scheduled Task” to execute ­“ProjectOpen-pg_dump.bat” every day
3. You can configure the CygWin “cron” scheduler to execute the command.

In Linux configure the "Crontabs" daemon to execute the periodic backup.

# Backup PostgreSQL "projop" database every night
20 3 * * 0 su - projop -c "pg_dump -c -O -F p -f /web/projop/filestorage/backup/pg_dump.`/bin/date +\%Y\%m\%d.\%H\%M`.sql " > /var/log/pg_backup.log 2>&1

Reference

• System Configuration 

• PostgreSQL documentation