Note: This web site is only kept up to date for OSG Software 1.2 (VDT 2.0.0). If you are looking for information for the most recent release, the RPM-based OSG Software 3.0, please see the OSG documentation web site

Database of Automated Test Results

This page documents how we use a PostgreSQL database to store our automated test results.

Common Usage

Starting the PostgreSQL server

To start the server, you must be logged in to tonic as user condor.

  1. Change to the database cluster directory:
    cd /scratch.1/vdt/test-db
  2. Is PostgreSQL running?
    ps xuc | grep post
    PostgreSQL is running if you see lines like this:
    condor   18034  0.0  0.1 38924 3996 pts/1    S    09:01   0:00 postgres
    condor   18040  0.0  1.1 39056 24864 ?       Ss   09:01   0:00 postgres
    condor   18041  0.0  0.0 38924 1316 ?        Ss   09:01   0:00 postgres
    condor   18042  0.0  0.0 39056 1452 ?        Ss   09:01   0:00 postgres
    condor   18043  0.0  0.0 10388 1288 ?        Ss   09:01   0:00 postgres
  3. If PostgreSQL is NOT running, delete its pid file if needed
    rm -f postmaster.pid
  4. Start the database
    ./start_server
  5. Is PostgreSQL running now?
    ps xuc | grep post
  6. If the database was down when the test summary email should have gone out, consider sending the email manually (below)

Sending the test summary email

Sending the test summary email can be done at any time and as any user with appropriate (VDT) AFS access.

  1. Run the script that sends the automated test summary email
    /p/vdt/workspace/bin/send-mail.sh

Using the database from the command line

Because it has its own user system, you can access the PostgreSQL database from any user account. However, we have restricted access such that you can connect only from the following hosts: tonic, teal, monza, and the VDT test machines.

  1. Connect to the server
    /s/postgresql-8.3/bin/psql -h tonic -U vdt_admin vdt_tests
  2. Enter the password when requested (get from Tim or other sources)

Deleting old records

Although we intend to automate this process in a cron job eventually, it may be helpful to know how to delete old test results from the database manually.

  1. Connect to the database (see above)
  2. Run a SQL command such as the following (changing the date, of course)
    DELETE FROM runs WHERE time < '2008-12-01';
    or
    DELETE FROM runs WHERE version = '1.10.0';

Vacuuming the database

Vacuuming a PostgreSQL database reclaims unused space on disk and, with extra options, reclaims even more space and tunes the database for speed. Again, we intend to automate this process.

  1. Connect to the database (see above)
  2. Run the basic vacuum command to reclaim unused space:
    VACUUM VERBOSE;
    or run the longer-running, locking, no-holds-barred version to reclaim maximal space and tune optimization statistics:
    VACUUM FULL VERBOSE ANALYZE;

About the Database

Basic data

Version: 8.3
Host: tonic.cs.wisc.edu
Port: 5432 (PostgreSQL default)
Database cluster: /scratch.1/vdt/test-db
Binaries: /s/postgresql-8.3/bin (CSL supported)

Database users and privileges

Starting with the PostgreSQL 8.3 database cluster, we now have several database users, each with its own privileges and intended use.

User Privileges Intended Use
condor root owns entire database cluster, can do anything; use with caution
vdt_admin ALL PRIVILEGES can do nearly anything to the vdt_tests database; good for routine maintenance
vdt_write SELECT, INSERT, UPDATE for the test scripts from VDT test machines
vdt_read SELECT for the test results website

Basic commands

When logged into the PostgreSQL command interpreter (psql), there are lots of commands you can use. Here are a few helpful ones.

Schema

The test database has a very simple structure. There are essentially two tables:

The extra runs_id_seq table is used by PostgreSQL to give unique sequential numbers to the id column of the runs table. Generally, it can be ignored.

PostgreSQL references

Here are a couple reference materials, should you need to dig deeper.

Creating the Database From Scratch

These are the steps that Tim followed in December 2008 to recreate the database cluter using PostgreSQL 8.3.

Perform all steps on tonic as user condor.

  1. Shut down existing cluster
    cd /scratch.1/vdt/test-db
    ./stop_server
  2. Move existing cluster aside
    cd /scratch.1/vdt
    mv test-db SOME_BACKUP_DIRECTORY
  3. Create new cluster under PostgreSQL 8.3
    /s/postgresql-8.3/bin/initdb -D /scratch.1/vdt/test-db -E UTF8
  4. Verify that permissions are 0700 for condor:condor on the new directory
    ls -ld /scratch.1/vdt/test-db
  5. Make a new start_server script:
    #!/bin/sh
    /s/postgresql-8.3/bin/pg_ctl start -D /scratch.1/vdt/test-db -l postgres.log
  6. Make a new stop_server script:
    #!/bin/sh
    /s/postgresql-8.3/bin/pg_ctl stop -D /scratch.1/vdt/test-db
  7. Set ownership and permissions on start and stop scripts
    chown condor:condor start_server stop_server
    chmod 0700 start_server stop_server
  8. Edit postgresql.conf to include the following settings
    listen_addresses = '*'
  9. Edit pg_hba.conf to include at least the following entries
    local   all         all                                trust
    host    all         all         127.0.0.1/32           trust
    host    all	    all	        128.105.121.53/32      md5    # tonic
    host    all	    all	        128.105.121.36/32      md5    # teal (for website)
    host    all	    all	        128.105.175.36/32      md5    # monza (for kronenfe)
    host    all	    all	        198.51.254.98/32       md5    # VDT test machines (now)
    host    all	    all	        198.51.254.99/32       md5    # VDT test machines (possible)
    host    all	    all	        198.51.254.100/32      md5    # VDT test machines (possible)
  10. Connect to the new database and make sure it’s happy
    /s/postgresql-8.3/bin/psql -h localhost -U condor postgres
  11. Create the VDT test database
    \i SVN_TEST_TRUNK/create-test-db.sql
    If this goes badly, there’s a matching clean-up script (caution: this command removes everything from the test database, even the VDT users):
    \i SVN_TEST_TRUNK/drop_test_db.sql