This page documents how we use a PostgreSQL database to store our automated test results.
To start the server, you must be logged in to
tonic as user
ps xuc | grep postPostgreSQL 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
rm -f postmaster.pid
ps xuc | grep post
Sending the test summary email can be done at any time and as any user with appropriate (VDT) AFS access.
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.
/s/postgresql-8.3/bin/psql -h tonic -U vdt_admin vdt_tests
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.
DELETE FROM runs WHERE time < '2008-12-01';or
DELETE FROM runs WHERE version = '1.10.0';
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.
VACUUM VERBOSE;or run the longer-running, locking, no-holds-barred version to reclaim maximal space and tune optimization statistics:
VACUUM FULL VERBOSE ANALYZE;
|Port:||5432 (PostgreSQL default)|
|Binaries:||/s/postgresql-8.3/bin (CSL supported)|
Starting with the PostgreSQL 8.3 database cluster, we now have several database users, each with its own privileges and 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|
When logged into the PostgreSQL command interpreter (psql), there are lots of commands you can use. Here are a few helpful ones.
The test database has a very simple structure. There are essentially two tables:
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.
Here are a couple reference materials, should you need to dig deeper.
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
cd /scratch.1/vdt/test-db ./stop_server
cd /scratch.1/vdt mv test-db SOME_BACKUP_DIRECTORY
/s/postgresql-8.3/bin/initdb -D /scratch.1/vdt/test-db -E UTF8
condor:condoron the new directory
ls -ld /scratch.1/vdt/test-db
#!/bin/sh /s/postgresql-8.3/bin/pg_ctl start -D /scratch.1/vdt/test-db -l postgres.log
#!/bin/sh /s/postgresql-8.3/bin/pg_ctl stop -D /scratch.1/vdt/test-db
chown condor:condor start_server stop_server chmod 0700 start_server stop_server
postgresql.confto include the following settings
listen_addresses = '*'
pg_hba.confto include at least the following entries
local all all trust host all all 127.0.0.1/32 trust host all all 220.127.116.11/32 md5 # tonic host all all 18.104.22.168/32 md5 # teal (for website) host all all 22.214.171.124/32 md5 # monza (for kronenfe) host all all 126.96.36.199/32 md5 # VDT test machines (now) host all all 188.8.131.52/32 md5 # VDT test machines (possible) host all all 184.108.40.206/32 md5 # VDT test machines (possible)
/s/postgresql-8.3/bin/psql -h localhost -U condor postgres
\i SVN_TEST_TRUNK/create-test-db.sqlIf this goes badly, there’s a matching clean-up script (caution: this command removes everything from the test database, even the VDT users):