Operations and Maintenance Guide


About Operations & Maintenance

System administration includes all processes to keep the application running during the application life cycle.
SysAdmin tasks include:

  • Regular review of log files and security checks
  • Subscription to the SourceForge “Open Discussions” and “News” forum in order to keep up to date with ]po[ security
  • Taking ]po[ backups off site

Operations & Maintenance processes are necessary to keep a software application up and running. The complexity of these processes varies with the size of the company:

  • Small office or home office (SOHO) Companies (<10 Users) Most of this manual is overkill for a home office. The basic principles apply, but the procedures are becoming much easier. Operations & Maintenance are basically reduced to the periodic use of the "Automatic Software Update Services" (ASUS) that is built into ]project-open[ (see chapter 3). This service is similar to the "Windows Update Service" from Microsoft and doesn't require many system administration skills.
  • Small Companies (<20 Users) Small companies usually have a dedicated System Administrator for the maintenance of PCs and the local area network. This SysAdmin can use the "Automatic Software Update Services" (ASUS) to update the system, similar to SOHO companies.
  • Larger Companies (>20 Users) Larger companies will probably have to implement the entire scheme. Senior management should control that the processes are handled correctly, in particular the testing phase on the Staging Server.

Simplified Operations & Maintenance

Operations & Maintenance for SOHO and Small Companies can be reduced to the use of the ASUS (Automatic Software Update Service) that will be available in one of the next releases.

Operations & Maintenance Overview

The figure above provides an overview over all processes covered in this manual. The processes will be explained one-by-one in the following chapters.

The figure shows roles interacting with technical items such as the software application and hardware.


The figure above uses several "roles" to describe the responsibilities of the people related to a with ]project-open[ system:

  • SysAdmin:
    Keeps the server running: This should be the most technical person in your company
  • DbAdmin:
    Keeps the database running: Usually identical with the SysAdmin
  • Tester:
    Tests system changes: Double-checks the work of the SysAdmin, so it needs to be a different person.
  • HelpDesk:
    Maintains contact with ]project-open[: In charge of answering help requests from company's end users.
  • Development Team:
    Modifies the application: Performs changes in the application code. This can be performed either in-house or by ]po[

Hardware Environment

Also the following symbols are used in the figure above to refer to several types of servers:

The figure above represents three different servers that are used during the lifecycle of a ]project-open[ application:

  • "Development Server":
    The Development Team uses this server in order to fix bugs and to develop new product features. Every software developer usually runs his own development Server. A development server can be any desktop computer running ]po[. For example you may run a development server in your company if you are experimenting with the system.
  • "Staging Server":
    Also called "Testing Server": This server has the only purpose to test the application before it becomes used at the "Production Server". The Staging Server is frequently used as a backup system for the case that the Production Server fails.
  • "Production Server":
    Failures of the Production Server may cause financial loss to your company, so your Production Server should be equipped with a RAID disk array and a USB power supply. However, you don't need to buy a new computer for ]po[ because it perfectly OK to run ]po[ together with your file server on the same machine.


As a code repository, ]po[ uses CVS (Concurrent Versioning System). The repository is mirrored nightly to github.

Bug Fixes and Updates

Please see About Upgrades

Software Development

Please find additional information at About Developers

Staging Server

The "staging" process has the purpose to create a testing environment on the STAGING Server that is as close as possible to the PRODUCTION Server. Staging consists of two steps:

Executing Steps 2a and 2b:

  • Step 2a (getting the latest code):
    Please see “Getting the latest code from CVS” section below
  • Step 2b (getting the latest application data):
    Please see the “Importing data from another instance” and “Updating the Data Model” below.


A "Tester" performs testing on STAGING and verifies that the application is running correctly.  Afterwards the staging process is repeated on the PRODUCTION server.

Productive Setting

"Productive Setting" is a repetition of the staging operation on the production server.


Helpdesk operations assure you that all of your users can use the system productively. In general you want to optimize the following parameters:

  • Reaction time:  User requests should be answered as quickly as possible in order not to waste time.
  • Costs: You want to reduce the service costs of ]po[ or other help desk providers.

The best practice to optimize this reaction time / cost ratio is to use a staged system of:

  • 1st level support (end-user support, typically in-house, dealing with questions & training issues),
  • 2nd level support (support to the 1st level help desk, in-house our outsourced) and
  • 3rd level support (support to your 2nd level support, typically outsourced).

Application Log Files 


Default locations:

  • Main application log file: /web/projop/log/error.log
  • Http log: web/projop/log/projop.log
  • Mail log: /var/log/maillog

For log rotation please add the following lines to either 'root' or 'projop' crontab 

# Force logrotate every hour in order to keep logs small
49 */2 * * * /usr/bin/killall -HUP nsd



PostgreSQL Administration

]project-open[ has been developed primarily using PostgreSQL   as the database back end for information storage and retrieval. 

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 security configurations are controlled by a config file located 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 Database Backup

To create a new backup please go to your ]po[  application’s Admin -> Backup menu and click on “New Backup”. The backup file is written into the backup folder, which is configured in the Admin -> Parameters section. By default, it is set to /web/projop/filestorage/backup/.
In the same page you can:

  • Create a backup
  • Download the backup file
  • Delete backups
  • Restore backup data (Please use with care !)

It is no problem to execute the PostgreSQL backup during the execution of ]project-open[, you don't need to stop the server. However, depending on system resources available, the backup will slow down the system slightly, so please choose some calm moments during the day.

Manual backup

You can also manually create a backup from the Linux/CygWin command line:

su - projop
pg_dump --no-owner --clean --disable-dollar-quoting --format=p --file=/web/projop/filestorage/backup/pg_dump.[SERVER_NAME].projop.[YYYYMMMDD].[hhmmss].sql

We recommend that you use the name "pg_dump" for the backup dumps, and use our standard naming format. This format is used in all ]project-open[ installations and helps to limit errors when operating with database dumps from multiple servers.

Automatic Full PostgreSQL Backup

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
Below scripts create a backup of all PostgreSQL databases into the $exportdir directory.
Please choose the option you prefer (Backup only/Backup and SFTP files), copy the code into a /root/bin/export-dbs file and add the following line to your “crontab” in order to execute the script daily:
29 3 * * * /usr/bin/perl /root/bin/export-dbs

Example 1: export-dbs - backup only

# --------------------------------------------------------------
# export-dbs
# Copyright (c) 2008 by ]project-open[
# Licensed under GPL V2.0 or higher
# Author: Frank Bergmann 
# --------------------------------------------------------------

my $debug = 1;
my $psql = "/usr/bin/psql";
my $bzip2 = "/usr/bin/bzip2";

my $exportdir = "/var/backup";
my $logdir = "/var/log/backup";
my $pg_owner = "postgres";
my $computer_name = `hostname`;
my $time = `/bin/date +\%Y\%m\%d.\%H\%M`;
my $weekday = `/bin/date +%w`;


open(DBS, "su - $pg_owner -c '$psql -l' |");
while (my $db_line=) {
        $db_line =~ /^\s*(\w*)/;
        my $db_name = $1;
        next if (length($db_name) < 2);
        next if ($db_name =~ /^\s$/);
        next if ($db_name =~ /^List$/);
        next if ($db_name =~ /^Name$/);
        print "export-dbs: Exporting '$db_name'\n" if $debug;
        my $file = "$exportdir/pg_dump.$computer_name.$db_name.$time.sql";
        my $log_file = "$logdir/export-dbs.$db_name.$time.log";
        my $cmd = "su - $pg_owner --command='/usr/bin/pg_dump $db_name -c -O -F p -f $file'";
        print "export-dbs: $cmd\n" if ($debug);
        system $cmd;
        my $cmd2 = "su - $pg_owner --command='$bzip2 $file'";
        print "export-dbs: $cmd2\n" if ($debug);
        system $cmd2;

Example 2: export-dbs  (backup and sftp files)

Requires PERL libs:

  • IO::Pty
  • Net::SFTP::Foreign


# --------------------------------------------------------------
# export-dbs
# (c) 2016 ]project-open[

# Licensed under GPL V2.0 or higher
# Determines the list of databases available and backs up all DBs
# Author: Frank Bergmann 
# Author: Klaus Hofeditz 
# --------------------------------------------------------------

use lib '/root/perl5/lib/perl5/';
use Net::SFTP::Foreign;

# Constants, variables and parameters

# FTP Server
my $FTPServer = 'example.your-ftp-server.com';
my $FTPUser = 'george';
my $FTPPasswd = 'georges-password';
my $ftp_path = '/backup';

# Shell params
my $debug =     1;
my $psql =      "/usr/bin/psql";
my $pg_dump =   "/usr/bin/pg_dump";
my $bzip2 =     "/usr/bin/bzip2";

my $exportdir = "/var/backup";
my $logdir =    "/var/log/backup";

my $pg_owner = "postgres";
my $computer_name = `hostname`;

my $time = `/bin/date +\%Y\%m\%d.\%H\%M`;


# Establish SFTP connection
my $sftp = Net::SFTP::Foreign->new($FTPServer, user => $FTPUser, password => $FTPPasswd, autodie => 1);
$sftp->die_on_error("Unable to establish SFTP connection");
$sftp->setcwd($ftp_path) or die "unable to change cwd: " . $sftp->error;

# Get the list of all databases. psql -l returns lines such as:
#  projop       | projop       | UNICODE
open(DBS, "su - $pg_owner -c '$psql -l' |");

while (my $db_line=) {

        $db_line =~ /^\s*(\w*)/;
        my $db_name = $1;

        # Ignore system DB's
        next if (length($db_name) < 2);
        next if ($db_name =~ /^\s$/);
        next if ($db_name =~ /^List$/);
        next if ($db_name =~ /^Name$/);
        next if ($db_name =~ /^template0$/);
        next if ($db_name =~ /^template1$/);
        next if ($db_name =~ /^postgres$/);

        # #######################
        # Create Db dump, ZIP it and put it on FTP server
        my $file_pg_dump = "$exportdir/pgback.$computer_name.$db_name.$time.sql";
        my $cmd = "su - $pg_owner --command='$pg_dump $db_name -c -O -F p -f $file_pg_dump'";
        system $cmd;

        # bzip pgdump
        my $cmd2 = "su - $pg_owner --command='$bzip2 $file_pg_dump'";
        system $cmd2;

        # Upload pgdump
        my $file_pg_dump_bz2 = "$file_pg_dump.bz2";
        $sftp->put($file_pg_dump_bz2) or $newerr=1;
        if ($newerr) {
            logit("Error transferring $file_pg_dump_bz2");
        unlink($file_pg_dump_bz2) or logit("$! can't unlink $file_pg_dump_bz2");

        # #######################
        # Tar the entire web server (except for filestorage/backup) and put it on FTP server.
        my $file_webback = "$exportdir/webback.$computer_name.$db_name.$time.tgz";
        my $cmd3 = "tar --exclude='/web/$db_name/log' --exclude='/web/$db_name/filestorage/backup' -c -z -f $file_webback /web/$db_name/";
        system $cmd3;

        # Upload
        $sftp->put($file_webback) or $newerr=1;
        if ($newerr) {
            logit("Error transferring $file_webback");
        unlink($file_webback) or logit("$! can't unlink $file_webback");

PostgreSQL "Vacuum" Maintenance (Version < 8.4 only)

“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

Loading a Backup Dump into the Database

This step allows you to restore a system from a previously created backup dump.

Restore Using the ]po[ Administration Screen

To restore a previously created backup dump please go to your ]po[ application’s Admin -> Backup menu and click on the “Restore” link of one of the shown backups. Please Note: This procedure only works with PostgreSQL 8.0 or higher and for backup dumps created in that same Administration screen.

Simple Manual Restore Using Command Line

PostgreSQL allows restoring backup dumps using the following simple command:
su - projop
psql –f pg_dump.xxxx.sql

Please note that this command will only succeed if:

  • If you have created the PostgreSQL dump using the commands above
  • If you load data from the same system and with exactly the same database version
  • If in case of a restore on a different platform/system timezones are equal.

Full Manual Restore into a new Database

If you move your installation between different computers and/or database versions, we recommend the following process. Let’s assume for the following that you want to take a copy of your production database to a test VMware installation on your Windows laptop. You would go through the following steps: - Create a backup database dump using Admin -> Backup. - Upload the database dump into a VMware. Tip: You can use the filestorage of the existing (old) ]po[ instance on the Vmware to conveniently upload the file. - On your laptop’s VMware: Drop the database - On your laptop’s VMware: Create a new database - On your laptop’s VMware: Import the database dump - On your laptop’s VMware: Restart the AOLserver

Make sure that both systems have identical configuration

Most importantly you want to make sure that both system use the same timezone setting. 
Run the following command on both systems: 
show timezone

Drop the Database

Please use the following command to drop the database:
su - projop
killall -9 nsd; dropdb projop
Please note the "killall -9 nsd". This command kills the AOLserver, so that PostgreSQL can drop the database. Otherwise PostgreSQL will complain that: “ERROR: database ‘projop’ is being accessed by other users”. In some cases (very fast systems) it is possible that you can’t drop the database this way. In that case please insert the following command as the first line of /web/projop/etc/config.tcl: # Wait 5 seconds for PostgreSQL to come up... exec sleep 5

Create a new Database

Please execute the following commands. The last command is optional if you already created the language “plpgsql” in the “template1” database (see above).
su - projop
createdb projop
createlang plpgsql projop

Import the Database Dump

Now you can import the database dump as usual:
su - projop
psql –f pg_dump.xxxx.sql 2>&1 > import.log
less import.log

Please note the “less import.log”. Please analyze the import.log file for errors:

  • It is OK if there are some ~2500 lines with “ERROR:” in the top of the file. These lines are created because the database dump contains instructions to drop previously existing data. However, we have started with a clean database, so the drop commands will fail.
  • However, the rest of the import should be free of “ERROR:” messages.
Restart AOLServer Now you have to restart AOLserver with the new database.
su - projop
cd ~/log
rm *			(delete all old log files)
killall -9 nsd	(restart AOLserver)
less error.log	(check the error log)
Please note the “less error.log” command: Use this to search for “ERROR:” in the errror.log file.

System Recovery

System recovery is the process of recovering a ]project-open[ system after system crash or another incident.
The following Gantt chart gives you an overview over the procedure. The recovery of a system should be possible within 90 minutes if backups have been made correctly and if there is spare server hardware available.

Useful Windows Shell Scripts

WINDOWS: Sample Script to drop and re-create a ]po[ database

# psql
# set -e
# set -u
cmd /c '%SystemRoot%\\system32\\WindowsPowerShell\\v1.0\\powershell.exe' -Command "spsv AOLserver-projop"
rm -f /cygdrive/c/project-open/servers/projop/log/error.log
dropdb -U postgres projop
createdb -U postgres -O postgres -E utf8 projop
cd /cygdrive/c/project-open/servers/projop/filestorage/backup/
psql -d projop -U postgres -f pg_dump.projop.20151204.001241.sql
cmd /c '%SystemRoot%\\system32\\WindowsPowerShell\\v1.0\\powershell.exe' -Command "sasv AOLserver-projop"

WINDOWS: Set ENV variables

# #######################
# Goto cygwin shortcut properties and check: "Always run as Admin"

# ------------------------
# Configure .bash_profile

export POSTGRES_HOME="/cygdrive/[DRIVE_LETTER]/project-open/pgsql/"
export POSTGRES_LIB="/cygdrive/[DRIVE_LETTER]/project-open/pgsql/lib"
export POSTGRES_INCLUDE="/cygdrive/[DRIVE_LETTER]/project-open/pgsql/include"

export CVSROOT=":pserver:cvs@cvs.project-open.net:/home/cvsroot"
export CVSREAD="yes"
export CVS_RSH="ssh"

# If problems w/ UNVALID ENV Variables:
export CVS_PASSFILE="[DRIVE_LETTER]:/cygwin64/home/[USERNAME]/.cvspass"
export PATH=$PATH:~/

alias "l=ls -als"


Following links to earlier versions of the Operations & Maintenance Guides

  Contact Us
  Project Open Business Solutions S.L.

Calle Aprestadora 19, 12o-2a

E-08907 Hospitalet de Llobregat (Barcelona)

 Tel Europe: +34 932 202 088
 Tel US: +1 415 429 5995
 Mail: info@project-open.com