[Postgresql Database Setup Procedures]


JobServer depends on a RDBMS to store and manage its critical data. This document 
will cover the basic steps needed to configure your database such that it is 
operational for Jobserver to use. However, this document is not intended to be a 
thorough discussion of Postgres database configuration, administration, or
tuning. For example, you will need to insure that your Postgres database is 
configured properly to allow for remote jdbc clients, ...etc. Tuning your 
database will depend, in part, on how large you expect the database tables to 
grow and how many concurrent users and concurrently running jobs you expect
JobServer to manage.


Database Requirements:
----------------------

Postgres versions 7.3 and higher are supported.


Instructions for setting up Postgres JobServer database:
--------------------------------------------------------


 1) Have your database administrator make a database instance available for use by 
    JobServer. Make note of the hostname, port number, and database instance name. 

    You have the option of defining a schema namespace for your JobServer tables. This is
    optional, but can be helpful in administrating the tables and also allows multiple
    JobServer installations to share the same database instance while remaining isolated
    from each other. Follow the steps below to perform the installation.

    There are two ways to install the database tables. One way uses schema namesapes
    the other does not. Choose the one that best suites your needs.


    Installation steps when NOT using a schema namespace:
    ----------------------------------------------------
       
        a) Have your administrator create a database user named "jsuser" (you may use
           a different name if you like). This will be the user that will be referenced 
           by the JobServer applications when connecting to the database. If you already 
           have an existing database user that you want to use then skip this step and 
           go to part "b" now. Otherwise, your database administrator may use the 
           following command to create the "jsuser":

             > CREATE USER jsuser PASSWORD 'changeme';
              
        b) To create the JobServer tables you can either do it manually by applying the sql
           file "postgres-jobserver.sql", or you may use the "dbsetup" tool which walks you 
           interactively through the process. In both cases you will need to have access to
           a database username/password with administrative rights to create tables, indexes,
           and grant rights. Choose one of these two steps to complete the installation of the
           database tables:

             i) dbsetup tool: 
                   If you choose to use the "dbsetup" tool, it will walk you
                   through the remaining steps to create the JobServer tables.
                   Run the command "dbsetup" found in the "jobserver/database"
                   directory and follow its instructions as shown here:
                   
                     > dbsetup.sh  (unix)
                     > dbsetup.bat (windows)

            ii) Manually run script:
                   Have your database administrator run the database script 
                   "jobserver/database/postgres-jobserver.sql" to create the 
                   tables for JobServer. Ideally you should run the script such 
                   that the tables are owned by the the user "jsuser". If you 
                   use a different username other than "jsuser", you will need 
                   to edit the "postgres-jobserver.sql" file and replace all 
                   occurrences of "jsuser" with the database username you chose 
                   instead.
         
        c) Now go to step 2 below.

        
    Installation steps when using a schema namespace:
    ------------------------------------------------

        a) Have your administrator create the database user "jsuser" and a schema 
           namespace by the same name. You may choose any other database username or 
           schema namespace you like. The "jsuser" user is used by the JobServer 
           applications to access the database.
  
           Have your database administrator perform the following steps to begin the 
           JobServer database setup process:
 
              > CREATE USER jsuser PASSWORD 'changeme';                    
              > CREATE SCHEMA jsuser; 
              > GRANT ALL ON SCHEMA jsuser TO jsuser;


        b) To create the JobServer tables you can either do it manually by applying
           the sql file "postgres-jobserver.sql", or you may use the "dbsetup" tool which
           walks you interactively through the process. In both cases you will need
           to have access to a database username/password with administrative rights 
           to create tables, indexes, and grant rights.

             i) dbsetup tool:
                  If you choose to use the "dbsetup" tool, it will walk you
                  through the remaining steps to create the JobServer tables.
                  Run the command "dbsetup" found in the "jobserver/database"
                  directory and follow its instructions as shown here:
                   
                     > dbsetup.sh  (unix)
                     > dbsetup.bat (windows)

           ii) Manually apply script:
                  Apply the script "jobserver/database/postgres-jobserver.sql" using 
                  the database user "jsuser". This will insure that the tables will 
                  be contained within a schema by the same name and owned by the user 
                  "jsuser'. If you choose to use a different name other than "jsuser" 
                  make sure to substitute the name in the "postgres-jobserver.sql" to 
                  reflect the change, before applying the sql file. 


 2) Once the database setup has been completed, the JobServer installation will need to be 
    able to reference the database. The following database properties will need to be 
    defined in the Jobserver "jobserver/conf/system-config.properties" file: 

        dbDriverType=PSQL        /* Identify the database used by JobServer as Postgre      */
        dbHost=localhost         /* Hostname where database is installed                    */
        dbName=somename          /* Name of the database used                               */
        dbPort=9020              /* Port number to use to connect to database via JDBC      */ 
        dbUsername=jsuser        /* Postgre user used by JobServer apps to access database. */
        dbPassword=changeme      /* Password corresponding to jsuser username.              */
        dbSchemaName=jsuser      /* Name of the schema namespace used to house the tables   */
                                 /* database structures. Leave blank if namespace not used. */


 3) Configure the database properties discussed above in the 
    "system-config.properties". Now with the database up and running, check 
    if the database is properly configured and can be accessed by JobServer.
    Run the following command:
    
        > jobserver/bin/jsmonitor.sh  (unix)
        > jobserver/bin/jsmonitor.bat (windows)

    This will tell you if JobServer can detect and connect to the database. 
    If the database status is not shown as "Active" recheck the installation 
    steps to make sure you have configured the database and JobServer properly.
    
    
 4) JDBC Driver Support:
    Check with your DBA to verify the exact version of PSQL you are using. JobServer comes
    bundled with a version of the 8.x JDBC driver. You can if necessary replace this driver
    with one that is more appropriate for your particular version of the PSQL server you
    are using. Contact us for questions or assistance.


                       [Database Maintenance]


Routine Maintenance
-------------------
It is recommended that you follow and apply the maintenance proceedures discussed
in the Postgres administration docs. These include:

 - Routine Vacuuming
 - Recovering disk space
 - Updating planner statistics
 - Preventing transaction ID wraparound failures
 - Routine Re-indexing
 - Log File Maintenance
 - Backups

Table sizes and fragmentation should be monitored and kept optimal. The tables 
that grow most rapidly will need the most attention. These include the tables 
"js_wbstatus", "js_opstatus", and "js_taudit".


Table Trimming
--------------
The JobServer database tables should normally be trimmed on a periodic basis, to avoid
accumulating excessive amounts of outdated historical data. To do this,  
create a job using the pre-built TaskBean called "JobServerCleanup" that comes with 
the installation. Create, configure, and schedule this Job. Run this job on a regular
basis (for example weekly, monthly, or as needed). It will keep the database tables trimmed
to your specifications by removing outdated historical records from the various database
tables including outdated log files from the filesystem.


Backup
------
Backup database as necessary. Incremental backup is recommended with periodic 
full backup.